Query Builder

  • 2 minutes to read

This topic describes how to use the Query Builder to manage data tables and their relations in a Snap data model (e.g., to create master-detail data sources), filter incoming data and specify a custom SQL string.

The Query Builder displays the structure of a connected data source along with its associated Snap data model. When designing a report, this data model is reflected in the Data Explorer.

NOTE

The Query Builder is available for SQL and Data Federation data sources.

This topic includes the following sections.

Run the Query Builder

To run the Query Builder, start by right-clicking a data source in the Data Explorer and clicking Edit Data Source in the invoked menu.

Howto-Bind-Snap-Report-to-Data06

Manage Data Tables

Double-click table names in the rightmost column to add them to the query being edited. To include a particular field in the query, enable the corresponding checkbox.

The Query Builder automatically highlights the tables of an external data source that are related to the tables of a Snap data model by one or more primary keys.

To modify a table join, click its corresponding ellipsis button. This invokes the Join Editor, which allows you to edit existing joins and their types (Inner join or Left outer join), or create new joins.

QueryBuilderJoinEditor

Shape Data

To assign a custom name to the selected data column, specify its Alias in the column list displayed in the lower part of the window.

query-designer-snap-custom-alias-column-display-name

To specify the filter criteria applied to source data, click the Filter button, which invokes the Filter Editor editor.

query-designer-filter-editor

Criteria specified in the Filter Editor dialog are transformed into the WHERE clause, as illustrated in the picture below:

query-builder-filter-editor-result

IMPORTANT

Manual SQL editing is not allowed by default. Enabling custom SQL queries may lead to inadvertent or unauthorized modifications to your data/database structure. Before you decide to allow manual SQL editing, we recommend you to follow best practices and implement the appropriate end-user read/write permissions at the database level.

If for some reason you have to enable custom SQL editing, set the SnapControl.Options.DataSourceWizardOptions.SqlWizardSettings.EnableCustomSql option to true.

Note that only SELECT statements are allowed in the text. To override this restriction, handle the SnapControl.ValidateCustomSql event.

Preview Results

You can click Preview results... at any time to see the results of the query.

query-builder-preview