Filter Queries
- 3 minutes to read
SQL queries constructed in the Query Builder can be filtered by including WHERE clauses in the query. Filters can be applied to underlying or aggregated data. You can also limit the number of returned records when filtering data.
Invoke the Filter Editor
To filter data in the Query Builder, click the Filter button in the Query group on the Data Source ribbon page…
… or use a corresponding button within the Query Builder. This invokes the Filter Editor dialog which allows you to build filter criteria:
Tip
Documentation: Filter Editor
The Filter tab allows you to filter underlying data and the Group Filter tab allows you to filter aggregated data on the server side.
Filter Data
You can compare a field value in the Filter Editor with the following objects:
- A static value (represented by the
icon). Click this button to switch to the next item mode (“another field value”) to compare a field value with another field value. - Another field value (represented by the
icon). Click this button to switch to the next item mode (“parameter value”) to compare the field value with a parameter value. - A parameter value (represented by the
icon). Click this button to switch back to the initial mode (“static value”) to compare the field value with a static value.
You can pass the query parameter to the filter string by clicking the button. Next, click the button and finally click <select a parameter>.
In the invoked popup menu, you can choose from the following options.
Add Query Parameter - allows you to create a new query parameter. The following dialog is invoked:
In this dialog, you can specify a parameter’s name (Name), type (Type) and value (Value).
Existing query parameters are displayed within the popup menu.
Bind to - allows you to pass a dashboard parameter to a filter string. You can choose from the list of predefined dashboard parameters or create a new dashboard parameter by selecting Add Dashboard Parameter. If you selected Add Dashboard Parameter, the following dialog is invoked:
In this dialog, you can specify settings of the dashboard parameter to be created. Refer to Create Parameters for more information.
Specify the required settings and click OK. A new dashboard parameter and a new query parameter is created. Note that created dashboard and query parameters are bound automatically.
The Filter Editor’s Group Filter tab allows you to apply filtering to grouped/aggregated data fields by including HAVING clauses in the query. The Group By and Aggregate options in the Query Builder manage grouping and aggregation. Refer to the Edit Column Settings paragraph in the Query Builder topic for more information.
Add Limits
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 a specific number of records in the returned dataset by specifying the records starting with index value.
Note
Note that the Sorting type should be specified in the Query Builder to be able to skip the specified number of records. Refer to the Edit Column Settings paragraph in the Query Builder topic to learn how to apply sorting.