Search code examples
reporting-servicesssrs-2008

Getting multiple integer values in an SSRS parameter

How do I get multiple integers into the value field of an SSRS parameter so that I can use an IN statement in my WHERE clause?

Bear with me. I think this will end up as a simple syntax question but the explanation is overlong.

I have a report that I want to split between two types of staff Dedicated and Bureau. The only way to determine which is which is the hour that the activation occurs, with the dedicated staff working all hours except 12 and 13 (midday to 2pm).

I've created a parameter called @AgentType and added a where clause in my SQL query 

Where

[Hour] in (@AgentType)

I then set the data type of the parameter as an Integer and set the available values as in the picture. settings

This works perfectly as a test however if I try to model the working hours accurately

enter image description here

SSRS removes my commas and turns the interger into 1234567... Also I tried using an expresion =1,2,3... but that caused errors when the report ran.

So my question basically is how do I get multiple integers into the value field of an SSRS parameter so that I can use an IN statement in my WHERE clause?

Solution

  • This solution could work for you. You can create two parameters, AgentType and AgentTypeHidden.

    For AgentType set two available values as follows:

    enter image description here

    AgentTypeHidden must be hidden and set to allow multiple values:

    enter image description here

    Don't set any available values but default values using this expression:

    =IIF(
   Parameters!AgentType.Value=1,
   SPLIT("1,2,3,4,5,6,7,8,9,10,11,14",","),
   SPLIT("12,13",",")
)

    Also set Always Refresh for the AgentTypeHidden parameter.

    enter image description here

    Finally in your query use the AgenTypeHidden parameter.

    Where [Hour] in (@AgentTypeHidden)

    Let me know if this helps.