Creating a vendor cost on-line monitoring chart
Emin Gabrielyan
2013-07-16
1. Table of contents
4. Creating
an ODBC connection
5. Creating
a connection in the Excel file.
6. Edit
the connection properties
7. Mirror
the remote table content in an area of your worksheet
2. Introduction
In scope of our continuous effort to monitor the frauds, this document shows an example of an hourly monitoring of a vendor cost.
3. Database
On a fraud monitoring server a Perl script is responsible for alimenting a database of a vendor cost.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Monitor |
| fraud-monitor |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.04 sec)
mysql> use Monitor;
Database changed
mysql>
mysql> show tables;
+-------------------+
| Tables_in_Monitor |
+-------------------+
| Hourly_Cost |
+-------------------+
1 row in set (0.04 sec)
mysql>
mysql> select * from Hourly_Cost;
+-------------+----------+---------------------+-----------+
| day_of_week | hour | date | cost |
+-------------+----------+---------------------+-----------+
| 2 | 16:00:00 | 2013-07-15 16:00:00 | 84.82307 |
| 2 | 17:00:00 | 2013-07-15 17:00:00 | 84.78726 |
| 2 | 18:00:00 | 2013-07-15 18:00:00 | 99.32051 |
| 2 | 19:00:00 | 2013-07-15 19:00:00 | 104.89002 |
| 2 | 20:00:00 | 2013-07-15 20:00:00 | 117.83250 |
| 2 | 21:00:00 | 2013-07-15 21:00:00 | 3.90537 |
| 2 | 22:00:00 | 2013-07-15 22:00:00 | 9.74724 |
| 2 | 23:00:00 | 2013-07-15 23:00:00 | 1.05910 |
| 3 | 00:00:00 | 2013-07-16 00:00:00 | 20.45499 |
| 3 | 01:00:00 | 2013-07-16 01:00:00 | 9.68919 |
| 3 | 02:00:00 | 2013-07-16 02:00:00 | 19.45224 |
| 3 | 03:00:00 | 2013-07-16 03:00:00 | 2.07848 |
| 3 | 04:00:00 | 2013-07-16 04:00:00 | 3.29143 |
| 3 | 05:00:00 | 2013-07-16 05:00:00 | 4.97508 |
| 3 | 06:00:00 | 2013-07-16 06:00:00 | 20.46893 |
| 3 | 07:00:00 | 2013-07-16 07:00:00 | 20.09224 |
| 3 | 08:00:00 | 2013-07-16 08:00:00 | 36.92859 |
| 3 | 09:00:00 | 2013-07-16 09:00:00 | 46.24613 |
| 3 | 10:00:00 | 2013-07-16 10:00:00 | 53.11939 |
| 3 | 11:00:00 | 2013-07-16 11:00:00 | 61.55477 |
| 3 | 12:00:00 | 2013-07-16 12:00:00 | 54.30661 |
| 3 | 13:00:00 | 2013-07-16 13:00:00 | 87.60364 |
| 3 | 14:00:00 | 2013-07-16 14:00:00 | 59.79774 |
| 3 | 15:00:00 | 2013-07-16 15:00:00 | 78.31534 |
| 3 | 16:00:00 | 2013-07-16 16:00:00 | 49.98598 |
| 3 | 17:00:00 | 2013-07-16 17:00:00 | 29.34529 |
+-------------+----------+---------------------+-----------+
26 rows in set (0.11 sec)
mysql>
4. Creating an ODBC connection
Click on Add
Select the MySQL ODBC Unicode Driver and click on Finish
Fulfill the connection and database information
Test the connection
Your connection is now created
5. Creating a connection in the Excel file
Open a new Excel file
Add a data connection (in Data / Connections)
Then continue as shown in the path [Data] / [Connection] / [Add] / [Browse for More…] / [New Source…] / [ODBC DSN] / [Next]
Select our new connection for the vendor cost monitor
Click on Next
Next
Finish
You now have a link to the remote table in your Excel file. Close.
6. Edit the connection properties
In the connection properties
Change the refresh time to 3 minutes and force the refresh at the opening.
7. Mirror the remote table content in an area of your worksheet
Click on the Existing Connections in the Data section
Select the new connection
And click on Open
Confirm the location (you can re-check the properties as well)
Your Excel file now looks like this
8. Creating the chart
Insert a clustered column chart.
It does not look good yet, as all hours of the horizontal axis are grouped into single days…
Select the D column (the column of date) and change its number format (Ctrl-1) to General
The chart will take a better look
Now select the values on the horizontal axis, directly on the chart, and change its number display format as follows
And click on Add
And then Close
Your chart will have the following look
Remove the legend. Add labels on histograms. Change the title.
Format the data labels alignment. Format also the alignment of the labels of the horizontal axis. Format the numbering format of the data labels as well.
Add colors to data labels depending on their values.
Add a semi-transparent background to data labels.
Your chart is ready
Your chart is ready.
9. References
Connecting Excel to a remote MySQL server: http://www.switzernet.com/3/public/130715-excel-to-remote-mysql/
Properties of Excel to remote MySQL connection: http://www.switzernet.com/3/public/130715-excel-mysql-connections/
Excel example file used in this document: [130716-vendor-cost-monitor.xlsx]
Copyright © 2013 by Switzernet.com
End of the document