CODEWITHSUNDEEP
reporting-servicesparameterssql-server-2008-r2bidssubreport
JLR
JLR

Reputation: 11

Sending multiple values in ssrs parameter to subreport

Currently have three subreports, one main report.

Main report has two parameters - SELECTDATE and EMP_ID. Main report sends Order_Nbr to all subreports.

All subreports work perfectly when I only select 1 Employee and 1 date, but if I choose multiple values it blows up.

SQL has the column as an INT. I have both parameters in main report and subreport, SELECTDATE is set as Text with Multiple Values, and EMP_ID is set to Integer with Multiple Values. My queries has my date IN (@SELECTDATE) and emp_id IN (@EMP_ID).

It obviously sends the correct information to the subreports because it works, but I would like it to work with more values being passed. Love the current ability to check and uncheck employees and end of month dates, like it currently is set using the IN function in my query.

Upvotes: 1

Views: 15191

Answers (3)

hessenfloh
hessenfloh

Reputation: 3

I used the following solution, which works in SSRS2016. This also works with text parameters.

  1. Like suggested above, pass the parameter as a string to the subreport by JOINing the values.

Join(Parameters!EmpID, ",")

  1. In your subreport, accept the parameter as text.
  2. In the SQL of your subreport, use the string_split function of SQL 2016 to return a table of the values and simply join it to your main query. So basically if your parameter is named "EmpID_Multi" do

... JOIN (SELECT value FROM string_split ( @EmpID_Multi, ",")) mv ON mv.value= ...

Note: You might consider pulling the values into a temporary table for SQL optimization (sometimes the optimizer does funny things...).

Upvotes: 0

user007
user007

Reputation: 1730

I guess you have not set the parameter in the sub report as Multi Select. If you have set the parameter in the sub report as multi select, then you could just send the parameter from the main report to the sub report as it is. See more at here

Upvotes: 0

David Lawson
David Lawson

Reputation: 796

Make the Parameters on your sub report non-multivalue, remove any 'Available values' set. Pass the multivalue parameter from you parent report as a string using the join method

=Join(Parameters!Emp_ID,",")

The EMP_ID parameter will be set to a comma delimited list, which is what a multivalue parameter sends to the query

I'm not sure how this works with text queries, but it works with stored procedures.

If the sub report is also used as a stand alone report you will need to add a new parameter to allow the user to send parameter values to @Emp_Id from a parameter the user can set

Upvotes: 4

Related Questions