[SOLVED] How return all records with blank multi-value parameter SSRS

How return all records with blank multi-value parameter SSRS

I have a report with a multi-value parameter (Name: @ParaID) that is blank by default and has no available values. The user can enter one or more values to filter the returned data. That works fine with the query set up like:

Select * 
from table1
where table1.ID in (@ParaID)

Now I wanted to set up the query to allow the user to decide if he wants to enter a value (or more) or not. If he leaves the parameter empty the report should return all records. But I cannot figure out how to do that. I also tried to use a default value (here: %). But I will not get any records returned or the report does not run in the first place. I set up the parameter to allow blank values.

What I tried:

Replace the empty parameter with a wildcard '%':

Select * 
from table1
where table1.ID in (@ParaID) ---> translates to table1.ID in (%)

I thought about using CASE but from my understanding this does not work beacause it is only possible to change the output of something but it is not possible to use it to change the where-clause. I thought about something like this:
```
Select * 
from table1
where
case @ParaID
when '%' then table1.ID like '%'
else table1.ID in (@ParaID)
end
```

I tried to replace the empty value with an select satement using expressions:

 =IIF(Parameters!ParaID.Count < 1, "Select* from table1", Parameters!ParaID.Value)

so the query would translate to:

    Select * 
    from table1
    where table1.ID in (Select* from table1)

Which from my understanding should return all records aswell. But it did not work.

Since none of my ideas worked I am curious to see if anyone has a working solution for that. Just in case it is important: I want to add a second multi-value parameter that works the same once I figured out how to solve this problem.

Solution

Like Larnu suggested, creating a stored procedure was the way to go.

I followed this tutorial and adapted it to fit my scenario: SSRS multi-value parameters with less fail

For reference: My stored procedure looks something like this:

ALTER proc [dbo].[MyProcedure]
    @ParaID varchar(255),
as
if (exists(select * from dbo.MVSplit(@ParaID,',') where Item = '%'))
set @ParaID = null
SELECT        *
FROM table1
where 
@ParaID is NULL or table1.ID in (select Item from dbo.MVSplit(@ParaID,','))