Trouble seeing the images? Right click on images and open in new tab to enlarge or zoom in on the page (Ctrl + mousewheel).

In this article we will show you how to set up a dynamic query as well as show you examples at the end to get you started.

Why Use Dynamic Query?

Dynamic Query allows you to populate a parameter's value list using a SQL Query. Without using a Dynamic Query to populate the parameter values, you would have to do it manually. If you have a lot of values to use this will save you a ton of time.

For a more in-depth guide or examples on SQL queries. Click here.

Where Do You Use Dynamic Query?

Dynamic Query can be set on a parameter in the Parameters section of VDM.

Video Tutorial: 

Creating a Parameter with Dynamic Query

1. Open / Create A View

2. Click on Parameters

3. Add A Parameter

4. Set Dynamic to True

5. Enter your SQL Query.  Below is a basic SELECT statement that populates the Parameter using the ContactName from the Customers table.

6. Set the Type of the parameter to match your needs. Generally an IN type will be used if you are selecting multiple values, otherwise try a Combo Box type for single values.

Example IN Type Run Time:

Example Combo Box Run Time:

Dynamic Query Examples

This SQL Query will return every record for EmployeeID and populate the parameter

SELECT [Field] FROM [Table]
SELECT EmployeeID FROM Orders

Example Standard Query:

Using DISTINCT in the query will remove duplicate values from the results.

SELECT DISTINCT [Field] FROM [Table]
SELECT DISTINCT EmployeeID FROM Orders

Example Using DISTINCT:

If you want to sort the values in the parameter, you will need to use an ORDER BY clause.

SELECT DISTINCT [Field] FROM [Table] ORDER BY [Field] Desc
SELECT DISTINCT EmployeeID FROM Orders ORDER BY EmployeeID Desc

Example Using ORDER BY: