How to create a query using the query builder
1 In Data Explorer, right-click Data Sets, then choose New Data Set.
2 In New Data Set, specify the following information:
1 In Data Source Selection, select the JDBC Database Connection for Query Builder data source to use. Data Set Type displays SQL Select Query [Query Builder].
2 In Data Set Name, type a name for the data set.
3 Choose Next.
The query builder appears, as shown in
Figure 4‑17. The top pane displays a SELECT statement. You can type a query here, or use the tools in the middle and bottom panes to create the query. The rest of this procedure describes the steps for creating a query graphically.
Figure 4‑17 Query Builder
3 Select the tables and columns that contain the data to use in the report.
1 Right-click in the middle pane, and choose Add Table.
2 Expand a database schema, and select the desired table. The table and its columns appear in the middle pane.
3 Select the desired columns.
4 Repeat the previous steps to select columns in other tables.
4 Join the tables.
1 Right-click in a table, then choose Create Join.
2 In Create Join, specify the tables and columns to join, and the join type.
Figure 4‑18 shows an example of an inner join on the CUSTOMERNUMBER columns in the CUSTOMERS and ORDERS tables.
Figure 4‑18 An inner join between two tables
3 Repeat the previous steps to join all the tables.
Figure 4‑19 shows three tables that are joined. The SELECT statement in the top pane is updated to reflect the selected columns and table joins. The bottom pane lists the selected columns and their properties.
Figure 4‑19 Query Builder showing three joined tables
5 If necessary, use the tabs in the bottom pane to do the following:
Choose Columns to edit column properties, create computed columns or complex expressions.
Choose Conditions to filter data rows.
Choose Groups to group aggregate data.
Choose Group Conditions to filter groups.
The following sections provide more information about each task.
