Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problems with Caching SSRS report Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 6:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:59 AM
Points: 6, Visits: 705
Hi all,

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

Tim Short
UK
Post #1548243
Posted Tuesday, March 11, 2014 4:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:59 AM
Points: 6, Visits: 705
Update - it looks like whichever site is being cached last is the one being displayed.
So the cache refresh plan has Sites 1-3 being created at 5 minute intervals, but when I run the report the data for site 3 is being displayed, no matter what site I select in the report criteria.

I've never seen this behaviour before - it's almost like the cache is being over-written every time rather than a new version being created.
Any-one got any ideas??
Post #1549628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse