Report Solution Patterns and Recipes - Professional SQL Server 2005 Reporting Services - page 322

Two separate queries are necessary to keep track of selected and unselected values. These two queries
are combined into one result set using the Transact-SQL UNION operator. The first of the two results
returns the value 1 for the selected column for all of the selected products. The second result returns the
value 0 for all remaining, unselected products. When these results are combined, the selected column is
used as an indicator flag to produce the checked boxes.
The data set query is processed as a Visual Basic string expression so the list of selected ProductIDs may
be concatenated into the expression. In the following script, note the reference to the ProductIDs report
parameter. This parameter is defined in the Report Parameters dialog as a simple string-type parameter.
The default value is set to =”” . This allows the report to render without an explicit parameter value.
=”SELECT ProductID, 1 AS Selected,” &
“ Production.ProductSubcategory.Name AS ProductSubCategoryName,” &
“ Production.Product.Name AS ProductName, Production.Product.ProductNumber,” &
“ Production.Product.StandardCost, Production.Product.ListPrice,” &
“ Production.Product.ProductSubcategoryID” &
“ FROM Production.Product INNER JOIN Production.ProductSubcategory” &
“ ON Production.Product.ProductSubcategoryID =” &
“ Production.ProductSubcategory.ProductSubcategoryID AND” &
“ Production.Product.ProductSubcategoryID =” &
“ Production.ProductSubcategory.ProductSubcategoryID AND” &
“ Product.ProductID IN (“ & Parameters!ProductIDs.Value & “)” &
“ WHERE Production.Product.ProductSubcategoryID =” &
Parameters!ProductSubCategoryID.Value.ToString() & “ “ &
“UNION “ &
“ SELECT ProductID, 0 AS Selected,” &
“ Production.ProductSubcategory.Name AS ProductSubCategoryName,” &
“ Production.Product.Name AS ProductName, Production.Product.ProductNumber,” &
“ Production.Product.StandardCost, Production.Product.ListPrice,” &
“ Production.Product.ProductSubcategoryID “ &
“ FROM Production.Product INNER JOIN Production.ProductSubcategory” &
“ ON Production.Product.ProductSubcategoryID =” &
“ Production.ProductSubcategory.ProductSubcategoryID AND” &
“ Production.Product.ProductSubcategoryID =” &
“ Production.ProductSubcategory.ProductSubcategoryID AND” &
“ Product.ProductID NOT IN (“ & Parameters!ProductIDs.Value & “)” &
“ WHERE Production.Product.ProductSubcategoryID =” &
Parameters!ProductSubCategoryID.Value.ToString() &
“ ORDER BY ProductName”
The report contains two embedded images, named checked and unchecked. These are simply screen
captures of the two check box states that could be taken from any Windows application. Figure 7-68
shows the table with an image in the first column of the detail row. The Value property expression
for the image item simply toggles between these two images based on the selected column Value on
each row.