Creating a report with SSRS that iterates over a parameter

3
votes

I am modifying an existing report which is setup to let you view statistics per sales rep. You do this by changing the parameter sales rep in the report view.

I need to modify the report so that it can display a page for each sales rep. This will then be exported to Excel with each sales rep statistics being displayed in a separate tab.

I have researched this and found people talking about iterating over a report but I don't think that what they are referring too is what I am asking:
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/27ffabcc-6286-4fed-a8bf-0e5d78560be3
http://jameskovacs.com/2005/08/04/digging-deep-into-reporting-services/

2
reporting-servicesreportingssrs-2008
Can you explain what you need a bit further, for example by providing examples? At the moment it's not clear whether you want a page for each report parameter (or why you would need a page for each one), a page for each separate value of a multi-select parameter, or something else. - user359040

2 Answers

2
votes

You can accomplish this several ways, so here is 1 suggestion

  • Create a new tablix report whose query returns a (unique) set of all sales reps
  • group by sales rep
  • there is no detail row, but in the group footer/header add a subreport
  • the subreport will be the report you already are using and it's parameter will be the sales_rep value from your main report query
  • set page breaks for the group
0
votes

The main way I can think of doing it is to pass the parameters into the stored procedure, and in there have logic to UNION together all the possible results you want with a field for grouping the sections.

For example if they had a parameter for "Report Sections" and had selected "1, 2, 3", then you pass the parameter to the stored procedure; inside it uses a SplitString routine to convert the commas into a table format, and then constructing another temp table to hold the results. Everything is accumulated into there with a SectionNumber field (1, 2, 3, etc), and then the whole lot gets returned. Your group groups on that, with page break set, and that's it.