BI Dashboard Builder - Table Visualizations - Query By Example (QBE)

As shown above, a filter icon is available on the top-right of a table’s header to Query by Example.

When the icon is clicked, a row of fields appears above the table column headers for the user to enter QBE expressions for each of the table’s columns. Details on comparison and logical operators available to use in the expression fields for String, Number and Date columns are provided below.

Each table column can be queried for blank fields by clicking on the arrow button beside each expression field and selecting “is blank” or “is not blank” from the drop-down menu.

NOTE: The Value Case Hint field property, which is specified in BI Catalog Builder, can be used to generate SQL queries that may benefit from database column indexes. This field property is used to set whether values in a field are always uppercase or lowercase, which allows for a faster case-sensitive search in dashboard filters. However, users should only set this field property if the letter case of the column value is known, as inaccurate search results may be generated if set incorrectly. Refer to the BI Catalog Builder guide for more information.

After entering the QBE expressions, users can execute the query by pressing Enter on their keyboard. Invalid QBE expressions will not be included in the query.

NOTE: QBE is also applied to PDF and Excel exports.

Comparison Operators

The following comparison operators are supported:

Operator

Description

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

=

Equal to

!=

Not equal to

<>

Not equal to

Logical Operators

The logical operators “and” and “or” are supported.  They are case-insensitive.  Only one logical operator is allowed per QBE expression.

Columns

  • STRING: For a STRING column, a QBE value without a comparison operator prefix is implicitly assigned the startsWith operator.

  • NUMBER: For a NUMBER column, a QBE value without a comparison operator prefix is implicitly assigned the = operator.

  • DATE: For a DATE column:

    • The From date is implicitly assigned the >= operator.

    • The To date is implicitly assigned the <= operator.

    • When From date is empty, it is defaulted to the minimum date value for the column.

    • When To date is empty, it is defaulted to the maximum date value for the column.