SSRS parameter query with single value and multi-value variables?

Question

In SSRS, I'm trying to setup a parameter query on the dataset with single-value variable (x) and multi-value variable (y).

This is the last line in my Dataset query:
WHERE x = ? and y in(@programs)

For the Parameter setting of the Dataset Properties:
Parameter Name / Parameter Value:
@programs / expression: =Join(Parameters!programs.Value,",")

Under Parameters: @programs is set with Data type = Text, and Allow multiple values;
Available Values is set to Get values from a query; Dataset, Value field and label field are all pointing to the dataset.

When I run the report, the headings are displayed but there are no rows of data.

--
When I change the Parameter setting of the Dataset Properties to the following:
Parameter Name / Parameter Value:
@programs / [@programs]

When I try to run the report this Error message is displayed:

Cannot add multi value query parameter '@programs' for dataset 'Student_Agreement_Details' because it is not supported by the data extension.

Any idea?

Answer 1

I've been searching all over the web for a couple weeks now trying to find a solution. Please help.

Answer 2

Hi,

I think this question doesn't relate to SSAS?

What datasource are you using ?

I suspect the issue could be caused by the question mark in the where clause. I am not sure if this featyre could work in SSRS, did you have other component to pass the parameter ?

You could try get rid of the "？" sign to see if the dataset works or not.

Answer 3

Hi @BSA ，

As you mention the variable (y) is a multi-value, so I think you should use @programs / expression: =Join(Parameters!programs.Value,",") which is the first scenario.

When you run the report, the headings are displayed but there are no rows of data.

Have you tried to select all the values with the programs for test?

What's the x =? mean, do you mean x=‘？’，remove the x=? for single test.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

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.

Answer 4

WHERE x = ? and y in(@programs)

This is the same as

WHERE x = ? and y = (@programs)

so if @Programs has the value '1,2,3,4' you will get a hit if there is a row where y has that value. If you intended to get a get if y has any of the values 1, 2,, 3 or 4, you can do this on SQL 2016 and later:

WHERE x = ? and y in (SELECT str FROM string_split(@programs, ','))

For older versions you need to use a UDF, see http://www.sommarskog.se/arrays-in-sql.html for examples.