SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems with Caching SSRS report


Problems with Caching SSRS report

Author
Message
tim.short
tim.short
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
tim.short
tim.short
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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??
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search