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.
To filter data in the Query Builder, click the Filter button in the Query group on the Data Source ribbon page...
The Filter tab allows you to filter underlying data and the Group Filter tab allows you to filter aggregated data on the server side.
You can compare a field value in the Filter Editor with the following objects:
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 Creating 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.
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 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.