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
get count of customer records from the Microsoft access northwind database
2. Create a count from database 2
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.
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.
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