Last updated on July 25th, 2022 at 09:28 am
In the screenshot below, you can see that there is clearly an issue with the Tools gauge in my SQL Server Reporting Services (SSRS) dashboard. The purple arrow is pointing out that the gauge is not displaying properly. What’s the matter with it? In this blog post, I will show you what the problem is and how to fix it.
The Problem
This is one of those rare times when I’ll show you a report that is incomplete. The final version of this report, Software Update Dashboard, is now in Enhansoft Reporting and it was a giveaway to attendees when we were at last month’s Microsoft Ignite.
If you follow my blog, you will know that I always recommend creating queries in SQL Server Management Studio (SSMS). Then I suggest moving queries from SSMS over to SQL Server Data Tools (SSDT) in order to create the reports. In my opinion, it is important to do it this way because you can ensure that the query returns the correct results and that the performance of the query is as expected.
Quickly looking at the dashboard above, you can see that it is for software updates (SU), and as you might expect, SU queries are a pain to get right. This query isn’t any different. I followed my proven process and tested it within SSMS first. The results were as I expected, so when I created the dashboard in SSDT everything looked good. I didn’t notice the problem with the Tools gauge until I started to get into what I call, “the tweaking phase.”
Troubleshooting
At that point I started to troubleshoot the problem. At first I thought it was the gauge itself. I tried different gauges, but each had the same result. What to do? These days, when I run into a problem with a query or dashboard, I take a break by walking around in order to get my 10,000 steps in for the day. It just so happened that while I was out on my walk it hit me that there was no percentage listed under the Tools header! I guessed that I was getting a, “Divide by Zero,” error. Fortunately, I thought at the time, this is relatively easy to fix.
After returning to my office, I checked the results of the SQL Server query. Do you see the problem in the screenshot above? Unfortunately, it was somewhat worse than a, “Divide by Zero,” error because there were no results for the Tools SU classification. The query was running perfectly. But since there wasn’t a SU with the tools classification it meant that the results showed up as NULL for all columns.
Solution
There are two solutions to this problem. The first solution is to ensure that the results are always returned from within the SQL Server query for all SU classifications. Even if the System Center Configuration Manager (SCCM) site doesn’t have this classification enabled. The second solution is to ensure that you test for both NULL and, “Divide by Zero,” within each gauge.
In most cases, I’d opt for the first solution and recommend updating the SQL Server query to ensure that there are no NULL or, “Divide by Zero,” issues.
Fixing the Tools gauge
The fix for the Tools gauge in my SSRS dashboard was really simple. I updated the SQL Server query by ensuring that it always returned results for all SU classifications. This was done via the SQL Server union command and a second SQL Server select statement.
Next, to ensure that I would never get a, “Divided by Zero,” error, I added 0.0001 to the total number of SUs. Since I’m using a single decimal place in my percentage results in the dashboard. Using such a small number will not affect the overall results of the percentages. This simple trick means that it is impossible to have a, “Divide by Zero,” error. If you look at the Tools classification in the above screenshot. You will notice that the number of installed SUs is 0 and the total number of SUs is 0.0001. Here’s how the equation works out: 0/0.0001 = 0.
Comparing the first screenshot in this post to this last one. You can see that I made a lot of changes to the final version of this dashboard. The Tools gauge now looks correct!
If you have any questions, please feel free to contact me @GarthMJ.
Do you have an idea for a blog post about a Configuration Manager query or reporting topic? Let me know. Your idea might become the focus of my next blog post!