Skip to main content

SQL Query Builder

  • 7 minutes to read

The Query Builder allows you to construct SQL queries to retrieve data from an SQL Database.

Query Builder

Run the Query Builder

Use the Data Source Wizard or Report Wizard to bind your report to an SQL Database. Switch to the query customization page and click the Add button in the Queries row.

Data Source Wizard's Query Customization Page

Select Tables

Drag the table that you want to add to a query from the list of available tables and drop the table onto the Query Builder surface.

Query Builder: Drag and Drop a Table

To find a table by name, switch to the table list, press CTRL+F, and enter the table name in the editor.

Query Builder: Find a Table by Name

Enable checkboxes for the table columns that you want to include in the query.

Query Builder: Select Columns

If you want to include all the columns available in the table, enable * (All Columns).

Right-click the table and select Rename or Delete to change the table’s name or remove it.

Query Builder: Rename or Delete Column

Join Tables

You can join multiple tables within the same query. Do one of the following to add a table onto the Query Builder surface:

  • Drag and drop a table from the table list onto the surface.
  • Double-click a table in the table list.

The table list on the left highlights all child and parent tables that are bound to the dropped table by a foreign key.

Query Builder: Table Added to a Query

Add required tables to the surface. The Inner Join relation with the previously added table is created automatically. Added tables display the green plus button for the columns that refer to other tables. You can click this icon to add a linked table to the query and create the Inner Join relation with this table.

Query Builder: Create the Inner Join Relation Automatically

Right-click a relation to edit, delete, or reverse.

Query Builder: Edit Relation

The Edit Relation command invokes the Join Editor. It allows you to specify the join type (Inner, Left Outer, Right Outer, or Full Outer ), columns by which the tables should be joined, and a logical operator (Equal to, Is less than, or others) used to compare table columns.

Query Builder: Join Editor

You can manually join tables if they are not bound by a foreign key at the database level. In this case, when you drag-and-drop a table onto the Query Builder surface, the Join Editor is automatically invoked, and this editor allows you to construct a custom join relation.

Query Builder: Create Relation Manually in the Join Editor

Note

When you join multiple tables within a single SQL query, you create a flattened table composed of data records selected based on the specified join relations. You can also create hierarchical data sources. In general, master-detail reports are generated faster than similar-looking reports created based on flattened data sources. If possible, use hierarchical data sources instead of flattened ones.

Shape Data

The Query Builder displays a list of the query’s columns at the bottom-right corner.

Query Builder: List of Selected Table Columns

This list allows you to add new table columns to the query or shape selected table columns. The following options are available:

Column

Specifies the selected column. Click the down-arrow button to display a drop-down column list and replace the column with another column. Click the ellipsis button to replace the column with an expression.

Query Builder: Specify an Expression for a Column

Table
The table that contains the selected column. When you create an expression for a column, this option displays (All Tables).
Alias
A custom column name.
Output
Specifies whether to include the column in the query’s resulting set.
Sorting Type

Specifies whether to keep the initial data record order (Unsorted) or sort the records by the column (Ascending or Descending).

Note

When you bind a report to an XML file, the Query Builder does not support sorting by aggregate functions, the DISTINCT and SELECT ALL statements, and manual SQL editing.

Sort Order
Defines the sort order when data is sorted by multiple columns. For example, if column A has the sort order set to 1 and column B has it set to 2, data records are first sorted by column A and then by column B. This option is available if you enable the Sorting Type option.
Group By
Specifies whether to group the query’s resulting set by this column.
Aggregate

Specifies whether to apply an aggregate function to column values. The following aggregate functions are supported:

  • Count
  • Max
  • Min
  • Avg
  • Sum
  • CountDistinct
  • AvgDistinct
  • SumDistinct

When you want to use the Group By or Aggregate operations, you should apply them either to all columns or none of them. When you use these operations, only the result of aggregation or grouping is included in the result set.

Filter Data

Click the Filter button to invoke the Filter Editor.

Query Builder: Click Filter Button

Query Builder: Filter Editor

The editor has the following tabs:

Filter Tab
Allows you to build criteria to filter data for the report. Filter criteria can reference query parameters, which you can also map to report parameters.
Group Filter Tab
Allows you to specify filter conditions for grouped and aggregated data. This tab is disabled if the data is not grouped.

You can also enable the Select only option to limit the number of resulting data records. If you enable the Sorting Type option for at least one column, you can specify how many data records should be skipped.

Note

Some data providers do not support the skip setting in the provider-specific SQL string. If you enable the Select only option for such data providers, data records are skipped but the SKIP statement is not included in the SQL query.

The Select only distinct values option allows you to include only unique values into the resulting set.

Edit Query Parameters

Click Edit Parameters to invoke the Query Parameters dialog.

Query Builder: Click Edit Parameters Button

This dialog allows you to add, edit, and remove query parameters. The created parameters are available on the Configure Query Parameters wizard page.

Query Builder: Query Parameters Dialog

The following properties are available for each query parameter:

Name
The name by which you can reference the parameter.
Type
The data type of the parameter’s value.
Expression
Specifies whether the parameter’s value is static or generated dynamically. You can enable this option when you need to map the parameter’s value to a report parameter‘s value.
Value
The parameter’s value. If the Expression option is enabled, this value is generated dynamically based on the parameter’s expression.

Refer to the following topic for more information: Use Query Parameters.

Edit SQL Queries As Text

Click Edit SQL to edit the query in a text-editing window.

Query Builder: Edit Query as Text

Note

In the End-User Report Designer, manual edit operations of SQL queries are disabled since they are considered unsafe. Refer to the following topics for more information:

Preview Results

Click the Preview Results button to open the Data Preview dialog.

Query Builder: Preview Results Button

The dialog displays the first 1000 rows of the query result set.

Query Builder: Preview Results

Manage Queries

Right-click a data source in the Report Explorer or Field List and select Manage Queries in the context menu.

Invoke the Manage Queries Dialog

This invokes the Manage Queries dialog that allows you to perform operations on queries and stored procedures.

Manage Queries Dialog

Add a New Query

Click Add, specify a name for the new query, and select tables that you want to include in the query.

Manage Queries Dialog: Add a New Query

Copy and Remove Queries

Select a query and click the Copy or Remove icon.

Manage Queries Dialog: Copy or Remove a Query

Modify Stored Procedures

Select an existing stored procedure and choose a new one that you want to include in a query.

Manage Queries Dialog: Modify a Stored Procedure

Add a New Stored Procedure

Expand the Add menu and select Stored Procedure.

Manage Queries Dialog: Add a Stored Procedure

Rename a Query or Stored Procedure

Double-click an item in the list of queries and stored procedures and use the editor to specify a new name for this item.

Manage Queries Dialog: Change an Item's Name

See Also