I've got a problem I nned some help with.
I'm designing a SSRS Dashboard report consisting of one table and several graphs.
There are 2 parameters for the report: Date and Site. Site can be either a total for the two sites or the individual site total. I've set the defaults to be 'Yesterday' for the date and the Combined Site value.
The report takes about two minutes to run, and so I thought I'd cache it.
I've cached several other reports with no problem.
I've created three different cache refresh plans - one for each of the three site values, all using the default date. I also set the cache to expire on a daily basis.
I waited until the cache plans had run successfully, and then viewed the report.
The report for the 'Site total' value ran fine, so I changed the drop down to be Site 1 and pressed the 'View Report' button. The report came up in less than 2 seconds as you would expect, but the values and graphs were exactly the same. The same happened for Site 2.
The reports produce the correct values in Visual Studio, and in the browser when cacheing is turned off.
I've tried various changes to the report (shortening the report name, having the dates selectable from a drop down list rather than a calendar control, ensuring the Site parameter gets the data from a query rather than being hard coded) but to no avail. I always make sure I delete the report from the web server after I change the parameters, as I know that can be a 'gotcha'.
I've also had the other developer in the office look at the report, and he can't see anything obviously wrong.
I have full Admin rights to the Reporting server
I've looked at the Execution Log 3 query on the reporting server, and can see the caches being created and the correct values being used when I run the report.
If anyone can come up with an answer I'd really appreciate it.
Thanks in advance