CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
peterbonar
peterbonar

Reputation: 599

Using an IN statement for query in SSRS

In an SSRS report I'm building, I've a query like the one below: 

SELECT ID, Name
FROM TableA
WHERE Name IN (@name)

And then within SSRS, I have the parameter available values set to 'Get values from a query' and the Value field will be something like below: 

'A','B','C'
'D','E','F'
'G','H','I'

And the Label field something like this: 

Label A
Label B
Label C

When I run the query 

SELECT ID, Name
FROM TableA,
WHERE Name IN ('A','B','C')

The correct results are returned in SQL Management Studio, however, the report returns nothing. How do I get the correctly formatted value into the IN statement?

Upvotes: 0

Views: 1348

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

Based on your example, I would approach it like this...

Create a table or view that contains your labels and values such as

myTable:
    myCaption   MyValue
    'Label A'   'A'
    'Label A'   'B'
    'Label A'   'C'
    'Label B'   'D'
    'Label B'   'E'
    'Label B'   'F'
    'Label C'   'G'
    'Label C'   'H'
    'Label C'   'I'

For your first parameter (@myLabel), the dataset would simply be 

SELECT DISTINCT myCaption FROM myTable ORDER BY myCaption

The your second parmameter (@myValues) (assuming you want the user to be able to choose from the values) would be 

SELECT myValue FROM myTable WHERE myLabel = @myLabel

You will need to make the parameter multi-value and set the available values to the 2nd dataset, optionally you can set the default values to the same dataset.

In your main report dataset the query would be like your example

SELECT * FROM myDataTable WHERE myColumn IN (@myValues)

As long as the above is directly in the dataset query (in other words, you are not using a stored procedure), then this will work as expected. SSRS will automatically convert your multi-value parameter into a comma separated list and inject it into your dataset query so the actual query sent to the server would be SELECT * FROM myDataTable WHERE myColumn IN ('A', 'B', 'C')

Upvotes: 2

Related Questions