Passing Default Value into SSRS Query

Question

In SSRS I have a report parameter that is a multi-select and has a default label of (None) and default value of 0.

In my query I want to set it up so that I can pass in 0 or some other value(s). However, if I pass in 0, I don't want it to filter down the data.

Normally you would add the below clause to your where statement, but I'm not sure if or how this is done with multiple values.

(@Parameter = 0 OR table.column = @Parameter)

Answer 1

Don't know if I understand the question, you're going to pass a LIST OF POSIBLE VALUES or 0, right?...

If so, maybe the only change you have to do is set your parameter to string ('0' or '1,2,3') and do a condition like this

(@Parameter = '0' OR table.column IN (@Parameter))

Answer 2

Are you calling a procedure or running a direct query from SSRS?

If you're calling a proc you could try determining in the proc whether any filtering needs to take place, something like

DECLARE @filter INT
SELECT @filter = LEN(@parameter)
-- or something similar to determine if there are any parameters

SELECT
   -- everything you want
WHERE
    @filter = 0 OR table.Column IN (@parameter)

Answer 3

You don't have to pass the parameters exactly as the user fills them in onto the queries.

On your dataset you can have 2 parameters: @Parameters and @ApplyFilter

and then on the Parameters tab of the dataset (Parameter Name = Parameter Value):

@Parameters = @Parameters
@ApplyFilter = IIF(And(Parameters!Parameters.Value.Length = 1,Parameters!Parameters.Value(0)=0),"0","1")

Finally your SQL Filter would need to be something like:

WHERE
   (@ApplyFilter = 1 AND tbl.column IN (@Parameters))

The reason why I prefer this solution is because you make the query more readable!