Cascading Parameters

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16

     Cascading parameters provide a way of managing large amounts of report data. With cascading parameters, the list of values for one parameter depends on the value chosen in preceding parameter. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list.

Example:

• Open BIDS
• Open/Create required solution.
• Add a report with the name "CascadingParameters"

• Create 'Data Source and Dataset'. Insert a table and arrange the columns.

• Add two parameters "Gender and Dept_ID".

• Go to dataset properties and add the below query
• SELECT        ID, FirstName, LastName, Gender, Designation, ManagerID, Dept_ID, Salary, Commission, HireDate
  FROM            EMPLOYEE
  WHERE Gender IN (@Gender) AND Dept_ID IN (@Dept_ID)

•  We need to create tow datasets for the tow parameters. We will enable drop-down list for the parameters, the values will come from the datasets.
• Dataset1:
  Add the data set with the name "Gender"
  Query:
  SELECT DISTINCT GENDER FROM EMPLOYEE

• Datset2:
  Add the data set with the name "Dept_ID"
  Query:
  SELECT DISTINCT DEPT_ID FROM EMPLOYEE
  WHERE GENDER IN (@GENDER)

• Po to "Parameters" and map the 'Gender' parameter (Highlighted in the below screen)

• Click on "OK" button.
• Double click on "Gender" Parameter. Then Gender "Report Parameter Properties" window will open.

• Select "Allow Multiple Values" check box(Highlighted in the above screen)
• Click on "Available Values" section.

• Select "Get values from the query" check box. After that select required value for "Dataset(Gender), Value field (Gender) and Label field (Gender)" from the drop-down list.

• Go to "Dept_ID" parameter properties and select the "Allow Multiple Values" check box(Highlighted in the below screen)

• Click on "Available Values" section.
• Select "Get values from the query" check box. After that select required value for "Dataset (Dept_ID), Value field (Dept_ID) and Label field (Dept_ID)" from the drop-down list.

• Go to Preview section.
• First we need to select gender filter value then only Dept_ID filter will enable (based on Gender filter value Dept_ID drop down list will enable).
• Select the required filters and then click on "View Report" button.

• Report is generated.