Add subtract data from two different database sources : Howto

How to perform calculations across different data sources/databases

Imagine, you have a CRM database that contains all your customer and sales information and you have a different database that stores your GL and ERP data.

Now add some complexity to it, your CRM database is say SQL Server or Microsoft Access or just Plain old Excel file. And your ERP is housed on an Oracle database.

Say you needed to show total leads from CRM, show total cost from GL journals and derive a third metric say "Cost per leads" (fictitious metric).

Typically, the best practice is to bring the information together in some kind of datawarehouse and then since the information now resides in one database we can do any kind of calculation on it.

But for a small business, datawarehouse is a "nice to have" thing and may not be feasible or practical for one-off requirement. So the following steps will illustrate how you can acheive and calculate this metric within the dashboard itself.

In the below example we will take counts from two different tables that are in separate databases and then create a third widget that does the sum of both numbers. This should be sufficient to illustrate the basic mechanism.

1. Create a count from database 1

Image

get count of customer records from the Microsoft access northwind database

Image

Now Edit the Javascript tab and insert the following code.

global_widget1_value=glb["_new"];

Image

The above javascript code is simply assigning the count from the database to a javascript variable. global_widget1_value is just a variable name that we can refer globally and to it we assign the value from the database. All database values arrive in an array and glb["_new"] contains only the first element of that array. glb["_new"] is same as glb["_odata"][0][0] and since we are concerned with only a single cell value we can use it here.

 

2. Create a count from database 2

Image

 

Image

 

To this widget we add the following javascript

global_widget2_value=glb["_new"];

Image

 

3. Create a third widget or duplicate any of the above widget.

The database for the third widget is not important. It can be either database 1 or database 2 as we are going to overwrite the value with a custom calculation.

 

Image

Here is the javascript for the third widget.

Image

glb["_odata"][0][0]=parseFloat(global_widget1_value)+parseFloat(global_widget2_value);

In this code, we take the global_widget1_value and global_widget2_value and add them. We assign the new value to the first element of the data array.

This gives us the new derived value.

Image

 

CATCH: Widgets are refreshed in the order which they are created in the dashboard. So when the dashboard is launched, widget 1 will refresh first and show a value of 34, next, widget 2 will refresh and show value of 404 and since widget 3 was created last, it will have latest information from widget 1 and widget 2

Join InfoCaptor and Get Free Dashboard Software

Posted in Charts, dashboards, DB2, Excel, How-to, MS Access, MySQL, webdashboard-help | Tagged , , , , , , , ,


Would you like to build your dashboard now?

If so, click to buy now, including 60 minutes of dashboard development for free!