Passing multiple values into sub report parameter SSRS

Question

I have a report which shows product details. Some of these products will be Children of Parent products. The children have 3 columns Parent1 Parent2 and Parent3.

The sub report runs off the same query except has a variable @ParentCodes. This is set as varchar(max). I then use a function in the where clause to split the codes which may be passed ParentCode in (SELECT [Value] FROM dbo.SplitMultiValueParameterString(@ParentCodes, ','))

If I pass in 2 or 3 codes in a string like ('123', '456') then the query returns the 2 rows required.

I am struggling to get this to work in SSRS. I have set up both reports and added the @ParentCodes parameter to the sub report.

In the main report, I am using an action on a text box to go to the sub report and passing the parameter @ParentCodes with the following value expression =Fields!Parent1.Value & Fields!Parent2.Value & Fields!Parent3.Value

In the sub report, in the parameter tab in the dataset I have added the @ParentCodes parameter and using the expression =JOIN(Parameters!ParentCodes, ",")

This is not working as desired, can anyone understand what I am trying to do and propose a solution?

Larnu Larnu · Accepted Answer · 2020-08-27T11:48:16

In SSRS, if you are using direct statements (in that you just dump the raw SQL into the Report Data) then you need to use an IN, and SSRS (as much as I hate it) "safely" replaces the variable in the IN with an injection of the parameters. In simple terms, take a query like the below:

SELECT *
FROM dbo.YourTable
WHERE ID IN (@ID);

Then you select the parameters, 1, 2, 3, and 4 in the report. It would inject those and the query would become the below:

SELECT *
FROM dbo.YourTable
WHERE ID IN (1,2,3,4);

If you're using a Stored Procedure, however, you need to use a string splitter (such as DelimitedSplit8k_LEAD) to do the work, and make your parameter a (n)varchar:

CREATE PROC dbo.YourProc @IDs varchar(8000) AS
BEGIN

SELECT *
FROM dbo.YourTable YT
     JOIN dbo.DelimitedSplit8k_LEAD(@IDs, ',') DS ON YT.ID = DS.item;

Passing multiple values into sub report parameter SSRS

3 Answers