sql2008 set subcategory report parameter available values and default value steps to share _mssql2008

1. In the Parameters folder of the Report Data pane, right-click Subcategory, and then click Parameter Properties.

2. Click Available values.

3. Click Get value from query.

4. In the DataSet Drop-down list, click Subcategoryvalues.

5. In the Value field, click Subcategory.

6. In the Label field, click Subcategory.

7. Click "Default Value".

8. Click Get value from query.

9. In the DataSet Drop-down list, click Subcategoryvalues.

10. In the Value field, click Subcategory.

11. Click OK.
Next, you create @Product parameters that depend on the @Category value and @Subcategory value.
Add a value DataSet for report parameter Product
1. In the Report Data pane, right-click Adventureworks_ref, and then click Add DataSet.
2. In name, type Productvalues.
3. Paste the following query text into the query pane:

Copy Code code as follows:

SELECT DISTINCT P.name as Product
From Production.Product P
INNER JOIN Production.productsubcategory as PSC
On P.productsubcategoryid = PSC. Productsubcategoryid
INNER JOIN Production.productcategory as PC
On PC. ProductCategoryID = PSC. ProductCategoryID
WHERE (PC. Name = (@Category)
and PSC. Name = (@Subcategory))

4. Click OK.
A productvalues dataset containing one Product field is added to the report Data pane.
Next, set the properties of the @Product parameter and use the values in the query above as their available values and default values.