Query Builder is a tool that allows you to graphically model queries included in report and dashboard editors. However, if you make modifications to queries manually, they can no longer be evaluated by Query Builder and cannot be displayed in the graphical editor.
1. In the main menu of the dashboard designer, enter the dashboard menu and the Data Sources.
2. Select a record from the data source and select the edit icon, the window is enabled Data Source Wizard Dashboard, to view the query and modify the required information.
3. Select the option Run Query Builder, Window is enabled Query Builder where you can graphically model the queries.
Views and tables
4. In the category Available Views and Tables in the query Builder you will be able to display a list of tables; select and move a table to the Query Builder design area to include it in a query result set.
Query Builder provides a toolbar with the following commands:
Enable check boxes for the columns in the table that you want to include in the query result set.
Use the dedicated search box to find a specific table or view.
Using Join
When you use Query Builder it allows you to join tables and/or views. Use drag and drop to connect the corresponding columns (key fields). Connected columns must have data types of identical types.
When you define a relationship between tables, you can display the category Relationship Properties. The properties in this section define the type of join (Left or Left Outer joins) and the logical operator applied.
A combination outer left returns all values of an inner join (inner join) along with all the values in the “source” table that do not match the “destination” table and includes rows with null (empty) values in the key field. If you select the combination outer left, the relationship line shows an arrow pointing to the “right” table of the join clause.
The executed query returns a “flat” table that joins different tables within a single query. The specified join options define which data records make up the result set.
Filter data
In the category Query Properties of the Query builder, you will be able to customize the different query options such as:
| Consultation options | Description |
|---|---|
| Name | Enter the name of the custom query (alias). |
| Filter | Runs filter editor where you can specify filter conditions for the resulting data. Filtering criteria can contain parameters of the query. |
| Group filter | 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 in selected tables or views in the query result set, regardless of their individual settings. The default is No. |
| Select Superior | Specifies the number of first records to include in 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. |
| Compensate | Specifies the number of records to be skipped before the reporting engine retrieves the data. This option is available only for sorted data. |
| Select different | Specifies whether to include only different values in the result set. The default value is No. |
Field Configuration
Selecting a table or view and accessing a column of data will take you to the Column Properties in Query Builder, to customize the data column options:
| Consultation options | Description |
|---|---|
| Name | Indicates the name of the column that Query Builder gets from the database. |
| Alias | Specifies the name of the custom column (alias). Include a column in a query to enable this option. |
| Guy | Indicates the data type of the column. Query Builder provides information about the maximum string length for string columns. |
| Exit | Specifies whether the column is included in the query result set. |
| Type of Arrangement (classification) | Specifies whether to preserve the order of the original data records within the column or to sort them (in ascending or descending order). |
| Ranking order | Apply classification to the records in the data column to enable this option. Defines the sorting priority for multiple columns (the lower this number, the higher the priority). For example, set the sort order to 1 for column A and set it to 2 for column B. The Query Builder first sorts the query by column A and then by column B. The sort order for all columns is automatically updated when you change this setting for a column. It allows you to avoid priority conflicts. |
| Group by | Specifies whether the query result set will be grouped by this column. NOTE: You must apply grouping and/or aggregation to each selected column. |
| Aggregate | Specifies whether to add the column’s data records. You can use the following added functions: Count, Max, Min, Avg, Sum, CountDistinct, AvgDistinct, SumDistinct. Query Builder discards individual data records from the query result set and retains only the result of the added function when you apply any of these functions. NOTE: Use aggregation/grouping for all or none of the selected columns. Query Builder applies grouping to all selected columns automatically if you apply aggregation to a column. Query Builder resets grouping to other columns when you remove all aggregation functions. |
Use query parameters
In the category Parameters in the Query builder, you can add, remove, and edit query parameters
| Consultation options | Description |
|---|---|
| Name | Name of the query parameter. |
| Guy | Specifies the data type of the parameter value. Set this property to Expression to dynamically generate parameter values. |
| Result Type | Specifies the data type of the output value of the expression. This property is enabled if the query parameter type is Expression. |
| Value | Determines the actual value of the query parameter. You can specify a static current value based on the data type of the selected value. Alternatively, construct an expression to generate real parameter values dynamically. Click the ellipsis button for this property to invoke the Expression Editor and create an expression. This ellipsis button is enabled if you set the query parameter type to Expression. |
5. After you model the query in the Query Builder, select the Preview Results to test the query on the limited subset of current data at any time. The Open Data Preview screen displays the first 100 data records in the query result set.
