I'm using SQL Server Reporting Services to make a few reports. I have a few reports where filters are optional.
For example .. Imagine you have a report of books. The end user has the option to specify (or filter by) none, one, or many authors.
I do not believe I can put an IF { } statement into my query?
If there a suggest or best way to do this in SQL Server Reporting Services? thanks!
You can do this anywhere you want a condition..
WHERE ((:param0 IS NULL) OR (column_name0 = :value0))
AND ((:value1 IS NULL) OR (column_name1 = :value1))
AND...
Pass Null into the parameter if you don't want to use it.
In SSRS, you'll need to set the paremeters as NULL by default in SSRS... see here for more detail
NULL. Can also use a 'magic' default value other than NULL ;)
Its hard to tell without seeing the whole procedure that you've written, but you can have multiple values in your WHERE clause by using the statement IN,
eg:
SELECT
*
FROM
BOOKS
WHERE
AUTHOR IN ('AUTHOR1', 'AUTHOR2', ...)
You can provide everything in the author list in a single (type eg VARCHAR(MAX), TEXT) parameter if you like by using a set delimiter and then using a split function in your WHERE clause. Obviously if this parameter is empty or NULL the procedure would return all books.
There may be other approaches but this one works for me.
One solution is to push the requested authors into a staging table and query from that.
Select ...
From books
Where Exists (
Select 1
From author_staging As S
Where S.author = books.author
)
If the desired behavior was to return all books if no authors were selected, then you could do:
Select ...
From books
Where Exists (
Select 1
From author_staging As S
Where S.author = books.author
Union All
Select 1
From ( Select 1 As Value ) As Z
Where Not Exists (
Select 1
From author_staging As S1
)
)
Or another form:
Select ...
From books
Where Exists (
Select 1
From author_staging As S
Where S.author = books.author
)
Or Not Exists (
Select 1
From author_staging As S1
)
