CODEWITHSUNDEEP
reporting-servicesssrs-2012
David B
David B

Reputation: 5

Run SSRS report for all parameters, then each parameters

Is it possible to have a report that runs for all the parameters, and then runs through the report for each parameter?

I'm looking to generate 1 report where the first worksheet has the data for all the selected parameters. If more than 1 parameter is selected, the second worksheet would have all data for a first parameter, the third worksheet would have the data for a second parameters, and so on. The report would generate when a user needs to run it so I cannot set it as a subscription, and the report should only export to one Excel file with multiple worksheets.

Upvotes: 0

Views: 957

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

It sounds like you want to use a LIST object to iterate through all of the selections of a single parameter for a table or set of tables and charts. Let me know if I have misinterpreted (as Steve may have).

For example, if your parameter is for employees and you want to see data for each selected employee, you would filter the data for the employees selected, then use a list to go through your query one employee at a time - grouping the list by your employee ID field.

Inside the list object (which is just a table with one column), you can add your charts, tables and text boxes you need just like the report body.

For your idea of having a combined total page first, you would make the page that you want for ALL first then copy all the objects and put them into the list for the single employee version. The first page (without the list) would aggregate all of the data into a single page. The list would then display the same page but filtered for each selected employee, one at a time giving you as many pages as selected employees (plus the initial overall page).

Remember to set PageBreaks to Between each instance of a group in your List and add a PageName property an expression that makes the name unique for the Excel tab names. You can also hide the list if there's only one employee selected - Parameters!YourParameter.Count = 1.

For examples and more detail:

MSDN Lists (Report Builder and SSRS)

SSRS -- Using a List Item to Display Details - YouTube

Upvotes: 0

Related Questions