asked
SSRS with cached dataset, filter on Tablix
I've never used SSRS caching, and documentation I've read does not seem to fit my goal: I have a SSRS report based on a stored procedure. The procedure has an @emp_code parameter which the report user fills in to narrow the results to just their data (or if @emp_code is NULL, shows the full dataset). People were intermittently running the report until now - now I need to push the report to 300 individuals, which means each one would run the report with a different parameter value, rapid fire. It seems like I should remove the parameter from the stored procedure, and return the full dataset each time. Then the report could still have the parameter and simply filter on the Tablix itself. My thought was to have the report cache the data for 30 minutes whenever it is first run, then cache again the next time someone runs the report outside that 30 minutes. The documentation (as I read it) suggests that if I have a parameter in the report (regardless of the stored procedure having one) SSRS will cache a version for *each parameter value available* - is this true? Again - I'm not trying to cache individual users' reports - I don't want to store 300 versions - I'm only trying to cache the full dataset in hopes that randomly selecting a Tablix filter will yield a quick turnaround. If anyone knows how to do this, please advise. As a work-around (aka if this is not possible in the system settings), I could always create a second stored procedure that populates a cache table, and have that procedure run every 30 minutes. Then the report would just pull from the cache table. However, I'd rather trigger the cache by an actual request as - over the course of the week - most of the time no one will be running the report. Feast or famine sort of thing. Thanks for any thoughts or ideas!
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
I am clarifying my self. You want to send an email to 300 persons individually with their data only. If yes, then please check below steps it may help you. - Create email table, which will store data of how many user need this report - Create SP to store data in above table on some particular time using SQL JOB. - Create SP to pick up above stored data using SSRS email subscription, which has empcode - Now subscription will generate data empcode wise and send email with attachment as PDF to those persons
Thanks for the response! You seem to be in agreement with my work-around. So it does sound like the only way to cache just the dataset used (versus a cache of every permutation of Tablix filter) is on the SQL side of things. Adding a cache in SSRS will cause it to cache one-per-parameter value, which is too far along the process for my needs. Thanks!
20 People are following this question.
