Set query filter criteria

To define the records to include in a query's results, you can add filters to the query and set their criteria in Builder. You can base a filter's criteria on:

• Static values, such as a specific account or date
• Parameters, to dynamically set values when the query runs
• Calculated values derived from SQL operations and functions

To define a filter's criteria, select its value on the Filters tab, and set its value from the Filter Properties panel.

Filter by a static value

To include records based on a static value, enter the value on the Filter Properties panel, and click Apply.

Filter by a dynamic parameter value

To dynamically set a filter's value when the query runs, such as to slice-and-dice the data at runtime, use a parameter value:

1. From the Filter Properties tab, click Parameter , and select the parameter to use. To add a parameter, click Create new parameter.
   
2. Click Apply and Save.

Filter by a calculated value

To derive a filter's value from SQL operations or functions, use a calculated value. For example:

• To always filter a Date field to the "current" date when the query runs, set a calculated value of NOW().
• To offset to a Current Year parameter to filter a Year field to the prior year, set a calculated value of YEAR = :CurrentYear - 1.

To set a calculated value:

1. From the Filters Properties panel, enable Calculated.
   
2. Under Included Columns, add the source columns to use in the calculation.
3. Under Calculation, compose the formula to derive the value.
   • To refer to source columns, enter their assigned numerical tokens, such as {1} + {2}.
   • To refer to a parameter, enter its name after a colon, such as :CurrentYear.
4. Click Apply.

Filter criteria and case-sensitivity

Filter criteria is case-sensitive. For example, if you set a State filter to "Iowa", the results include records with uppercase "Iowa", but not lowercase "iowa". However, you can use calculations to return records regardless of a value's casing.

• To convert a column's values to lowercase, use a calculation with a LOWER() function—such as LOWER(TBL.STATE) = 'iowa'—as the filter.
• To make filter criteria case-insensitive, set a calculated value that includes a regular expression REGEXP_LIKE condition with an i flag, such as SELECT * FROM table WHERE REGEXP_LIKE(column, ‘(?i)ab’).