December 19, 2014 at 9:23 am
Hi all,
I'm looking to build out a dashboard that will take in a dynamic list of instances and applications. At a high level, I'm looking to have a handful of metrics (CPU, memory, etc) to give a very high level look at the health of SQL Servers for a given application.
My challenge is that subreports cannot pass values back to the "parent".
At the moment, I have a tablix with App | Instance Name | <Subreport that takes the instance name as a parameter from the details of the tablix; returns health state>
This seems to work out for that level of detail, but I'm interested in going a layer higher and rolling up the health status for all instances of a particular app, and I cannot seem to figure out logic as to where i can potentially replace the subreport and use another method to get the health state to roll up.
My preference is not to use an SSIS package and store the data, as we simply want to leverage this dashboard for purely real-time "how do I look at the moment?" reporting.
Obviously, there are other tools out there that would accomplish this, but I was trying to see if there was a way to accomplish this in SSRS.
Thanks in advance!
Sample mock up:
App 1 | Healthy
App 2 | Healthy
Underneath App 1:
Instance 1 | Healthy
Instance 2 | Healthy
January 29, 2015 at 9:39 am
Anyone have any suggestions? I suppose I could get this built if I collect the data and report off of that, but I was trying to have a live query executed.
January 29, 2015 at 11:27 am
How do you calculate health? (Or does it matter?)
I think you can create a stored procedure to join the two tables and flatten the data and then just group by the App/Instance. Then you could show the score in the group by putting a calculation in the App group level showing a summary of child values (like SUM or something like that).
(Or did I totally misunderstand your question?)
HTH,
Pieter
January 29, 2015 at 11:34 am
i have a dashboard, that pulls multiple sub reports from a couple of central tables;
those tracking tables get populated by queries/sql jobs that grab statistics and insert them into the table.
so they don't query other reports, but a central repository of various stats that get assembled.
is that what you are after?
or you really want to check other dashboards?
Lowell
January 29, 2015 at 11:53 am
pietlinden (1/29/2015)
How do you calculate health? (Or does it matter?)I think you can create a stored procedure to join the two tables and flatten the data and then just group by the App/Instance. Then you could show the score in the group by putting a calculation in the App group level showing a summary of child values (like SUM or something like that).
(Or did I totally misunderstand your question?)
HTH,
Pieter
Thanks for the reply. I'm trying to build a heat map to see if any of our critical application instances are performing sub-optimally. For example, CPU over 75% or active blocking occurring, and basically define out any other facets to measure. Per your suggestion, I would already have the data gathered which I could run via SSIS to load in the data, and the report would be delayed as to how often I run the collection.
The situation that I'm really looking to address is:
1. We are seeing latency in application X. How does the DB environment look? The level 1 team, as well as management, could have this nice clean report where application x shows red, green, or yellow in real-time to give them an idea on if ANY of the SQL Servers behind application x are outside our defined parameters.
What i did in my first attempt (albeit maybe over-complicating things)
1. Parent report has a tablix where the dataset is set up for a list of instances. A column of that tablix loads a subreport.
2. The subreport is simply a blank canvas with a color fill expression based on the results of multiple data sets (my data points). DataSet_CPU_Health -- sample expression IIF(<cpu_health> = "good", "Green", "Red")
However, the big flaw with that is I can get the status on the parent report at the instance level, but I can't read the value returned by the subreport. SSRS doesn't allow the subreport to flow data or variables back to the parent, can only push values down.
I'll probably wind up loading the data and assuming a 5 minute delay in data that is presented on the dashboard.
January 29, 2015 at 11:58 am
Lowell (1/29/2015)
i have a dashboard, that pulls multiple sub reports from a couple of central tables;those tracking tables get populated by queries/sql jobs that grab statistics and insert them into the table.
so they don't query other reports, but a central repository of various stats that get assembled.
is that what you are after?
or you really want to check other dashboards?
Thanks for the reply, Lowell. As in my other reply, this is likely the solution (to load in the data and report back out on it), but I was trying to get DMV queries to execute in real-time against a dynamic list of instances and report back on health, and roll that status up on-the-fly.
Show just a list of applications and their statuses.
App 1 - Red
App 2 - Green
App 3 - Green
Click on App 1 and see the following:
App 1 (Red)
--Instance 1: Green
--Instance 2: Red
--Instance 3: Green
Click on App 2 and see the following:
App 2 (Green)
--Instance 1: Green
--Instance 2: Green
--Instance 3: Green
Click on App 3 and see the following:
App 3 (Green)
--Instance 1: Green
--Instance 2: Green
--Instance 3: Green
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply