Tag Archives: Parameters

Select All in Reporting Services

A common request when having a parameter as a dropdown in SSRS, is a “Select all” entry at the top of the list. If you you use a multi-value parameter in SQL Server 2005 SP0 or SP2 (Not available in SP1), the top checkbox will have this option. But if you don’t want to make the parameter multi-value, or you’re using SSRS 2005 SP1 or SSRS 2000, you have to do it like this:

Parameter dataset:

SELECT * FROM 
( 
SELECT Vendor.VendorID,Vendor.VendorName FROM Vendor 
UNION ALL 
SELECT -999 AS VendorID, 'All Vendors' AS VendorName 
) AS Tbl1 
ORDER BY CASE WHEN VendorID=-999 THEN ' ' ELSE VendorName END

Notice the order by clause. This makes sure that “All Vendors” appear at the top of the list.

For the main dataset:

SELECT * FROM Sales WHERE Vendor=@VendorParam OR @VendorParam=-999