Trouble viewing images? Click on them to enlarge.
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.
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. Click here.
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: