How to Select a Connection Profile for Dynamic Queries in VDM

Dynamic Queries are used to populate parameter values by running a SQL statement. A connection marker can be added to a Dynamic Query to specify which configured Connection Profile should execute the query. This is useful when parameter values need to be retrieved from a specific data source.

Creating a Dynamic Query Parameter

1. Open or create a View in VDM.
2. Locate the Query Builder section.
3. Select Parameters.
4. Click Add Parameter.
5. Select the new parameter under View Parameter Properties.
6. Under Behavior, set Dynamic to True.
7. Select the Dynamic Query property and enter the SQL statement.
8. Configure the remaining parameter properties, including the name, data type, and parameter type.

Specifying a Connection Profile

To specify which connection should execute the Dynamic Query, add a connection marker at the beginning of the statement.

Use the following format:

{ConnectionProfile: ConnectionName}

Replace ConnectionName with the exact name of the Connection Profile configured in VDM.

The connection marker must:

• Appear at the beginning of the Dynamic Query.
• Be enclosed in braces { }.
• Include ConnectionProfile: followed by the Connection Profile name.
• Reference an available Connection Profile using its exact name.

Dynamic Query Example

The following example uses the DEF Connection Profile to retrieve client numbers and client names:

{ConnectionProfile: DEF}Select CLIENT_NUM, CLIENT_NAME
From SQLUser_Clnt_General

After entering the connection marker and SQL statement, click OK to save the Dynamic Query.

Running the Dynamic Query

When the View is run, the Dynamic Query uses the Connection Profile identified by the connection marker. The values returned by the query are displayed on the parameter prompt.

1. Run the View.
2. Review the values returned for the dynamic query.
3. Select the required value or values.
4. Click Finish to continue running the View.

In this example, the Dynamic Query uses the DEF Connection Profile and returns the available client numbers and client names.

Important Information

The connection marker specifies the connection used to execute the parameter’s Dynamic Query. It does not change the primary connection used by the View.

The specified Connection Profile must be available in the environment where the View is run. If the View is published to WebReports, the corresponding Connection Profile must also be configured and available in WebReports.