Creating Cascading Parameters in VDM Using Dynamic Queries

Trouble Viewing Images? Right-click on any image and select "Open in new tab" to view a larger version. You can also zoom in using Ctrl + Mouse Wheel for easier readability.

Article Goal

Explain how to create cascading parameters in VDM using dynamic queries, allowing the value of one parameter to determine the available options in another.

What Are Cascading Parameters?

Cascading parameters are dependent parameters where the values in one parameter (e.g., Orders) are filtered based on the selected value(s) in another (e.g., EmployeeID). This approach improves data precision and user experience by narrowing choices based on previous selections.

Tip: Cascading parameters are especially useful in multi-level relationships such as Employee → Order → Product, where each level depends on the previous one.

Why Use Cascading Parameters?

• Streamlines data selection by filtering dependent values

• Prevents irrelevant values from being displayed

• Makes parameters more intuitive and dynamic

• Helps users avoid incorrect combinations

Use Case: Selecting EmployeeID = 1 will limit the OrderID options to only orders related to Employee 1.

Download Sample Views

• Download Version 1 – Uses EmployeeID = 1

• Download Version 2 – Uses EmployeeID = 2

Steps to Create Cascading Parameters

Note: If you're not familiar with dynamic queries or WHERE clauses, review the Dynamic Query and WHERE Clause in a Dynamic Query article first.

Step 1: Open or Create a View

• Launch VDM and open or create a View

• Run the View to initialize data access

Step 2: Add Parameters

• Open the Parameters panel

• Add at least two parameters:

  • EmployeeID – Primary (parent) parameter

  • Orders – Dependent (child) parameter

Step 3: Create a Dynamic Query for the First Parameter

1. In the first parameter (EmployeeID), set Dynamic to True

2. Enter a basic dynamic SQL query: SELECT DISTINCT EmployeeID FROM Orders

3. Run the View to verify values are returned.

Step 4: Create a Dynamic Query for the Second Parameter

1. Initially set the second parameter (Orders) with a general dynamic query: SELECT DISTINCT OrderID FROM Orders

2. Confirm that it returns a list of all available OrderID.

Step 5: Add a WHERE Clause to Cascade the Second Parameter

Modify the second parameter’s dynamic query to reference the first parameter:

• This ensures the values in Orders are filtered based on what’s selected in EmployeeID.

Tip: Use IN if the first parameter supports multi-select.
Make sure the first parameter is defined before the second in parameter order.

Step 6: Adjust Prompt Page Settings

1. Set the Number of Prompt Pages to 2

2. Assign:

EmployeeID to Page 1

Orders to Page 2

Tip: This ensures the second parameter loads after the first is selected.

Step 7: Run and Test the View

• Run the View

• Select a value in EmployeeID

• Verify that only the related Order IDs appear in the second prompt

• Try switching between different EmployeeID and confirm the behavior updates accordingly

Article Summary

Cascading parameters in VDM allow one parameter to filter another by using SQL WHERE clauses in dynamic queries. This enhances interactivity, reduces clutter in dropdowns, and improves user accuracy. With proper prompt page setup, cascading logic becomes seamless and user-friendly.

Use Case: A user selects a customer (CustomerID), and only orders (OrderID) related to that customer are shown in the next parameter, saving time and preventing irrelevant choices.