Multi-value parameters in rdl not working

Question

Multi-value parameters in rdl not working

Dom 976

I have a report that runs customer activity based on promotion codes. I want the user to be able to select the parameter values from a list that I specify. In the parameter properties|General tab, I check off Allow Multiple Values. In available values, I add two labels and values. When running the report, the parameter dropdown displays my two values. If I select either of the values individually, the report runs fine. However, if I select BOTH values (or Select All), the report returns NO data. What am I doing wrong? What can I show you to help me troubleshoot?

0 comments

2 answers

Your answer

Answer 1

Anonymous

Hi @Dom
I agree with Olaf. When adding a multi-value parameter, the text of the query variable must contain the IN operator.
Like this:

This official document explains how to add multi-valued parameters: add-a-multi-value-parameter-to-a-report.
Hope this can help you.
Best regards,
Aniya

Dom 976 Reputation points

2022-10-27T14:19:27.557+00:00

That is what I was afraid of. The report (which I inherited) uses a pretty complex stored procedure that loads this "code" field through five different iterations of tmp_tables. So the "code" field exists in the dataset in Visual Studio. I was hoping that by clicking on Allow Multiple Values that I (or the user) could simply type a comma separated list into the @Aidan Wick parameter field (or select multiple values from the drop down).

Not sure I'd be able to modify the SP (I'm not a programmer). But I was thinking I might create another dataset that loads a query that fetches all the available codes and uses that to populate the parameter?
Anonymous

2022-10-28T02:31:42.87+00:00

Hi @Dom
Yes, you can pass parameters by creating a query dataset, but ultimately you still need to pass parameters to the original query. According to your description, you want to use a stored procedure to create multi-valued parameters, right? Then you can refer to this link: ssrs-multi-value-parameter-using-a-stored-procedure.
Best regards,
Aniya
Dom 976 Reputation points

2022-11-03T16:53:09.42+00:00
Thank you for the link. Looks like there are quite a few solutions. If I could: Is there one that you would recommend for an experienced query developer but a beginner programmer? I was thinking of trying this one but I'm not sure if it is above my experience level:

In the SSRS dataset properties, pass the multi-value param to the stored procedure as a comma-delimited string

In Sql Server, you need a table-value function that can split a comma-delimited string back out into a mini table (eg see here). edit: Since SQL Server 2016 you can use the built-in function STRING_SPLIT for this.

In the stored procedure, have a where clause something like this: WHERE sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,','))

... where ufnSplit is your splitting function from step 2.

Thanks
Anonymous

2022-11-04T08:54:02.19+00:00

Hi @Dom
I used a method to do a simple test for you.
First add the parameter in the stored procedure.

Then check "Allow multiple values" in the parameter properties, and add the following parameter expression on the parameter page of the dataset.

Preview:

Best regards,
Aniya

Answer 2

Olaf Helper 47,616

the report returns NO data.

Then your SQL statement using the parameter is wrong. I guess you used and equal = compare, it should be an IN operator instead, like

WHERE Field IN (@parameter)

0 comments

Share via

Multi-value parameters in rdl not working

2 answers

Your answer