Designing Data Access - Professional SQL Server 2005 Reporting Services - page 152

Production.ProductSubCategory.ProductCategoryID,
Production.Product.ProductSubCategoryID
FROM Production.Product INNER JOIN Production.ProductSubCategory
ON Production.Product.ProductSubCategoryID
= Production.ProductSubCategory.ProductSubCategoryID
INNER JOIN ProductCategory
ON Production.ProductSubCategory.ProductCategoryID
= Production.ProductCategory.ProductCategoryID
WHERE Production.ProductSubCategory.ProductCategoryID = @CategoryID
ORDER BY Production.Product.Name
Note that SQL Server 2005 queries now include a schema prefix for each object reference ( Production in
this query) similar to the owner prefix for SQL Server 2000. If you use the Graphical Query Designer and
type or paste SQL without the schema prefixes, the query designer will try to add the schema prefix to
every table reference.
You're going to create two more data sets to populate parameter drop-down lists and set up a cascading
relationship between the two parameters.
Using the same data source, add another data set and name it CategoryList. Type this text into the third
pane of the Query Builder:
SELECT ProductCategoryID, Name
FROM Production.ProductCategory
ORDER BY Name
And add one more data set, named SubCategoryList, using this text:
SELECT ProductSubCategoryID, Name, ProductCategoryID
FROM Production.ProductSubCategory
WHERE ProductCategoryID = @CategoryID
ORDER BY Name
With the report designer Layout tab selected, select the Report item from the properties window drop-
down list and find the ReportParameters property. Click the ellipsis button next to this property. This
will open the Report Parameters dialog. Note that the CategoryID parameter has been added to the
report parameters, as expected. Click the Add button to add a new parameter and name it SubCategoryID.
Leave all of the other settings at default values to keep things simple. Click OK to close the Report
Parameters dialog.
Now switch back to the Data tab, select the first of these three data sets from the Dataset drop-down list
and click the ellipsis next to the data set name. On the Dataset dialog, switch to the Filters tab. There are
three required elements for a filter expression — the Expression (what you want to filter), Operator (how
you're going to compare a value), and Value (the source of the filter value). For the expression, drop
down the list and select =Fields!SubCategoryName.Value .
Leave the equality operator set to = and then drop down the Value list and select Expression. This opens
the expression builder. Use the controls to select the SubCategoryID parameter and use the Insert button
to move it into the expression box on the right side of this dialog. The resulting expression should be
=Parameters!SubCategoryName.Value .