CODEWITHSUNDEEP
sqlsql-serverreporting-servicessql-server-2016
user5021612
user5021612

Reputation:

How to select all parameter values in SSRS?

I have a parameters @Year and @Month in my report, where month has assigned values like January(label): 1(values), February: 2, ... My datased is feeded by stored procedure which has a filter enter image description here

WHERE (cal.CalendarYear = @Year) AND (cal.MonthId = @Month)

When I check 'Allow multiply values' in parameter settings, it returns an error

Error converting data type nvarchar into int.

How can I select all values (by default)?

Upvotes: 0

Views: 4946

Answers (3)

Alan Schofield
Alan Schofield

Reputation: 21683

If you don't need to use a Stored Proc you can easily solve the problem.

Copy the SQL from your stored proc (excluding the PROC definition, just the main code) and paste it in as your dataset query.

Change your = @Year and = @Month to IN(@Year) and IN(@Month)

That's all there is to it, no joining, splitting or anything else.

SSRS will inject the parameter values as comma separated values correctly for you.

Upvotes: 2

Pete Rennard-Cumming
Pete Rennard-Cumming

Reputation: 1618

Your error message about "nvarchar into int" suggests a data type mismatch between your SSRS parameter and your MonthId column. Check the Report Parameter Properties -> General -> Data type for the former and your table schema for the latter, and make sure they're either both text/varchar or both integers.

Allowing your query to handle multiple parameter values should be much simpler than needing to use joins and splits. Just use IN instead of =, and put your parameter name inside a set of brackets.

AND (cal.MonthId IN (@Month))

To set the defaults for your parameter, go to the Report Parameter Properties -> Default Values. Select the Specify values option, and add your numbers 1-12 as separate Value records.

Upvotes: 0

Jesse
Jesse

Reputation: 873

When you select multiple values, you pass the parameter to the procedure as an expression with Join().

enter image description here

Then in your procedure change your month parameter to be of type nvarchar(max).

Create a temp table and use a t-sql split string function (Google this, there are so many varieties but if you're stuck we can find one) to split the string into a table. Then inner join to your table in your procedure to filter on the selections.

Upvotes: 0

Related Questions