SSRS report using a Stored Procudure. - Passing Multi-Valued Parameter -- I thought you HAD to add a JOIN clause for the parameter in the RDL?

Question

SSRS report using a Stored Procudure. - Passing Multi-Valued Parameter -- I thought you HAD to add a JOIN clause for the parameter in the RDL?

LH 26

I've been developing SSRS reports and I thought in an RDL, you HAD to use a JOIN clause for a Stored Procedure data set that consumes a parameter referencing a Multi-Valued report parameter
-- like this =Join(Parameters!ClientCountry.Value, ",")

...And then a String_Split within your Stored procedure

Is it correct that in the RDL for a Stored Procedure data set - you just have to reference the Multi-Valued parameter like [@ClientCountry] and NOT =Join(Parameters!ClientCountry.Value, ",")?
If you need to use a different delimiter, like a Pipe delimiter, do you then HAVE to set the Stored Procedure data set parameter values to =Join(Parameters!ClientCountry.Value, "|")?
Whatever the answer, has it always been like that or did something change in an SSRS version?
Does it matter if the Multi-Valued report parameter feeding that SP gets its value from an SP itself or a Text query?

Thanks for your response!

3 answers

Your answer

Answer 1

Joyzhao-MSFT 15,631

Hi @LH ,

Pass the multi-value parameter to the stored procedure as a comma-separated string by setting the parameter expression in the SSRS Dataset Properties: =JOIN(Parameters!ClientCountry.Value,",")

2) If you need to use a different delimiter, like a Pipe delimiter, Please change =JOIN(Parameters!ClientCountry.Value,",") to =JOIN(Parameters!ClientCountry.Value,"|"), and change the stored procedure to use a where clause like this:

Where prod.Color in (Select value from string_split(@Color,'|') where RTRIM(value)<>'')

That is, it uses string_split to split the passed value into a table, then selects from that table and checks for membership with 'in'.

3) Since SQL Server 2016, you can use built-in functions for this.

4) We cannot call the stored procedure in Text query, please select "Stored Procedure" in the dataset query type.

Please refer to the following article : SSRS : How to pass multi-value Parameter to a Stored Procedure.
Best Regards,
Joy

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

LH 26 Reputation points

2022-09-14T16:09:49.627+00:00
@Joyzhao-MSFT That is the way I learned to pass in the Multi-Valued parameter into the SP

BUT when I test using SQL profiler...I see that I did not have to use =Join(Parameters!ClientGroup.Value, ",")
That just using the [@ClientGroup] in the Query Parameter Values passes in a comma delimited value to the SP

So is there a reason it is preferred to use the =Join(Parameters!ClientGroup.Value, ",") as opposed to the [@ClientGroup] in the Query Parameter Values for the Stored Procedure?

Example from SQL Profiler using the [@ClientGroup] and [@DebtorCode]:
Joyzhao-MSFT 15,631 Reputation points

2022-09-15T02:14:29.377+00:00
@LH ,
At first, we need to understand the following points:

When the multivalued parameter is selected, the selected parameters are stored as an array, but the array cannot be passed directly to your statement. Therefore, it is not suitable to pass array values directly, we need to concatenate all the values into a string by using =JOIN(Parameters!ClientCountry.Value,",") , and then pass the string to the storage process.

However, after passing the string to the statement, it cannot be filtered. At this time, you need a function that can extract all the values in the string, namely string_split, and then use the in condition in the where clause.

I'm not sure whether your report is working well with the parameters selected, but if you create a multi-value parameter in this way, please follow my answer above to set it up.
Joyzhao-MSFT 15,631 Reputation points

2022-09-15T03:20:02.717+00:00

The following information exists in this official document:
For information on how to pass multiple parameter values to a stored procedure, see Working With Multi-Select Parameters for SSRS Reports on mssqltips.com.

The documentation mentions that it needs to be passed to the stored procedure using a JOIN expression.

Answer 2

LH 26

Thank you @Joyzhao-MSFT ,

I do understand what you are saying about the Multi-Value parameters stored as an Array.
However, if that is true, then why for the dataset with the Stored Procedure that accepts Multi-Value parameters [@ClientGroup] and [@DebtorCode] -- why would SSRS pass in Comma delimited strings as seen in SQL Profiler since I am not using =JOIN(Parameters!ClientCountry.Value,",") but instead padding in [@ClientGroup]?
In this image, the SP with string values being passed in is copied over from SQL Profiler -- that is what is actively being passed when I run the report.

LH 26 Reputation points

2022-09-20T19:02:21.18+00:00

Hi @Joyzhao-MSFT , as I mentioned before -- without doing that JOIN, the values are being passed in a comma delimited string. -- by just referencing [@ClientGroup] in the Dataset parameter value for the Stored Procedure dataset. SQL Profiler examples I have shown above prove this.

Are you saying it is simply best practice to use the =JOIN(Parameters!ClientCountry.Value,",")?

Because the other way (Not doing a join but simple referencing the Multivaluedparameter) works, I'm just not sure why if what you state above is true that a multivalued parameter is storing the values as an Array and therefore cannot be passed directly to the Stored Procedure dataset.

Answer 3

I am working on this now, and I have followed all instructions

Multi-valued parameter with JOIN(Parameters.pMultiSelect .Value,",") in the Expression in SSRS
Putting the values into a TEMP table in the Stored Procedure
using (Select value from string_split(@pMultiSelect,',') where RTRIM(value)<>'')
Joining Temp table to real table to return the correct values

If I run the Stored Procedure, and set the value of the Parameter to N"Value1,Value2,Value3" (meaning I am feeding it a list of comma separated values directly) in Query analyzer the SP runs successfully

If I set the value of the Multi-Select Parameter to Value1,Value2,Value3 - then execute the Report it throws no error and returns NOTHING

As troubleshooting I have the JOIN(Parameters.pMultiSelect .Value,",") showing on the header of the report and it returns Value1,Value2,Value3 correctly

If I look at SSRS execution log, the parameter is NOT being passed as pMultiSelect =Value1,Value2,Value3 but is instead being processed as pMultiSelect=Value1&pMultiSelect =Value2&pMultiSelect=Value3 so it is splitting it up front and as a result, the query returns nothing.

So, I have no idea why it is doing that, but I have reviewed all the available articles and I believe I am doing everything correctly. This is SSRS 2019

Devin McMahon 0 Reputation points

2023-06-20T18:45:36.6466667+00:00

Please disregard the above - there was a simple reason why it was appearing to return no data, it was a problem with the design of the report. My apologies. I can confirm that the steps described above work with SSRS 2016 (Not 2019)

Share via

SSRS report using a Stored Procudure. - Passing Multi-Valued Parameter -- I thought you HAD to add a JOIN clause for the parameter in the RDL?

3 answers

Your answer