Last updated on September 28th, 2022 at 09:34 am
A super-cool feature with SQL Server Reporting Services (SSRS) is all of the available options for creating dashboards and reports! For example, did you know that you can hide an SSRS gauge in a dashboard based on the results of a query? Or, that you can hide an SSRS gauge based on the items selected within a prompt?
I showed you how to make a gauge appear less plain-Jane in a recent blog post called, “How Can I Update the Plain SSRS Gauge in a Dashboard?” The dashboard example that I used in that post was Overall Software Update Status Dashboard from Enhansoft Reporting. Originally when I was building and testing this dashboard, a number of testers had questions about the software update categories. They told me that they either didn’t deploy all of the categories or their Manager didn’t want to see any categories in a report other than Critical Updates, Security Updates, and Upgrades. A number of times I heard, “How can I make the dash only show specific software update categories?”
In this blog post, I will show you how to hide an SSRS gauge in a dashboard/report.
Hide an SSRS Gauge
The process for hiding an SSRS gauge is fairly simple. Using the Overall Software Update Status Dashboard as my example, there is a multi-select prompt for Software Update Classifications (see the screenshot above). One or more values can be selected, so it only makes sense that you can turn off the gauges for those values that are not selected.
For the purposes of this blog post, I will show you how to hide the WSUS Infrastructure Updates software update classification. You will need to repeat these steps for all of the gauges and objects you want hidden.
It might seem odd that I mentioned objects, but if you look at the dashboard, each gauge has its own tablix associated with it. Below, I highlighted the tablix with a purple arrow.
How to Determine If WSUS Infrastructure Updates Is Selected
Unlike a single item prompt, when the prompt is a multi-select prompt, you need to look at all of the selected items and then determine if the one you want hidden (in this case WSUS Infrastructure Updates) is selected. The equation to do that would look as follows:
InStr(JOIN(Parameters!Classification.Value,”,”),”WSUS Infrastructure Updates”)>0
If the value is greater than zero, then it means that WSUS Infrastructure Updates exists.
Next, you need to add IIF to the equation. This allows you to turn the object off or on. The equation now looks as follows:
IIF(InStr(JOIN(Parameters!Classification.Value,”,”),”WSUS Infrastructure Updates”)>0,False,True)
How to Hide a Tablix
In order to hide a tablix, start by selecting the tablix, right-clicking and then select Tablix Properties…
On the Tablix Properties page, select the Visibility node.
Select the Show or hide based on an expression radio button. Now you can paste the equation into the text box. Don’t forget to include the equal sign, “=” at the beginning of the line.
Clicking on the OK button completes this task. The tablix will now automatically hide when the object is not selected.
How to Hide an SSRS Gauge
The process for hiding an SSRS gauge is very similar to the one for hiding a tablix.
Start by selecting the gauge, right-click, select Gauge Panel, and then select Gauge Panel Properties…
On the Gauge Panel Properties page, select the Visibility node.
Again, select the Show or hide based on an expression radio button, and paste the equation into the text box. Remember to include the equal sign, “=” at the beginning of the line.
Clicking on the OK button completes this task.
If you look at the header of the dashboard above, you’ll notice that under the Classifications section, WSUS Infrastructure Updates is not listed. The tablix and gauge are also hidden in this report.
The one item that still needs to be updated is the grey, “Classifications,” line; it extends beyond the Feature Packs gauge. This can be fixed with the CanGrow and CanShrink options on that line. I hope to cover these items in a future post.
If you have any questions, please feel free to contact me @GarthMJ.
Do you have an idea for a blog post about a ConfigMgr query or reporting topic? Let me know. Your idea might become the focus of my next blog post!