Today I am putting together my SSRS dashboard. This will be an OVERVIEW page that I can drill into for details. I’ll be sharing the OVERVIEW page only here but may update the code in GIT at a future date to include some detail pages.
I am using Visual Studio Professional 2019 with the Reporting Services plugin installed.
Here is what I hope the final report will look like:
Data Sources
All of my reports will be coming from the data that was imported into the HEADQUARTERS ServerAnalysisDW database. Therefore, I will only need a single data source. I will create this as a SAHRED DATASOURCE so I don’t have to repeat this process on every single report.
To do this, RIGHT CLICK over the SHARED DATA SOURCES folder and select to create a new one.
REPORT: Data Source
Next I will create a NEW REPORT by RIGHT CLICKING over the REPORTS folder. I will name the report DASHBOARD.
I will need use of the SHARED DATA SOURCE inside this REPORT. To do that, I OPEN the REPORT DATA TOOL BOX. I typically do this by clicking anywhere on the blank report, then PRESS CTRL+ALT+D at the same time.
Once I find the DATA SOURCE folder, I RIGHT CLICK and select ADD DATA SOURCE.
I have configured my DATA SOURCE by giving it the same name as the shared data source: ServerAnalysisDW. I CLICK the USE SHARED DATA SOURCE radio button and select the shared source I created earlier, from the dropdown menu.
REPORT: Data Set
While I could create a SHARED DATA SET, I will be mostly using DATASETS at the report level instead of at the project level. This is because most of my report pages are unique.
To start with a DATASET, I right click over the DATASET ICON and select ADD DATASET.
I would like to use the Stored Procedure I created in PART 8 of this series for my data. To do this, I will configure my dataset with the name CLUSTEROVERVIEW and click the radio button to EMBED it in my report.
Next, I select the only DATASOURCE I have in this report. Then, I click the STORED PROCEDURE radio button which will populate the dropdown box below with available options.
From the list I select, ANALYSIS_PERFCOUNTER_RPT_OVERVIEW, which I created last week.
REPORT: Title
From the TOOLBOX, I grab a TEXT OBJECT and drag it onto the REPORT MATRIX. Using the PROPERTIES WINDOW I can adjust colors and font. I titled my report: SQL SERVER PERFORMANCE MONITOR DASHBOARD.
I’ve been thinking about changing it to SQL MON or SQL DEAL or LAST SNAP. Just warning, in case what ends up in GIT is completely different.
REPORT: Tablix
Since I am monitoring multiple servers at multiple locations, I thought it would be best to create a TABLIX on the OVERVIEW REPORT. This will iterate through each instance and display all the gadgets in some kind of orderly way.
In my TOOLBOX I grab a TABLE OBJECT and drag it under the TITLE TEXT.
Next, I RIGHT CLICK in the UPPER LEFT CORNER of the TABLE HANDLE and select TABLIX PROPERTIES. This is a feat in itself, as the Tablix likes to hide and show its boarders depending on where my mouse is located. Sometimes this can be trickier than playing a video game without a joystick.
Here I will wire my DATASET to the TABLIX by selecting it from the dropdown menu.
Since I will need eight (8) columns for my gauges, I can add more by activating that Tablix header and RIGHT CLICK the last column, then select INSERT COLUMN, repeating until I have the eight total.
I see that there are two rows. A HEADER and DATA row. I will start bringing into focus the first cell and setting the HEADER columns as such:
- Instance
- Status
- CPU Percent
- Wait Percent
- Latency ms
- Memory Percent
- Disk Space Percent
- Disk Space Low
In the DATA ROW, I add a single TEXT BOX to each of the first two (2) columns.
I set both of these boxes to use an EXPRESSION by RIGHT CLICKING the popup menu.
On the STATUS COLUMN ONLY, I add a second EXPRESSION.
Hint: I add the second expression, by copying the first expression and pasting it into the same text box.
To configure the EXPRESSION, I right click and select TEXT BOX PROPERTIES, then click the FX ICON on the VALUE bar.
The FIRST EXPRESSION will display UP or DOWN.
=Switch(Fields!ClusterStatus.Value = "UP",(VbCrLf & "UP"),
Fields!ClusterStatus.Value = "DOWN",(VbCrLf & "DOWN"))
The SECOND EXPRESSION will display the TOTAL MINUTES UP only if the server is in the UP state.
I’ve added a line return (VbCrLf) to better center the data.
=Switch(Fields!ClusterStatus.Value = "UP",(VbCrLf & Fields!RunDuration.Value),
Fields!ClusterStatus.Value = "DOWN",(VbCrLf))
TEXT BOX COLOR: I also wish to change the color of the text box based on the server being up or down. I right click on the text box and select TEXT BOX PROPERTIES. Then the FILL sidebar item for FILL COLOR. I click the FX ICON and write some code to do this.
=Switch(left(Fields!ClusterStatus.Value.ToString,2) = "UP","Green",
Fields!ClusterStatus.Value = "DOWN","Maroon")
In the next five (5) columns, I add a RADIAL GAUGE.
I set each DIAL to the desired COLUMN to evaluate. (CPU, Waits, Memory, etc.)
I right click area, select GAUGE PANEL PROPERTIES and set FILL and BORDER to BLACK.
- Dataset Column Value to evaluate.
- Gauge Panel Properties
- Pointer Properties
- Label Properties
I use an EXPRESSION for the FILL COLOR on my POINTER. That way I know GREEN IS GOOD, YELLOW may need attention and RED alerts me.
=Switch(Fields!CpuPercent.Value < 75,"Green",
Fields!CpuPercent.Value > 75 and Fields!CpuPercent.Value < 90,"Gold",
Fields!CpuPercent.Value > 90,"Maroon")
I also use this same code to set the color of my LABEL.
The last column is the DISK WATCH. This will list drives by letter that are low on space. Here I click the UPPER RIGHT CORNER of the eighth cell and select the data point. In the PROPERTIES WINDOW, I set the color to ORANGE.
FINAL REPORT
Save and test the report.
To get it on the server, I will BUILD the PROJECT. Configure my connection the the SSRS server and DEPLOY the report.
If I wanted to deploy this to SSRS on an AWS RDS Server, I would create the SHARED DATA SOURCE on the server. I would BUILD the PROJECT and then in the BIN FOLDER, on my local workspace, I would find the RDL file and UPLOAD that to the RDS Report Server.
NEXT
In the next section (Part 10) I will go over how to make a SNAPSHOT of the data and some trouble shooting tips.