SSRS Parameters. Allowing "All" or "Null"

Question

SSRS parameters are a pain. I want to be able to re-use reports for many different needs by allowing the users access to many different parameters and making them optional.

So, if I start out with code such as:

Select * from mytable myt
where myt.date between '1/1/2010' and '12/31/2010'
and year(myt.date) = '2010'
and myt.partnumber = 'XYZ-123' 

I want those parameters to be optional so my first attempts were to make the parameters default to null such as:

and (myt.partnumber = (@PartNumber) or (@PartNumber) is null)

That has problems because if the database fields in question are nullable then you will drop records because null does not equal null.

I then used code such as this:

DECLARE @BeginDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @PartNumber AS VARCHAR(25)
SET @Year = '..All'
SET @BeginDate = '1/1/2005'
SET @EndDate = '12/31/2010'
SET @PartNumber = '..All'
SET @Year = '..All'

Select * from mytable myt
where (myt.date between (@BeginDate) and (@EndDate))
and (year(myt.date) =  (@Year) or (@Year) = '..All' )
and (myt.partnumber = (@PartNumber) or (@PartNumber) = '..All')

That doesn't work because Year(myt.date) is an integer and @Year is not.

So, here are my questions.

1. How can I make my dates optional? Is the best way to simply default them to dates outside of a practical range so I return all values?
2. What is the best way to handle the null or '..All' options to make my queries as readable as possible and allow my users to have optional parameters for most data types? I'd rather not use null for

Answer 1

I like your third code block. It seems like your WHERE clause could be corrected to work with a non-int value. The AND clause for the year line would look like this--not my best T-SQL, but it should get you pointed in the right direction:

and 1 = CASE @Year WHEN '..All' THEN 1 ELSE CASE WHEN year ( myt.date )  = CONVERT ( int, @Year ) THEN 1 ELSE 0 END END

This will allow you to have a string value of '..All' or an int value. Either will match correctly. You can do the same with partnumber.

Answer 2

Go ahead and allow nulls, which indicates the filter should not be applied. Then, you can use the following:

SELECT *
FROM mytable myt
WHERE COALESCE(myt.date, '1/1/1900') between COALESCE(@BeginDate, myt.date, '1/1/1900') and COALESCE(@EndDate, myt.date, '1/1/1900')
    AND COALESCE(YEAR(myt.date), -1) = COALESCE(@Year, YEAR(myt.date), -1)
    AND COALESCE(myt.partnumber, -1) = COALESCE(@PartNumber, myt.partnumber, -1)

In summary, if any variable value is NULL, then compare the column value to itself, which effectively ignores the condition. More specifically, when testing myt.date, if @BeginDate is NULL then set the lower range value equal to the myt.date value. Do the same substitution with the @EndDate value. Even, if both @BeginDate and @EndDate are NULL, the condition will be true.

A similar approach is used for YEAR(myt.date) and myt.partnumber. If the variable value is NULL, then compare the column value to itself, which is always true.

UPDATE: Added a default value to each COALESCE to handle the situation where the column value is NULL.

Answer 3

try it like this, the key is to fix your null parameters values to surrogate nulls, also since sql server supports short circuit evaluation, putting the null check should generally perform better.

Select * from mytable myt
where (myt.date between (@BeginDate) and (@EndDate))
and (@Year IS NULL OR COALESCE(myt.date,'1900') = @Year)
and (@PartNumber IS NULL OR ISNULL(myt.partnumber, '<NULL>') = (@PartNumber)