Specifying the data to retrieve
When you create a JDBC Database Connection for Query Builder data source, as described in the previous topic, you have access to the SQL query builder. This graphical tool provides access to your database schema and objects, and wizards that help you select and join tables and sort, group, and filter data.
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 5‑16. 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 5‑16 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 5‑17 shows an inner join on the CUSTOMERNUMBER columns in the CUSTOMERS and ORDERS tables.
Figure 5‑17 An inner join between two tables
3 Repeat the previous steps to join all the tables.
Figure 5‑18 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 5‑18 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 and 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.
Creating computed columns and complex expressions
Using SQL, you can manipulate data to return it in the format that you require. You can create computed columns that return values derived from multiple fields, for example:
QuantityOrdered * PriceEach
ContactFirstName || ' ' || ContactLastName
You can aggregate data using SQL functions, for example:
SUM(OrderAmount)
AVG(OrderTotal)
You can create statements that provide if-then-else logic, for example:
CASE WHEN QuantityInStock > 0 THEN 'In Stock' ELSE 'Out of Stock' END
How to create a computed column
1 Choose Columns.
2 In Column, click in an empty cell. Click the arrow button, scroll down the list of available columns, and choose Build Expression.
3 Click outside the cell to open the expression builder.
4 In Expression Builder, select the type of expression to build and choose Next. Expression Builder displays different properties depending on the expression type.
Figure 5‑19 shows an example of a function expression. The expression uses an aggregate function, SUM, to calculate order totals. The expression, SUM(QUANTITYORDERED * PRICEEACH), is created by selecting the SUM function, and the required columns and operator to use in the calculation.
Figure 5‑19 Example of a function expression
Choose Finish. The computed column appears under Columns.
5 In Alias, type an alias for the computed column so that it is easily identified.
Filtering data rows
Databases typically contain vast amounts of data. Reports, however, typically use a small subset of the data, so SQL queries often contain filter conditions to limit the rows returned.
How to filter data rows
1 Choose Conditions.
2 Create a filter condition.
1 In Column, select a column or Build Expression to create an expression.
2 In Operator, select an operator.
3 In Value, select a column or type a value.
4 In AND/OR, optionally select AND or OR to specify another filter condition.
Figure 5‑20 Filter condition on data rows
The following WHERE clause is added to the SELECT statement in the top pane:
WHERE CLASSICMODELS.ORDERS.ORDERDATE > '2004-06-30'
Grouping data
If you use aggregate functions, such SUM or AVG, you typically have to group the results by one or more columns. For example, an Orders table contains order records and some customers have multiple orders. To get the order totals for each customer, you would use the GROUP BY clause to group the customers, and the SUM function to aggregate the order totals by customer.
How to group data
1 Choose Groups.
2 Under Column, click in an empty row. Click the arrow button, and select the column whose aggregate data to group.
Figure 5‑21 shows an example in which results are grouped on the CUSTOMERNAME column.
Figure 5‑21 Grouping on the CUSTOMERNAME column
The following GROUP BY clause is added to the SELECT statement:
GROUP BY CLASSICMODELS.CUSTOMERS.CUSTOMERNAME
Filtering groups
You can specify filter conditions for data that is grouped. For example, if order records are grouped by customer, you can select only customers whose order totals exceed $150,000. The filter condition for a group is specified using the HAVING clause. This clause behaves like the WHERE clause, but is applicable to groups. The WHERE clause, on the other hand, applies to individual rows.
A SELECT statement can contain both WHERE and HAVING clauses. For example, you can select customers whose order totals exceed $150,000, factoring only orders placed after 06/30/2004. The SELECT statement would look like the following:
SELECT CustomerName, SUM(OrderAmount) FROM Orders
WHERE OrderDate > '2004-06-30'
GROUP BY CustomerName
HAVING SUM(OrderAmount) > 150000
How to filter groups
1 Choose Group Conditions.
2 Create a filter condition.
1 In Column, select a column or Build Expression to create an expression.
2 In Operator, select an operator.
3 In Value, select a column or type a constant value.
4 In AND/OR, optionally select AND or OR to specify another filter condition.
Figure 5‑22 shows an example of a filter condition specified for groups.
Figure 5‑22 A filter condition specified for groups
The following HAVING clause is added to the SELECT statement:
HAVING SUM(CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH) >= 150000
