The requested page is not available for the requested platform. You are viewing the content for Default platform.

Filter Queries

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...

EditQueriesButton_Ribbon

... or use a corresponding button within the Query Builder. This invokes the Filter Editor dialog which allows you to build filter criteria.

FilterEditorDialog_SqlDataSource

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 Parameters_FilterEditor_CompareButton 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 Parameters_FilterEditor_CompareButton2 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 Parameters_FilterEditor_CompareButton3 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 Parameters_FilterEditor_CompareButton button. Next, click the Parameters_FilterEditor_CompareButton2 button and finally click <select a parameter>.

FilterEditorDialog_AddParameterMenu

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:

    CreateQueryParameterDialog

    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:

    CreateDashboardParameterDialog

    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.

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.

FilterEditorDialog_TopSkip

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.