Filter Queries

The Filter Editor can apply filters to SQL queries by addition conditions that apply to underlying or to aggregated data. It can also limit the number of returned records. You can invoke the Filter editor from inside the Query Builder tool or directly from the Query Tools menu using the Filter button.

The Filter tab allows you to filter underlying data while the Group Filter tab provides the capability to filter data that has been aggregated on the server side. The Group Filter tab is only active when there is an aggregate applied to at least one column in the query.

The Filter Editor compares a selected field value with one of the following:

•      A static value (represented by the pencil icon), entered directly as a text or numeric value

•      Another field value (represented by the square icon), selected in the same way as the first field value

•      A parameter value (represented by the question mark), explained detail below

Click the icon to cycle through the three different comparison choices.

Parameter values can be from “query parameters,” which are defined entirely within the query and cannot be altered later, or from “dashboard parameters,” which are defined as part of the overall report and can be changed by the end-user when viewing the dashboard.

When you change the comparison to a parameter value and click on <select a parameter>, you will have the choice to “Add Query Parameter” or “Bind To” a dashboard parameter. In addition to listing any dashboard parameters that have already been defined, the “Bind To” option also lists the choice to “Add Dashboard Parameter” from scratch.

For more information on creating and using dashboard parameters, see the section on Using Dashboard Parameters.

The Filter Editor also allows you to limit the number of returned records. To do this, enable the Select only checkbox and specify the number of records to be returned.

You can also skip the required number of records in the returned dataset by specifying the records starting with index value.

Checking the option to “select only distinct values” will return exactly one row for each set of values across all the columns the query returns.