Editing Custom Variables

When creating advanced reports, you can specify a custom variable within the WHERE clause. For example, to create a search by contact, enter:
...
WHERE 
   contact = '$CONTACT_MATCH'; # $CONTACT_MATCH is the custom variable.
...
Once you declare the variable within the SQL script text, you can view and edit the variable on the Edit Advanced Report Template page.
  1. To edit a custom SQL variable used in an advanced report, go to Reports and click the Manage Report Types button. Find the advanced report and click edit. Click Edit Parameters.
  2. Find the custom variable you want to configure and click edit.
  3. Select the desired variable type from the Type drop-down menu.
    Variable Type Description
    string The value of this variable must be a string.
    integer The value of this variable must be an integer.
    date The value of this variable must be a valid date. Click the calendar icon to select a valid date.
    ip The value of this variable must be a valid IP Address.
  4. Optional: Allow the user running the report to leave the variable undefined by clearing Is field required?. Custom variables are required by default.
    If Is field required? is selected, a user running this report is required to enter a value for the custom variable to run the report.
    Note: If the custom variable is not required and it is used with the AND operator, then write the report query as follows:
    ...
    WHERE
    ...
       AND (
         t.status = '$FOO'
         OR '$FOO' = ''
       )
    ...
    Failure to include OR '$FOO' = '' results in an empty report because the data is filtered by t.status = '', which is always false.
  5. Optional: Define the variable name that is displayed when Console asks for the value of this variable.
    For example, if you want to search by contacts and included a custom variable named $CONTACT_MATCH in your SQL script, Console by default prompts the user running the report to enter a value for "Contact Match." If you enter "User Name" in the Label field, Console asks for a value called "User Name" instead and matches the result to $CONTACT_MATCH.
  6. Optional: Add a hint to remind the user the purpose of this variable.
    Continuing the previous example, if your custom variable, $CONTACT_MATCH, is used to search your database for contacts matching the value of this variable, a possible hint is: "Search by this CONTACT name."
    When running the report, the user is prompted with the following:

  7. When finished, click Update.