When I first encountered this issue, some time ago, I checked all of my queries in SQL Server Management Studio (SSMS) and guess what? As I suspected, the queries were 100% correct! I was troubled because I couldn’t figure out why, if my queries were correct, I was getting incorrect results in Visual Studio. The dashboard showed me one thing and the drill down report always gave me different results. I banged my head against the wall because I knew that the results should match!
The report “magically” fixed itself, so at the time I put the answer down to having a bad day. When it happened, again, I knew the reason wasn’t a bad day, but then I stumbled across the answer. It’s an extremely simple fix! This issue with Visual Studio gets me almost every time, so I put together this blog post in case you ever encounter this problem too.
Here’s what’s happening: Visual Studio is caching the results of your query in a file so that your reports run faster while you are creating and updating them. The simple fix is to force Visual Studio to query the database, again, by using the refresh button! There is one gotcha with drill throughs. You can’t force a drill through to re-run. You must go back to the original report and force the report itself to refresh.
Fortunately, this problem does NOT happen once the report is deployed to SQL Server Reporting Services (SSRS). This problem appears in Visual Studio, but I’m sure that it also happens in Report Builder.
Incorrect Results in Visual Studio
Let me show you the problem. The gold arrow, in the screenshot above, is pointing to the number 3. This is the number of computers with unknown software update scan results. When I click on the number, I see the drill through (below).
What the…? The drill through says that there are only two computers with unknown software update scan results! Where is the third computer?
As I said, the solution is very simple. Just click the refresh button on the tool bar. This forces Visual Studio to re-query the database and not to use the cached results. Remember, though, what I said about the gotcha for drill throughs.
You can’t force a drill through to re-run, so you must go back to the report file and force the report itself to refresh. You can see what I mean by taking a look at the GIF above. I drill through from the Software Update Health Dashboard, which shows 3 computers, to the List of Computers with Unknown Software Update Scan Results report, which shows 2 computers. Notice that I hit the refresh button a couple of times on the drill through, but the results are the same. Next, I go to the report itself and refresh the results there. Once I hit refresh in the report itself, everything is great. Finally, three computers appear in the drill through report.
Fortunately, this is one annoying issue that is super simple to fix, once you know what’s happening.
What other annoying Visual Studio/SSRS issues are you seeing? Let me know by either using the comment section below or tweet me @GarthMJ. I look forward to hearing from you.