Dashboard Parameter trick : Count from two seperate Databases

In this article we will discuss a scenario and how to accomplish it within the dashboard.

Let say you have two databases. Both databases have similar tables and you need to display the difference in the number of rows from these two tables.

InfoCaptor allows you to connect to multiple databases and display information on the same dashboard page. But in this situation we are required to create two distinct queries from two seperate databases and do calculation on the returned values.

This can be accomplished as illustrated below

Create first parameter with the total row count from 1st database table

Image

 

Create second parameter with row count from second database table

Image

 

Now create a Qlet that will do a difference between the two parameters

Right click on the text editor, will show the parameter list. Select the parameter Display value, remove the single quotes and the comments and then select the second parameter display value.

Image

Once you select the p32 Get Display the Qlet text editor shows as below

Image

 

Remove the single quotes and the comment section as shown below

Image

Add a minus sign after the above string and then select the second parameter display value

Image

 

Thats it - you get a Qlet that represents the difference of row counts between two seperate databases.

Now how about applying some conditional formatting to the Qlet

Image

 

For the Qlet, enable the Javascript option by setting 'Run Dynamic Java Script = 'Y'

and then enter the following script

if (numericValue < 0 )
{
renderer.setForeground(Color.red);

renderer.setBackground(new Color(153,153,250));

}
else if (numericValue >0 && numericValue <100)
{
renderer.setForeground(Color.yellow);

renderer.setBackground(new Color(153,153,250));

}
else if (numericValue >100 )
{
renderer.setForeground(Color.orange);

renderer.setBackground(new Color(153,153,250));

}

 

Alternatively you could create a meter chart with the same query

Image

https://www.infocaptor.com/user_help/dashboard_meter_chart.htm

Join InfoCaptor and Get Free Dashboard Software

Posted in Excel, General, How-to | Tagged , , , , , , , , , , , ,


Would you like to build your dashboard now?

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