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.



Others may find bits of it useful!








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: