Creating Parameters with Dynamic Queries in VDM

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

Learn how to create a Dynamic Query Parameter in VDM to automatically populate a parameter’s value list using a SQL query instead of manually entered values.

What Is a Dynamic Query?

A Dynamic Query is a SQL statement assigned to a parameter that dynamically retrieves values from the database at runtime. This removes the need to maintain long or frequently changing lists manually.

Tip: Dynamic Queries are ideal for parameters where the available values change often—like EmployeeIDs, active users, or product codes.

Why Use Dynamic Query?

• Automatically pulls live values from the database

• Saves time and reduces manual entry

• Ensures parameter lists are always current

• Allows use of advanced SQL logic (e.g., filtering, sorting, distinct)

Use Case: Instead of manually entering a list of EmployeeIDs, you can populate them directly from a live query:
SELECT DISTINCT EmployeeID FROM Orders ORDER BY EmployeeID DESC

Where Are Dynamic Queries Used?

Dynamic Queries are configured inside the Parameters section of a VDM View. Each parameter can be set to use either a static list or a SQL-powered Dynamic Query.

Video Tutorial:

Need help writing SQL?
Click here for a more in-depth SQL query guide.

Steps to Set Up a Dynamic Query Parameter

Step 1: Open or Create a View

• Launch VDM

• Open an existing View or create a new one

Step 2: Access the Parameters

• Click the Parameters button

• This opens the parameter management screen

Step 3: Add a New Parameter

• Click Add Parameter

• Give your parameter a name (e.g., SelectEmployee, FilterDate)

Step 4: Enable Dynamic Query

• Set the Dynamic field to True

Step 5: Enter Your SQL Query

In the Dynamic Query box, type the SQL you want to use to retrieve parameter values.

Example Basic Query:

SELECT DISTINCT EmployeeID FROM Orders ORDER BY EmployeeID DESC

 

 

Tip: Be sure your SQL syntax matches your database (SQL Server, Oracle, etc.).

Step 6: Set the Parameter Type

Choose a parameter type based on how the user will interact:

• IN / IN No Parenthesis – Best for multi-value selections

• Combo Box – Ideal for single-value dropdown menus

Step 7: Run the View and Test

• Click Run View

• Confirm the parameter shows a list of values retrieved by the SQL query

• Select values and verify correct filtering behavior

Article Summary

Dynamic Query parameters in VDM streamline filtering by using SQL to generate live, real-time value lists. Instead of manually updating parameter options, you can connect them to database queries—saving time and reducing maintenance. You can also sort, filter, and limit these lists by enhancing your SQL with DISTINCT or ORDER BY clauses.

Use Case: Your report filters by a changing list of customer names. With a Dynamic Query, the parameter is always updated with the latest customer list from the database.