Let say you have the following three widgets on the dashboard
Typically each widget can use its own individual SQL query to populate the widgets.
Users widget: select count(*) from xuser
Projects widget: select count(*) from xcategory
Dashboards widget: select count(*) from xobject
Now in the above example the SQL is pretty simple and it might very well be the case the results for each query return back in micro or milli seconds.
But what happens, if each of the query starts taking time in say 5 seconds per query. So the total response time for the entire dashboard to finish refreshing is around 15 seconds and that is a lot of latency.
In the following example we will achieve the same result with a single SQL and updating each widget at the client side.
1. Issue single SQL to get all the desired results
we combine all the SQL in union clause and then use it with the box widget.
NOTE: we may use any other widget as this is going to be the main data collector and not displayed on the dashboard.
a. Add the box to the dashboard
b. Right click and add the data source
select ‘Total Users’ as metric_name,count(*) as metric_value
from xuser
union
select ‘Total Projects’ as metric_name, count(*) as metric_value
from xcategory
union
select ‘Total Dashboards’ as metric_name, count(*) as metric_value
from xobject
c. In the javascript tab add the following channel values
glb_api[“add_channel_value”](“user_count”,glb[“_json”][“data”][1][0]);
glb_api[“add_channel_value”](“project_count”,glb[“_json”][“data”][1][1]);
glb_api[“add_channel_value”](“dashboard_count”,glb[“_json”][“data”][1][2]);
Overview: In the above box, we added the SQL that returns the data simply as two column array. Next in our post data javascript, we take each array value and assign it to a specific channel value. We are simply piggy backing on the dashboard drill down functionality. For now you can simply follow the procedure.
So far, this is how our widget on the dashboard look like
Don’t worry about how the data looks as we will hide it from dashboard display.
2. Add the individual widget containers
a. double click on the circle and type some value in it.
Change the font size to your desired setting.
b. right click and duplicate the widget 2 times
So now we should have three circle containers on the dashboard
c. Right click on the first circle and select “Data Source”
1. In the SQL tab, pick the connection “Just text in SQL” from the drop down (it is near the end of the list)
2. Click on the “Show Filters/Prompts”
3. Expand the Other node and Select the channel variable “user_count” (remember we added this channel variable inside the javascript of the parent box)
4. Click on “Add Selected Filter”
5. The value “param<user_count>” is added in the SQL box.
6. Close the window.
Clean up the javascript TAB. For new widgets, the data source is inherited from last time you updated any widget’s data source.
Since we don’t need the javascript here we simply select and delete it and keep it blank.
Click OK and you should see that the circle now has the user count value
Repeat the steps for the second circle but this time select the project_count variable
And for the last widget pick dashboard_count
Now that we have our widgets, we can the parent box settings to make it appear hidden.
Right click on the main box, select “Toggle Background” once to hide the background, and then select “Toggle Border” to hide the border.
Now select the box and change its text color to white
You can type the color “FFFFFF” or selec the white color
This is how your final dashboard will be
Notice how the box is not showing on the right side but still is responsible for fetching data and populating rest of the widgets.