Creating query parameters

  1. Click the Query Parameters option in the widget menu. The Parameters grid opens in a dialog.
    query_parameters
  2. Click Add Parameter to create new parameter. Click Delete Parameter to delete rows of existing parameters.
    query_parameters2
  3. Specify the Parameter Name.
  4. Select one of these options for the parameter type:
    • String: Accepts any combination of letters, numbers, special characters.
    • Number: Only accepts digits and decimal values.
    • Running Date: Only accepts integers which are used to calculate a relative date based on the current date.
    • Dynamic Field: Provide any of the below mentioned dynamic fields in upper case as the parameter value for the dynamic field.
    Note: The Dynamic Field parameter is applicable from Optiva version 12.14.61.00 onward.
    Dynamic Fields Description
    CURRENT_USER_CODE Uses the Optiva user code for the current user.
    CURRENT_GROUP_CODE Uses the Optiva group code for the current user.
    CURRENT_LAB_CODE Uses the Optiva Lab code for the current user.
    Below are some example queries for Dynamic Field:
    Query Lookup Labels and Query Parameters
    SELECT FORMULA_CODE, VERSION, OWNER_CODE FROM FSFORMULA WHERE OWNER_CODE = [%1]

    Lookup Labels: Formula;Version;Owner

    Parameter Type: Dynamic Field

    Parameter Value: CURRENT_USER_CODE

    This query retrieves the data using the Optiva user code for the current user.
    SELECT FORMULA_CODE, VERSION, OWNER_CODE FROM FSFORMULA WHERE GROUP_CODE = [%1]

    Lookup Labels: Formula;Version;Owner

    Parameter Type: Dynamic Field

    Parameter Value: CURRENT_GROUP_CODE

    This query retrieves the data using the Optiva Group code for the current user.
    SELECT LAB_CODE, PARAM_CODE FROM FSLABTECHPARAM WHERE LAB_CODE = [%1]

    Lookup Labels: Lab Code;Param Code

    Parameter Type: Dynamic Field

    Parameter Value: CURRENT_LAB_CODE

    This query retrieves the data using the Optiva Lab code for the current user.
  5. Data sent to Query = Current date + Parameter type Running Date value.
    If Parameter type Running Date value is negative:
    Example: If a user enters the running date as -5,
    Current date : 2019-02-08 10:14:22.000
    Running date : -5
    Value sent to query : 2019-02-03 10:14:22.000
  6. If the Parameter type Running Date value is positive:
    Example : If a user enters the running date as +20 or 20,
    Current date : 2019-02-08 10:14:22.000
    Running date : +20
    Value sent to query : 2019-02-28 10:14:22.000
  7. Define the values to be filtered by assigning tokens in the SQL Where clause.
    Query Lookup Label and Parameters
    SELECT PROJECTMGR, STATUS_IND, PROJECT_CODE, DESCRIPTION, PROJ_CUSTOMER, PROJ_TYPE, PORTFOLIO, PROJECT_CODE AS DR_PROJECT_PROJECT_CODE FROM FSPROJECT WHERE PROJECTMGR =[%1] AND STATUS_IND =[%2] AND LOGICAL_DELETE=0 ORDER BY PROJECTMGR

    Lookup Label: Project Manager;Status;Project code;Description;Project Customer;Project Type;Portfolio;DR_PROJECT_Project code

    Parameters:
    • Parameter Type: String
    • Parameter Value: JOHNNY
    • Parameter Type: Number
    • Parameter Value: 200
  8. Specify the Query and Parameter values in the Query form for testing.
    query_parameters_step8
  9. The test results are displayed in the Query Results tab.
    query_parameters_step9
  10. Set the widget Query Parameters to align the query with the Where [%1] tokens.
    query_parameters_step10
  11. This is the finished result of the query is displayed in the widget as below:
    query_parameters_step11