Add cascade parameters for Reporting Service

Take the product category as an example:

1. Add a big data set, categoryvalues
Select distinct name as category from production. productcategory

2. Add a small dataset, subcategoryvalues
Select distinct psc. Name as subcategory
From production. productsubcategory as PSC
Inner join production. productcategory as PC
On PC. productcategoryid = psc. productcategoryid
Where PC. Name = (@ category)

3. Add the parameter @ category in "report data" and bind the available value of the parameter to the categoryvalues dataset. Both the value field and the label field are: category.

4. Add the same parameter @ subcategory and bind the available values of the parameter to the subcategoryvalues dataset. The Value Field and tag field are both subcategory. In this way, if you select a product category in the report viewer, the @ category parameter is automatically passed to the subcategoryvalues dataset query statement for running, and the result is automatically bound to the subcategory drop-down box.

5. Add a statistical detailed dataset, similar to the following statement:

Select
PC. Name as category,
PSC. Name as subcategory,
P. Name as product,
Soh. [orderdate],
Soh. salesordernumber,
SD. orderqty,
SD. linetotal
From [sales]. [salesperson] SP
Inner join [sales]. [salesorderheader] Soh
On sp. [salespersonid] = soh. [salespersonid]
Inner join sales. salesorderdetail SD
On SD. salesorderid = soh. salesorderid
Inner join production. product P
On SD. productid = P. productid
Inner join production. productsubcategory PSC
On P. productsubcategoryid = psc. productsubcategoryid
Inner join production. productcategory PC
On PC. productcategoryid = psc. productcategoryid
Where (PC. Name = (@ category)
And psc. Name = (@ subcategory)
And P. Name = (@ Product ))

6. In the design view, add a table and drag and drop the fields to be displayed.

7. On the report viewer toolbar, click"View Report"In this case, you can select the product category, automatically associate with the product category, and select the product category until all the parameters required for the statistical detailed dataset are obtained, click "OK" to view the statistics.