Cascading Parameters

Trouble viewing images? Click on images to enlarge.

In this article we cover the setting up cascading parameters. If you would like to download the sample View used in this demonstration, scroll down to the bottom of the page where there will be a download link available, or click here. (Download Version 1 / Download Version 2) 

Note: Version 1 is ran using EmployeeID 1 and Version 2 is ran using EmployeeID 2

Why Use Cascading Parameters?

The use of cascading parameters lets the user further define the selection criteria of their parameters. This gives the user the chance to filter parameter selection..

Example: I have Employee ID 1 and 2. Each employee has order IDs associated with them. If EmployeeID 1 is the only selected employee ID, the next parameter selection for selecting Order IDs will only display Order IDs associated with employee 1.

How Do Cascading Parameters Work?

When values are selected for the first parameter, those values are then used to populate the values available in the next parameter using a WHERE clause inside the dynamic query that references the previous parameter.

How To Create Cascading Parameters

Note: If you are unsure how to use a WHERE clause we recommend reading our WHERE Clause In A Dynamic Query article first.

1. Open/Create your View, then go into the Parameters.

2. Add at least two parameters.

3. Create Dynamic Query for first parameter and verify data is populated into the parameter.

Example: SELECT DISTINCT EmployeeID FROM Orders

4. Create Dynamic Query for second parameter and verify data is populated into the parameter.

Example: SELECT DISTINCT OrderID FROM Orders

5. Modify the Dynamic Query for the second parameter by adding a WHERE statement that references the first parameter. (See Example below) Set the Number of Parameter Pages to 2 and set the second parameter to Prompt Page 2.

Example: SELECT DISTINCT OrderID FROM Orders WHERE EmployeeID IN ?EmployeeID?

The image on the left are the results of the second parameter when selecting EmployeeID 1

The image on the right are the results of the second parameter when selecting EmployeeID 2

6. Repeat the steps for any additional parameters. Then run the View to test the results.