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.