Per user snapshots? Or multiple snapshots for a report each with different parameters at the same time
I've received questions in the past about whether the report server supports per user snapshost - that is report snapshots customized with different parameter values for a given user. The answer I have always given is no - you get one snapshot per report and that snapshot always uses the default parameter values.
Well, it turns out that some inventive users have come up with a 'work around'; it involves some coding on your part. But here's the sketch:
Though RS supports only 1 *execution* snapshot per report, it supports N *history* snapshots per report. Also, you can cause a history snapshot to be created programmatically. So there we have it - "all" you need to do to create a set of history snapshots with different parameter values is automate a few steps:
1) Set the default parameter values for the report using the SetReportParameters SOAP API
2) Call the CreateReportHistorySnapshot SOAP API after step 1.
3) Repeat - as in lather, rinse, repeat ;-).
The reason we've found out about this workaournd is that there is one catch - history snapshots have a resolution of 1 second in reporting services. That means that if you try to create 2 history snapshots within the same second (time), the second (order) request is *ignored* - CreateReportHistorySnapshot returns the same history id for the second request as for the first request. You'll need to add a delay in your code to compensate. Based on the comment below from b5lurker, a delay of 1.001 seconds works well in practice.
Finally, since History Snapshots are identified in RS only by their ID and the time they were created, you'll need to be able for your end users to correlate the history snapshots with their parameters. You can do this in multiple ways - store the correlation in a database somewhere, flat file, etc. Of course you could do it all within RS by storing the information as custom properties on the report itself by using the SetProperties SOAP API.
You'll also likely need to create some management/viewing UI for controlling how many of these 'history' snapshots are stored on the server due to size limitations. You'll also want to have the UI allow end-users to view the right snapshot.
Finally, you should consider this a work around not a recommended solution – due to the idea represented here if you have a set of interactive users trying to browse the report live, while you’re generating your history snapshots, they might see wildly different default parameter values.
Kudos go out to Hi_i_am_Amitandb5lurkerfrom the MSDN Reporting Services forum that brought this workaround to our attention via their debugging efforts. Here are their threads, respectively. You may want to ask them what else they did to make this solution really work in their environments.
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1077593&SiteID=1
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379728&SiteID=1
Take care and good luck,
-Lukasz
Comments
- Anonymous
January 12, 2007
I may be missing something, but would this be an easier solution:
- Turn on caching for your report.
- Create a data driven subscription which delivers to the null delivery provider. It will run the report once for each parameter combination you need. Now when the user runs the report, it won't actually need to hit the datasource, just the SSRS cache.
- Anonymous
January 14, 2007
The comment has been removed - Anonymous
January 16, 2007
Thanks Lukasz for providing visibility to this issue. I am the same b5lurker from the MSDN forum post that you linked to. Is there any possibility of this being addressed in a future release of SSRS? In the MSDN post I mentioned that you can see a GUID being created for each snapshot in the History table, so I would think that this should be used instead of date/time. I also mentioned this issue to Brian Welker at TechEd Boston last year. We have our console app working fine and avoiding the duplicate calls by having it delay by at least 1 millisecond longer then 1 second before making the next request. The console app is still multi-threaded, but only to adding entries into an MSMQ queue for each report. It would be great if we could remove the delay once this issue is resolved and the snapshots can be referenced by GUID instead of date/time of generation. Thanks, Steve