Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSRS 2008 Snapshots for data driven subscription Expand / Collapse
Posted Wednesday, April 17, 2013 8:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 28, 2016 2:24 PM
Points: 5, Visits: 37
I have a business unit client who needs to produce 5 reports each month for each of 120 users (total 600 individual report instances). The user is a parameter to the reports, and each user can see only his data, or that in levels below him in the hierarchy. Because each report's data is organized in a hierarchical fashion, the roll-up/drill-down interactive functionality that you get with the SSRS report viewer is critical. My business unit client has set up a data driven subscription to generate the reports, but the DDS puts out the individual reports to an Excel file on a network file share, so we lose the roll-up/drill-down functionality. If each user runs his 5 reports interactively, 1) each report takes about 5 - 10 minutes to gather its data and render, so the performance is unacceptable; and 2) how can we limit each user to running each report for only HIS data? Is there any way we can use a data-driven subscription to produce snapshots of each report on a scheduled basis, so that each user can then just pull up the most recent snapshot of HIS reports in the SSRS report viewer, so that he still has roll-up/drill-down?
Post #1443293
Posted Friday, April 19, 2013 11:28 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:47 PM
Points: 560, Visits: 1,304
Since your query already seems to take into account the user, I think you can use the @UserID built-in field in the SSRS report to populate an internal parameter (let's call it "@DomainName"); you may need to trim that username to remove the domain portion, depending on what your dataset looks like.

Then you can pass that @DomainName to the query like you seem to be doing now just in the subscription. Make sure to set @DomainName first in the parm list so it's the first thing that populates when a user hits the report, and then you can filter the other parms and/or data automatically.

Hope that helps or at least points you in a direction. I'm not sure about the snapshot stuff but I think that's also possible.

Post #1444543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse