- 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.
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.
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.
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.
To specify the filter criteria applied to source data, click the Filter button, which invokes the Filter Editor editor.
Criteria specified in the Filter Editor dialog are transformed into the WHERE clause, as illustrated in the picture below:
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.
You can click Preview results... at any time to see the results of the query.