SQL Server Reporting Services - Optional Parameters
Right now, for our most quick-and-dirty reporting needs from Relational and OLAP databases, we are toying with the idea of using SQL Server 2005 Reporting Services to generate our web reports. Using this server means we get ubiquitous access to our reports in an easy-to-use, consistent manner.
A major hurdle to using Reporting Services is that by default, Parameters cannot be flagged as 'optional'. After much delay, I found there is a way to 'Hack' a report to have an optional Parameter.
First, place your parameter in the Query as such:
SELECT * FROM Table
(AssetID = @AssetID OR @AssetID IS NULL)
(Coincidentially, this is the same method I used to make parameters optional in .NET DataSets for web applications).
Second, in the dataset to populate the Parameters, make sure at least one row returns NULL. You'll need this to have the null row appear in the dropdown that the parameter creates. It might look like this:
SELECT DISTINCT AssetID, AssetName FROM Table
SELECT NULL, 'All'
ORDER BY 1,2
Third, configure the parameter as such; with the default value as null, to allow you to select 'All'
Hope this helps someone!