Query Builder

The Query Builder can be invoked from the SQL Data Source Wizard. It provides a visual editor to create custom queries and enables you to solve a variety of tasks.

Select Tables

To include a specific table or view into a query result set, drag and drop it onto the Query Builder design surface.

web-designer-query-builder-select-table-drag-and-drop

The Query Builder provides a toolbar with the following commands.

Icon Description
web-designer-query-builder-button-delete Removes the selected table or view from the query.
web-designer-query-builder-button-undo Reverses the most recent action.
web-designer-query-builder-button-redo Performs the action that has previously been undone.

Enable check boxes for the table columns you want to include into the query result set.

web-designer-query-builder-select-fields

You can search for a specific table or view by typing required text in the dedicated search box.

web-designer-query-builder-search-tables

Join Tables

To join separate tables and/or views, connect their corresponding columns (key fields) using drag and drop. The connected columns must have identical data types.

web-designer-query-builder-join-tables

Clicking the data relation will display the corresponding properties that define the join type (Inner or Left Outer) and applied logical operator.

web-designer-query-builder-join-tables-selection-properties

A left outer join returns all the values from an inner join along with all values in the "left" table that do not match to the "right" table, including rows with NULL (empty) values in the key field.

When the left outer join is selected, the relationship line displays an arrow pointing at the "right" table of the join clause.

web-designer-query-builder-join-tables-left-outer

After executing the query, it will return a "flat" table composed of data records selected based on the specified join options.

Note

Although joining different tables within a single query may be required in some scenarios, creating hierarchical data sources generally results in better performance (in general, master-detail reports are generated faster than similar-looking reports created by grouping "flat" data sources).

Filter Data

Clicking the Query Builder surface will display the query options.

web-designer-query-builder-selection-properties

The following options are available.

  • Name

    Specifies a custom query name (alias).

  • Filter

    Runs the Filter Editor where you can specify filter criteria against which the query result set should be narrowed down.

  • Group Filter

    Runs the Filter Editor where you can specify filter conditions for grouped and aggregated data. If data is not grouped, this option is disabled.

  • Select All (*)

    Specifies whether or not the query result set should include all columns from the selected tables and/or views, regardless of their individual settings.

    This option is set to No by default.

  • Select Top

    Specifies the number of first records to include to the query result set. The default value is 0 and indicates that the query result set should contain all records that meet all other filter conditions.

  • Offset

    Specifies the number of records to skip before data is retrieved. This option is available only if data is sorted.

  • Select distinct

    Specifies whether to include only distinct values to the result set. The default value is No.

Shape Data

Clicking a data column of a selected table or view will display the data column options.

web-designer-query-builder-shape-data

The following options are available.

  • Name

    Indicates the column name by which it is referred to in the database.

  • Type

    Indicates the type of data contained in the column.

    String columns are provided with information about the maximum string length.

  • Alias

    Specifies a custom column name (alias).

    This option is available only for columns that are included into a query.

  • Output

    Specifies whether or not the column is included into the query result set.

  • Sort Type

    Specifies whether to preserve the original order of data records within the column, or sort them (in an ascending or descending order).

  • Sort Order

    This option becomes available after applying sorting to the data column's records.

    It defines the priority in which sorting is applied to multiple columns (the less this number is, the higher the priority).

    For example, if column A has the sort order set to 1 and column B has it set to 2, the query will be first sorted by column A and then by the column B.

    Changing this setting for one column automatically updates the sort order of other columns to avoid conflict of priorities.

  • Group By

    Specifies whether or not the query result set should be grouped by this column.

    Note

    Grouping and/or aggregation can only be applied to each of the selected columns.

  • Aggregate

    Specifies whether or not the column's data records should be aggregated.

    The following aggregate functions are supported: Count, Max, Min, Avg, Sum, CountDistinct, AvgDistinct, SumDistinct.

    Applying any of these functions to a column will discard individual data records from the query result set, which will only include the aggregate function result.

    Note

    Use aggregation/grouping either for all selected columns or for none of them. Applying the aggregation to one column automatically applies grouping to other selected columns. If you remove all aggregation functions, grouping against other columns will be reset as well.

Preview Results

You can test a query at any time on a limited subset of the actual data by clicking the Preview Results button.

This will open the Data Preview screen displaying the query result set limited by the first 100 data records.

web-designer-query-builder-data-preview

See Also