Wrote a report recently where I needed an ‘ALL’ selection included for a report parameter with a standard list of items from a query. This technique comes in handy for a lot of different applications.
Using a list of collections as an example, where we want to display the Collection Name to the user, and need to use the Collection ID as the Value.
-- SQL:
SELECT '%' As SiteID, '<All>' as CollectionName
UNION
SELECT [SiteID], [CollectionName]
FROM [vCollections]
ORDER BY CollectionName
Results:
SiteID CollectionName
-------- ----------------------------------
% <All>
UMN004C2 00CollectionTest
UMN004C3 00CollectionTest
UMN005F4 Adobe Acrobat Professional 9.2 TS
UMN005FA Adobe Illustrator 14.0.0 TS
UMN005F8 Adobe InDesign 6.0.3 TS
UMN005E9 Adobe Photoshop 11.0.1 TS
SMS00005 All Active Directory Security Groups
SMSDM003 All Desktops and Servers
SMS00001 All Systems
<snip>
In the next blog entry, I’ll demonstrate how to use these results in a report parameter.