Dynamic Query

Cory Fifield -

Trouble viewing images? Click on them to enlarge.

 

 

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. 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.

 

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

mceclip0.png

 

3. Add A Parameter

mceclip1.png

 

4. Set Dynamic to True

mceclip2.png

 

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

mceclip3.png

 

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.

mceclip0.png

 

Example IN Type Run Time:

mceclip2.png

 

Example Combo Box Run Time:

mceclip3.png

 

 

 

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:

mceclip6.png

 

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:

mceclip7.png

 

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:

mceclip8.png

 

 

 

Have more questions? Submit a request

Comments