Adding custom query parameters

In addition to the query parameters provided by application, you can also add custom query parameters to the CUSTOMQUERYPARAMETERS table in the database.

To add a custom parameter:

  1. Access your Microsoft SQL Server or Oracle database management tool.
  2. View the CUSTOMQUERYPARAMETERS table.
  3. Add a row to the table.
  4. Specify a unique ID value in the CUSTOMQUERYPARAMETERID column.
  5. Specify a descriptive but short name in the NAME column. This is the name you see in the Value Is list when adding or modifying a conditional statement in Query Builder.
    Note:  The name can contain letters, but cannot contain numbers, spaces or symbols. Do not include a leading colon.
  6. Specify the SQL query statement in the PARAMETERVALUE field.
  7. Specify the SeccodeID SYST00000001 in the SECCODEID column, to make the query parameter available to everyone, or specify the SeccodeID for a single user.
  8. Specify the field type this parameter applies to in the DATATYPE column. The supported data types are: DateTime, String, and Integer.

    The query parameter displays in the Value Is list for the specified data type and supported operators.

  9. Specify T for True or F for False in the ISSCALAR column.
    • T: The query parameter is available for the operators
      • = equal to
      • <> not equal to
      • > greater than
      • < less than
      • <= less than or equal to
      • >= greater than or equal to
    • F: The query parameter is only available for the operator In.
  10. Sepcify one of these in the DATABASETYPE column:
    • MSSQL: Type MSSQL if the Parameter Value is specific to MSSQL.
    • Oracle: Type Oracle if the Parameter Value is specific to Oracle.
    • Both: Type Both if the Parameter Value is valid for MSSQL and Oracle.
  11. Save your changes.

    Once saved, the custom parameter is available in the Query Builder, Conditions, Value Is list for the data type specified in steps 8 and 9.