CODEWITHSUNDEEP
reporting-services
Chris
Chris

Reputation: 352

Creating snapshot reports when subreports require parameters?

My situation is this - I'm using SSRS to create a "cascading" report set. The top-level report pulls all of the data from a table that capture our deployment/release activity. This data includes the property that executed a release, the organization to which that property belongs, the scheduled and actual start and end times of the release (and its constituent deployments), etc., etc., etc., and in the report I aggregate the data by time windows (Fiscal Year, then Quarter, then Month). So, at the top level, the user sees a report that shows how many deployments and releases we executed, how long they were expected to take, and how long they actually took, over the last twelve months, grouped, again, by Fiscal Year, then Quarter, than Month.

Now, the next level of detail the user wants to see is for a given month. When he or she clicks on the link to run that particular sub-report, they now get the same information (number of deployment, number of releases, scheduled and actual durations, etc.) grouped by organization, for that particular month.

Here's my dilemma - the initial report takes a LONG time to render. I would like to create snapshot report to reduce this render time, but my sub-report uses the Month from the top-level report as a parameter. In other words, when I click on the link to the sub-report, the Action setting in SSRS says to use the Month that I clicked on as a parameter to generate the sub-report, and the query in the sub-report filters off of that parameter with the following query condition:

AND FiscalQuarter IN (@Quarter)

So, the reporting works fine, but I am wondering - is there some way that I can get ALL of the data, for ALL quarters, into the sub-report in a snapshot report, and then just generate that snapshot but filtered out for the quarter value that I pass from the top-level report as a parameter?

Upvotes: 1

Views: 2103

Answers (2)

Azhar S
Azhar S

Reputation: 46

yes you can, by creating a snapshot for sub report for all values. and the parameter defined on the subreport should be a dataset filter not a query parameter .

Now when you pass parameter from main report to subreport , it will filter it accordingly on the sub report snapshot without having to execute the costly sql

Upvotes: 3

Jeroen
Jeroen

Reputation: 63729

Alas, reporting services does not provide what you need. Caching reports will create a cached version of the report per distinct combination of parameter values, and a similar situation holds for cached datasets.

You have roughly two options left it seems:

  1. Use a cached dataset, have the subreport use the cached dataset but only use the parameter to filter the data once it's retrieved (e.g. in a tablix)
  2. Resort to a more database-oriented caching-like or performance enhancing strategy such as for example indexed views for your queries

Upvotes: 1

Related Questions