- 5 minutes to read
The Query Builder is a visual queries editor. You can invoke it from the Data Source Wizard.
Drag and drop a specific table or view onto the Query Builder design surface to include it into a query result set.
The Query Builder provides a toolbar with the following commands:
|Removes the selected table or view from the query.|
|Reverses the most recent action.|
|Performs the previously undone action.|
Enable check boxes for the table columns you want to include into the query result set.
Use the dedicated search box to find a specific table or view.
The Query Builder allows you to join tables and/or views. Use drag and drop to connect corresponding columns (key fields). The connected columns should have identical data types.
Click the data relation to display the Relation Properties section. Properties in this section define the join type (Inner or Left Outer) and applied logical operator.
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 and includes rows with NULL (empty) values in the key field.
If you select the left outer join, the relationship line displays an arrow which points at the “right” table of the join clause.
The executed query returns a “flat” table which joins different tables within a single query. The specified join options define which data records compose the query result set.
We recommend you to use hierarchical data sources because the reporting engine generates master-detail reports faster than similar-looking reports which obtains data from “flat” data sources.
Expand the Query Properties section to display the query options.
The query provides the following options:
Specifies a custom query name (alias).
Runs the Filter Editor where you can specify filter conditions for grouped and aggregated data. This option is enabled only for grouped data.
Select All (*)
Specifies whether to include all columns from the selected tables and/or views to the query result set, regardless of their individual settings.
The default value is No.
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 contains all records that meet all other filter conditions.
Specifies the number of records to skip before the report engine retrieves data. This option is available only for sorted data.
Specifies whether to include only distinct values to the result set.
The default value is No.
Select a table or view and click a data column to display the data column options.
The Column Properties section contains the following options:
Indicates the column name which the Query Builder obtains from the database.
Indicates the column’s data type.
The Query Builder provides information about the maximum string length for string columns.
Specifies a custom column name (alias).
Include a column into a query to enable this option.
Specifies whether to include the column into the query result set.
Specifies whether to preserve the original data records’ order within the column, or sort them (in an ascending or descending order).
Apply sorting to the data column’s records to enable this option.
It defines the sorting priority for multiple columns (the less this number is, the higher the priority).
For example, set the sort order to 1 for the column A and set it to 2 for the column B. The Query Builder first sorts the query by column A and then by the column B.
All columns’ sort order automatically updates when you change this setting for one column. It allows you to avoid conflict of priorities.
Specifies whether to group the query result set by this column.
You should apply grouping and/or aggregation to each selected column.
Specifies whether to aggregate the column’s data records.
You can use the following aggregate functions: Count, Max, Min, Avg, Sum, CountDistinct, AvgDistinct, SumDistinct.
The Query Builder discards individual data records from the query result set and keep only the aggregate function result when you apply any of these functions.
Use aggregation/grouping either for all selected columns or for none of them. The Query Builder applies grouping to all selected columns automatically if you apply the aggregation to one column. The Query Builder resets grouping against other columns when you remove all aggregation functions.
Use Query Parameters
Use the Parameters section to add, remove and edit query parameters.
Each query parameter provides the following properties:
Specifies the query parameter’s name.
Specifies the parameter value’s data type.
Set this property to Expression to generate parameter values dynamically.
Specifies the data type of the expression’s result value.
This property is enabled if the query parameter’s type is Expression.
Determines the query parameter’s actual value.
You can specify a static actual value according to the selected value’s data type.
Alternatively, construct an expression to generate actual parameter values dynamically. Click this property’s ellipsis button to invoke the Expression Editor and create an expression. This ellipsis button is enabled if you set the query parameter’s type to Expression.
Click the Preview Results button to test a query on the actual data’s limited subset at any time.
The opened Data Preview screen displays the first 100 data records of the query result set.