CODEWITHSUNDEEP
reporting-servicesreportingssrs-2008
Daniel Compton
Daniel Compton

Reputation: 14569

Creating a report with SSRS that iterates over a parameter

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/

Upvotes: 3

Views: 3976

Answers (2)

Ben English
Ben English

Reputation: 3938

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

Upvotes: 2

Cody Konior
Cody Konior

Reputation: 726

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.

Upvotes: 0

Related Questions