1

Guys sorry for the general question here but I have been looking on the internet and cant find a solution.

I have an SSRS report. There is one Parameter user must put in before running, @Location.

In the query its specified to only return results where that location is matched. I.E. where Company.location = @location

The results are displayed in a table. This all works fine, but I want to change the parameter to contain multiple values.....and then repeat the table for every location the user chooses.

Any help would be appreciated.

user2371384
  • 49
  • 2
  • 6

1 Answers1

4

Step 1: Create a dataset for your multi-valued parameter, something like this:

SELECT LocationId, LocationName FROM MyLocations

Step 2: Create the parameter @LocationId, with available values from the above dataset, and set it to allow multiple values.

Step 3: Create another dataset along these lines for the actual table:

SELECT *
FROM MyDataTable tbl
WHERE tbl.Location IN (@LocationId)

Step 4: Create a List, bind it to the second dataset (select the list, find the "DataSet" property).

Step 5: Open the properties for the Details of the list, group on LocationId.

Step 6: Drop a tablix inside the list, and pick your fields from Dataset2 for that tablix at your leisure.

Step 7: ...

Step 8: Profit!

PS. There are several alternatives available to do this, including:

  • Use a list for the location in combination with a subreport for the actual table.
  • Use one big table, with groupings for location.
Jeroen
  • 60,696
  • 40
  • 206
  • 339
  • I'm imagining if I try the one big tablix option then with the correct formatting it should be possible to make it look like lots of little tables? – whytheq Sep 26 '14 at 10:25
  • @whytheq yes, I've done so in the past. Blank rows between groups can help to do this. – Jeroen Sep 26 '14 at 11:18
  • @Jeroen on another issue what do you use to combat parameter sniffing in SSRS reports? Does `OPTION (RECOMPILE)` help? – whytheq Sep 26 '14 at 15:41
  • @whytheq Not sure if comments on this question are the place to discuss new questions. You should try either a chat room (if your question is open ended), or post a new question if you have a concrete issue/problem. Good luck! – Jeroen Sep 26 '14 at 16:09