CODEWITHSUNDEEP
sqlvisual-studio-2008variablesreporting-services
Bradley Oesch
Bradley Oesch

Reputation: 763

For SSRS in Visual Studio 2008, how can I make a variable accept multiple values as well as a single value?

I'm making a report with Visual Studio 2008, pulling the info from a table in a database. Let's say I just want to show the Type of employee (which is represented by an int, say 1-10) and their Name. Then my dataset would be this query:

SELECT Type, Name
FROM Employees
WHERE Type = @Type

This is SSRS, so I do not need to declare or set the variable (correct me if I'm wrong). When I run the report, it will give me an option to type in an int for the Type and it will create the corresponding report. My question is how can I set it so that I can type 1,2,3 in for the Type so that I get a report with those types of employees? So having the int variable be able to accept a list of ints or just one int. Essentially the "resulting query" from that example would look like this:

SELECT Type, Name
FROM Employees
WHERE Type = 1 AND Type = 2 AND Type = 3

Upvotes: 2

Views: 1233

Answers (1)

Bradley Oesch
Bradley Oesch

Reputation: 763

On the left side in the Report Data window under the Parameters folder, right click the variable, hit Parameter Properties, then check 'Allow Multiple Values' and select the correct datatype in the dropdown. I'm not sure why it decides to make a dropdown when you run the report, and you have to enter the values each on their own line instead of separated by commas, but it seems to work fine. Then just change the WHERE clause in your dataset from WHERE Type = @Type to WHERE Type IN (@Type). I don't know if the parentheses are necessary.

Also if you create a separate dataset that will present certain values you can have those show up instead of having to type them out. For example, create a dataset that contains this query

SELECT DISTINCT Type
FROM Employees
ORDER BY Type

This will create a list of distinct values for type that you can check/uncheck. You can make it more complex obviously as well.

Upvotes: 2

Related Questions