Using a list parameter in SSRS

Question

I need to be able to set an SSRS parameter to contain multiple values.

In TSQL, I can have a where clause that states:

where Attribute in ('Value1', 'Value', 'Value3')

What I need is in SSRS to have:

where Attribute in (@Attribute)

Where I am getting hung up is how to format the parameter value expression so that SQL sees it as: 'Value1', 'Value', 'Value3'

I have had some luck making the where clause look at only the first value, but I need it to look at all 3. How would I format the expression to do that?

I would just allow it to accept multiple values, and check each value individually, but I need the drop down list to have groups. So, if the user selects GroupA, the where clause uses: IN ('Value1', Value2') and if the user selects GroupB, the where clause uses a different list for the IN.

Hopefully it's just a matter of formatting the expression correctly.

Lamak · Accepted Answer · 2013-04-02 19:18:35Z

2

Well, if you didn't have the requirements about groups, this wouldn't be an issue since all you need is to make your parameter a multi-valued one, and on your dataset query do WHERE Attribute in (@Attribute). But taking that requirement into account, the only way I can think of, is to have two multi-valued parameters: @Group and @Attribute. You'll need to make @Attribute not visible and create a dataset to populate it. That dataset would be something like this:

SELECT Attribute
FROM Attributes
WHERE Group IN (@Group)

And create another dataset for your report data:

SELECT <all your data>
FROM YourTable
WHERE Attribute IN (@Attribute)

answered Apr 2, 2013 at 19:18

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1873604 Over a year ago

I am doing this, and it seems to be passing the correct values to the parameter, but the report is still looking only at the first value from the list.

Collectives™ on Stack Overflow

Using a list parameter in SSRS

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related