D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.


My alternative to scraps of paper lying about.








Wednesday, 5 May 2010

Select All option - SSRS

It's often necessary to have a 'select all' option when using parameters in Reporting Services.
Here's a way to do it:

For my report I've created three datasets.

1) The first dataset, named 'Package' creates the parameter values, using UNION command to adjoin a '** SELECT ALL **' value to those obtained from a database.

SELECT '** SELECT ALL **' as PACKAGE
UNION
SELECT PACKAGE.PACKAGE as PACKAGE
FROM PACKAGE
order by PACKAGE.PACKAGE

2) The second dataset, named 'SelectAll' is used to populate the Default Value for the parameter.

SELECT '** SELECT ALL **' as PACKAGE

These two datasets are then used to populate the Report Parameters window as below:















3) The third parameter, in this case named 'ChangedRequest', provides the select logic.

SELECT .....
FROM .....
WHERE PACKAGE.PACKAGE = @Package or '** SELECT ALL **' in (@Package)
order by PACKAGE.PACKAGE


Which results in something like this:




No comments: