April 17, 2013 at 8:22 am
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?
April 19, 2013 at 11:28 am
Since your query already seems to take into account the user, I think you can use the @user-id 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.
Cheers,
Eric
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply