Friday, 12 February 2010

Adding an 'All' option to a SSRS parameter dropdown

Adding 'All' to a query to use in a parameter list -
SELECT * FROM (SELECT NULL AS DepartmentID, '' as Name) AllDepartments
UNION
SELECT DepartmentID, Name FROM HumanResources.Department ORDER BY Name

Use OR in the WHERE clause as follows to use te passed parameter -
ELECT DepartmentID, Name FROM HumanResources.Department 
WHERE (DepartmentID IS NULL) 
OR (DepartmentID = @DepartmentID)

No comments: