Using an IN statement for query in SSRS

Question

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?

In your final example, are A, B and C separate items in your parameter list? Is the parameter a multi-value parameter? — Alan Schofield, Dec 11 '18 at 13:36
A, B and C are separate items in the parameter list, but all values are to be used in the query when Label A is chosen — peterbonar, Dec 11 '18 at 14:18
Can you post screen shots of the report parameter screens? What happens if you change the where clause to WHERE Name = @name? On report parameter screen the 'General' section is there a tick against 'Allow multiple values' ? — SQLBobScot, Dec 11 '18 at 15:10
you will have to create a split function on sql side that will take "," seperated values. and your code will work. eg: would be something like... WHERE Name IN (select item from dbo.fn_split @name) . Clearly NOT going to write you the function. — junketsu, Dec 11 '18 at 15:51
@BobF if he allows multiple values they will come in as 'A,B,C' not 'A', 'B', 'C' ; so op's in clause wont work. — junketsu, Dec 11 '18 at 15:52
If the dataset query is not a stored proc @IN will work perfectly with multiple values. I do this all the time. — Alan Schofield, Dec 11 '18 at 16:10
ya I dont know which route @peterbonar is going. But Sp is the route we went for ssrs. Which he sadly has no way of getting away from because eventually all queries that hit ssrs 'have to be' converted into SP for fast executions. But if he can get away with direct query your answer will work great for him Alan S. — junketsu, Dec 11 '18 at 16:34

score 2 · Accepted Answer · answered Dec 11 '18 at 16:21

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')

works as op did not suggest SP or query route and per Alan's suggestion this will work direct query route. — junketsu, Dec 11 '18 at 16:35

Using an IN statement for query in SSRS

1 Answers1

Linked