SSRS Cached Report Not Showing Document Map

Question

I'm running Microsoft SQL Server Reporting Services Version 15.0.1102.932 with SQL Server 2019 May 29, 2022 15:55:47. I've created a report and set the Caching option to

• Cache copies of this report and use them when available
• Cache expires after 30 minutes

When I run the report the document map shows. When I run the report again for the same parameters so it's using the cached version, the document map doesn't show. If I use different parameters so it won't use the cached version, the document map shows. If I set the caching to "Always run this report with the most recent data" then the document map always shows.

Any ideas how to get the document map to show on the cached versions?

Answer 1

Hi @Joyzhao-MSFT .

We have Execution Time in the header so I can see it is showing the cached version. Thanks for all the ways a cached report can be invalidated. I was aware of those but good to know I was correct. BTW, I've got Microsoft SQL Server Reporting Services Version 15.0.1102.932 installed locally and was able to reproduce this problem, and we've got Microsoft Power BI Report Server Version 15.0.1108.153 installed on several servers (dev, several testing servers, production), and had this issue on all of them.

This is really strange. We have been having this problem on many reports over the last two weeks and yesterday I was able to reproduce it all morning. Then suddenly it started working fine and I couldn't reproduce the problem. This morning I tried several reports that had the problem and they all work as expected, the document map shows all the time. However, on one report where we created a history snapshot on 10/16 the document map is not showing. But I just now created a new history snapshot and it does have the document map. This problem happened to multiple users on different machines and different browsers. We didn't reboot anything or clear anything out, it just suddenly started working as expected. Some of the reports that have this problem produce very large output, as in 800+ pages. We thought maybe that had something to do with it because the smaller reports always worked. But the history snapshot I created this morning has 9,024 pages and shows the document map. I also ran the report with the same parameters as the history snapshot getting 9,024 pages and it shows the document map on the cached version. It is very strange.

One question. In the ReportServer database there's the table SnapshotData. In that table there's a field HasDocMap. I noticed that is set to NULL for all reports, even ones that have a document map. Do you know anything about this table and field, and why it would be NULL when there is a document map? It's a bit field so would there be any harm in setting that to 1?

I'm going to continue working on this to see if I can reproduce it again, and if I can I'll be sure to post it. Thanks for your help!

Answer 2

Hey @Joyzhao-MSFT , found a way to reproduce this!

When we export the reports to Excel, we have logic in the DocumentMapLabel that says
=IIF(Globals!RenderFormat.Name = "EXCELOPENXML",Nothing, Fields!PAGR_ID.Value)

Once we export the report to Excel, if we go back and view the cached version OR the history snapshot again, the document map is gone!

Any thoughts? Is there a different/better way to turn off the document map when exporting to Excel that won't produce this problem?

Thanks!

Answer 3

@Joyzhao-MSFT , a few more thoughts...

I was looking at the ReportServerTempDB.dbo.SnapshotData table when I was running the report. The first time I run it I noticed a record got created. When I ran the report again with the same parameters, I noticed the field on that record TransientRefcount got incremented by 1. Each time I ran the report with the same parameters that field increased by one.

When I exported to Excel, a NEW record was created in that table. THEN when I ran the report again with the same parameters, the TransientRefcount on this new record got incremented.

SO, my theory is when you export to Excel (and probably PDF, etc), the report is run again getting the data again so it can render it in the desired format. It also creates a new cached record with the data for that export - including not having a Document Map. Then when you run the report again with the same parameters, it is grabbing the most recent cache, which is the one with no Document Map. So far the only way I can get the document map back is to wait for the cache to expire (or change the report, like uploading it again even if nothing on the report has changed).

I'm not sure there's any way around this. Sure feels like a bug. The export cache shouldn't be saved. I get it needs to get the data again, but it should throw away that cached data once it's done exporting.

Thoughts?

Answer 4

Hi @Richard Uchytil ,
Please try creating a timestamp in the Page Footer to confirm that the report being viewed is a cached report.

When you run a report for the first time, a cached report is created based on your settings. If the report is run a second time with the same parameters, the cache is not refreshed. A cached report is invalidated in response to the following events: the report definition is modified, report parameters are modified, data source credentials change, or report execution options change. If you delete a report that is stored in the cache, the cached version is also deleted.

In my test, I opened the report and selected two drop-down parameters. When I ran the report for the first time, I could view the complete document map. When I opened the report for the second time, the timestamp was displayed as the time of the first run of the report, and I still could check out the full document map. Therefore, the document map will normally be displayed on the cached report.

Please share more information to troubleshoot this issue.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.