Parameters

Tony Coffman -

Trouble viewing images? Click on images to enlarge.

 

Parameter fields allow you to set a filter value as a variable. When running a View with a parameter you will be prompted for input.  This will allow users to create a single VDM View that allows for different filter criteria to be entered at run time (ie, City, State/Province, Last Name or First Name etc.) versus updating the View each time with new criteria or having to create and maintain multiple Views.  Parameters can also be setup for each View independently in the Auto Scheduler.  Using parameters can save a great deal of time and make View maintenance much easier by allowing one View to be ran multiple times with different criteria.

Adding A Parameter To A View

  • Open or create a new VDM view.
  • Click the Parameters tab, then click Add Parameter.
  • Repeat the process for adding additional parameters.
  • Save and run your view. When the View is executed a parameter pop up box will prompt you for the filter criteria.

 mceclip0.png

 

Adding a Parameter to an Expression

  • Edit or Create a new expression by right clicking in a cell on the Details Grid and select Add or Edit Expression.

mceclip1.png

 

  • Select a field from the Fields category (ie City, First Name, Last Name)
  • In the Expression Editor place a “?” in the desired Parameter location.
  • Click “OK” to update/create the expression.
  • Save and run your view. When the View is executed a parameter pop up box will prompt you for the Expression parameter.

 Expression Editor Parameter

Expression.png

 

Managing Parameters

To manage the parameters in a View, click on the Parameters tab. This is where you will Add, Update and Delete parameters.

 

Parameter Properties

mceclip4.png

Behavior

  • Dynamic: When enabled, the value list will be provided using a Query sent to the server.
  • Dynamic Query: The query that provides the values to be selected when using the IN/Combobox and Dynamic is enabled.
  • Enabled: Determines whether or not this parameter is prompted at runtime.  A disabled parameter can still be used, but the value will not be updated.
  • Parameter: The selected Parameter determines whether the current Parameter is enabled based on the selected Parameter’s value.
  • Parameter Value: The value that determines if the current Parameter should be enabled based on the selected Parameter’s value.
  • Prompt Page: The Prompt Page determines what page the parameter will be displayed on at runtime.
  • Required: Requires input to continue.  Unless there is input for this parameter, the view will not be searched.
  • Type: The type of the parameter determines the structure that is presented to the user during the parameter prompt.

 

Data

  • Datatype: The datatype of the parameter value(s).
  • Default Value: This value will always be the default when prompted.
  • Description: The description will be displayed during the parameter prompt in place of the parameter name.
  • Name: The name of the Parameter.
  • Object Identifier: The Object Identifier determines string/date values. The default is a single quote (‘).
  • Values List: The values list provides the values to be selected when using the IN/Combobox and Dynamic is disabled.
  • Variable Filter: When enabled, the value list will be the selection for a Variable Filter. An guide to Variable Filters can be found here.

 

Suppression

  • Allow Suppress: Allows the Parameter and its SQL statement to be suppressed on a Key value.
  • SQL Statement: SQL statement that is used when Allow Suppress is enabled.  This value is what will be placed in the query if Allow Suppress is True and the Parameter value does not equal the Suppress Value.
  • Suppress Value: Value that determines whether or not the Parameter’s SQL statement is suppressed.

 

 

Below are examples of the different Types of parameters. **Note** all examples are using the Northwinds sample database.

Example of Combobox Type for a string field

  • Set the Datatype value to String.
  • Enter a Description (optional: this changes display name at run time).
  • Set the Type of the parameter to Combobox.
  • Enter Anchorage, Barcelona, London, Berlin, Madrid, Paris, Boise, Brandenburg, San Francisco, Sao Paulo, Seattle into the Values List Collection.

Example of adding items to the Values List Collection

mceclip3.png

 

Finished example of Combobox Type properties

mceclip2.png

 

Combobox Run Time Prompt Example

mceclip6.png

 

Example of “IN” Type for a string field 

  • Set the Datatype value to String.
  • Enter a Description (optional: this changes display name at run time).
  • Set the Type of the parameter to “IN“.
  • Enter Anchorage, Barcelona, London, Berlin, Madrid, Paris, Boise, Brandenburg, San Francisco, Sao Paulo, Seattle into the Values List Collection.

 

Example of adding items to the Values List Collection

mceclip3.png

 

Finished example of “IN” Type properties

mceclip8.png

 

IN Run Time Prompt Example using Value List

mceclip12.png

 

Example of “IN” Type for a string field using Dynamic Query

  • Set the Datatype value to String.
  • Enter a Description (optional: this changes display name at run time).
  • Set Dynamic to True
  • Set Dynamic Query to SELECT DISTINCT Customers.City FROM Customers **Note**Two fields can be returned.  The first being the value, the second being a description.
  • Set the Type of the parameter to “IN“.

**NOTE**Parameters that are prompted on earlier Parameter pages can be used inside of the Dynamic Query

Finished example of “IN” Type properties

mceclip14.png

 

IN Run Time Prompt Example using Dynamic Query

mceclip13.png

 

Example of Date Time Picker Type for a Date field

  • Set the Datatype value to DateTime.
  • Enter a Description (optional: this changes display name at run time).
  • Set the Object Identifier to ( ' )
  • Set the Type of the parameter to Date Time Picker.

Note* This example uses the a ( ' ) symbol as the Object Identifier. Access would require a ( # ) around date fields.  Not all databases will use the same Object Identifier for strings and dates.

 

Example of finished Date Time Picker Type properties

mceclip15.png

 

Date Time Picker Run Time Prompt Example

mceclip16.png

 

Example of Value Type for a string field

  • Set the Datatype value to String.
  • Enter a Description (optional: this changes display name at run time).
  • Set the Type of the parameter to Value.

Finished Example of Value Type properties

mceclip17.png

 

Value Run Time Prompt Example

mceclip18.png

Have more questions? Submit a request

Comments