## Tutorial 6: Creating a cross tab

This tutorial provides instructions for creating a cross tab that displays sales totals by state and product line. The cross tab uses data from the Customers, OrderDetails, and Products tables in the sample database, Classic Models.

In this tutorial, you perform the following tasks:

- Create a new report.
- Build a data source.
- Build a data set.
- Add a cross tab to the report.
- Organize data for the cross tab.
- Insert data in the cross tab.
- Add grand totals.
- Format the cross tab.

### Task 1: Create a new report

1. Choose File->New->Report.
2. On New Report, select a project in which to save your report.
3. Type the following text as the file name:

    SalesByStateAndProductLine.rptdesign

4. Choose Finish. A blank report appears in the layout editor.

### Task 2: Build a data source

Before you begin designing your report in the layout editor, you create a data source to connect your report to the Classic Models database.

1. Choose Data Explorer.
2. Right-click Data Sources, and choose New Data Source from the context menu.
3. Select Classic Models Inc. Sample Database from the list of data sources, use the default data source name, then choose Next. Connection information about the new data source appears.
4. Choose Finish. BIRT Report Designer creates a new data source that connects to the sample database. It appears within Data Sources in Data Explorer.

### Task 3: Build a data set

In this procedure, you build a data set to indicate what data to extract from the Customers, OrderDetails, and Products tables.

1. In Data Explorer, right-click Data Sets, and choose New Data Set.
2. On New Data Set, type the following text for data set name:

    Sales

3. Use the default values for the other fields:

- Data Source shows the name of the data source that you created earlier.
- Data Set Type specifies that the data set uses a SQL SELECT query.

4. Choose Next. Query displays the information to help you create a SQL query. The text area on the right side shows the required keywords of a SQL SELECT statement.
5. Use the following SQL SELECT statement to indicate what data to retrieve. You can type the column and table names, or you can drag them from Available Items to the appropriate location in the SELECT statement:

    SELECT Customers.state,
    Orderdetails.quantityOrdered,
    Orderdetails.priceEach,
    Products.productline

      FROM Customers INNER JOIN Orders ON Customers.customerNumber
        = Orders.customerNumber

    INNER JOIN Orderdetails ON Orders.orderNumber =
    Orderdetails.orderNumber
    INNER JOIN Products ON Orderdetails.productCode =
    Products.productCode
    WHERE Customers.country = 'USA'

This SELECT statement joins four tables to get the required data.
- Choose Finish to save the data set. Edit Data Set displays the columns specified in the query, and provides options for editing the data set.
- Choose Computed Columns, then choose New to create a computed field that calculates extended prices by multiplying values from the QUANTITYORDERED and PRICEEACH fields.
- On New Computed Column, specify the following values, as shown in Figure 17-2:

1. In Column Name, type:

    EXTENDED_PRICE

2. In Data Type, select Decimal
3. In Expression, type:

    row["QUANTITYORDERED"] * row["PRICEEACH"]

Alternatively, you can open the expression builder to construct the expression by selecting the appropriate data set fields. Note that unlike the SELECT statement where you can type table field names in any case, data set field names are case-sensitive. If, in Expression, you typed row["quantityOrdered"], BIRT displays an error when you preview the results returned by the data set.

Figure 17-2 Computed field, EXTENDED_PRICE
4. Choose OK to save the computed field.

- Choose Preview Results to confirm that the query is valid and that it returns the correct data. If you created the SELECT statement and computed field correctly, you should see the results that appear in Figure 17-3. These are the data rows that the query returns.

Figure 17-3 Preview of rows returned by the Sales data set
- Choose OK to save the data set.

### Task 4: Add a cross tab to the report

You use the palette to add a cross tab.

1. Choose the palette, then drag a cross tab element from the palette to the report. A cross tab appears in the report, as shown in Figure 17-4.

Figure 17-4 Cross tab inserted in the report
The cross tab displays instructions for adding data to specific areas in the cross tab.
2. Choose Data Explorer, expand Data Sets, then expand Sales to display the fields in the data set. Drag STATE into the cell that displays Drop data field(s) to define rows here.
Cross Tab Cube Builder appears, as shown in Figure 17-5.

Figure 17-5 Cross Tab Cube Builder

### Task 5: Organize data for the cross tab

In this procedure, you set up:

- A data group, or dimension, whose values to use as the row headings
- A data group, or dimension, whose values to use as the column headings
- A summary field, or measure, whose aggregate values to use in the cells of the cross tab

Data that you set up for a cross tab is stored in an entity called a cube.

1. On Cross Tab Cube Builder, in Name, change the name of the cube from Customer Cube to Sales Cube, which more accurately describes the data.
2. In Available Fields, drag STATE and drop it on (Drop a field here to create a group) under Groups (Dimensions), as shown in Figure 17-6.

Figure 17-6 Adding a state group to the cube
Group Level displays the properties of the STATE group, as shown in Figure 17-7.

Figure 17-7 Group Level, displaying properties of the STATE group
Choose OK to accept the default values.
3. On Cross Tab Cube Builder, in Available Fields, drag PRODUCTLINE and drop it on (Drop a field here to create a group) under Groups (Dimensions).
4. On Group Level, choose OK to accept the default property values.
Cross Tab Cube Builder shows the STATE and PRODUCTLINE groups you created, as shown in Figure 17-8.

Figure 17-8 Cross Tab Cube Builder, displaying two groups
5. In Available Fields, drag EXTENDED_PRICE and drop it on (Drop a field here to create a summary field) under Summary Fields (Measures).
Properties, shown in Figure 17-9, displays the properties of the EXTENDED_PRICE summary field. The SUM function, the most commonly used aggregate function, is selected by default. Because we want the cross tab to display the sales totals, use SUM.

Figure 17-9 Properties of the EXTENDED_PRICE summary field
Choose OK to accept the default property values for the summary field.
Cross Tab Cube Builder displays the summary field you created. The complete data definition for the Sales cube should look like the one shown in Figure 17-10.

Figure 17-10 Cross Tab Cube Builder, displaying two groups and a summary field
6. Choose Finish to save the cube. The Sales cube appears in Data Explorer, as shown in Figure 17-11.

Figure 17-11 Data Explorer, showing the Sales cube

### Task 6: Insert data in the cross tab

You insert data from the cube into the row, column, and detail areas of the cross tab.

1. On Data Explorer, under the first group in Sales Cube, drag STATE and drop it in the cross tab area that displays Drop data field(s) to define rows here, as shown in Figure 17-12.

Figure 17-12 Inserting STATE data in the cross tab
2. From Sales Cube, under Group1, drag PRODUCTLINE and drop it in the cross tab area that displays Drop data field(s) to define columns here.
3. From Sales Cube, under Summary Field, drag EXTENDED_PRICE and drop it in the cross tab area that displays Drop data field(s) to be summarized here.
The cross tab should look like the one shown in Figure 17-13.

Figure 17-13 Cross tab design
4. Choose Preview to preview the cross tab output. Figure 17-14 shows a portion of the data that the cross tab displays.

Figure 17-14 Preview of the cross tab data
The row headings display the states, the column headings display product lines, and the cells display the sales totals. The first cell displays 458563.64, which is the sales total of classic cars sold in California.

### Task 7: Add grand totals

Each number that is displayed in the cross tab represents the sales total of a particular product for a particular state. In this procedure, you add grand totals to display the total sales of all products for each state, the total sales of each product, and the total of all sales across products and states.

1. Choose Layout to return to the layout editor.
2. In Property Editor, choose the Row Area tab.
3. Choose Grand Totals, then choose Add.
4. On Grand Total, shown in Figure 17-15, use the default values, then choose OK.

Figure 17-15 Creating grand totals in the cross tab row area
A new row is added to the cross tab, as shown in Figure 17-16. In this row is a label that displays Grand Total and a data element that displays [EXTENDED_PRICE].

Figure 17-16 Cross tab with a new row to display grand totals
5. In Property Editor, choose the Column Area tab.
6. Choose Grand Totals, then choose Add.
7. On Grand Total, use the default values, then choose OK.
A new column is added to the cross tab, as shown in Figure 17-17.

Figure 17-17 Cross tab with a new column to display grand totals
8. Preview the report. Grand totals appear in the last row and last column of the cross tab.

### Task 8: Format the cross tab

Now that the cross tab displays the correct data, you can focus on improving the presentation of data in the cross tab. You perform the following tasks in this section:

- Display a string in empty cells.
- Change the format of the numbers.
- Edit the column headings.
- Use darker lines around the cross tab and around the cells.

#### Display a string in empty cells

If there are no sales for a particular product in a particular state, the cell displays nothing. Because an empty cell can be interpreted as missing data, you can display a string, such as 0.00 or --, to indicate that the value is zero.

1. Choose Layout to return to the layout editor.
2. Select the cross tab. Select the entire cross tab by clicking on the tab in the lower left corner of the cross tab.
3. In Property Editor, choose the Properties tab, then choose General.
4. In For empty cells, show:, type the following text:

    --

Figure 17-18 shows the value you type in the cross tab's General properties page.

Figure 17-18 General properties of the cross tab
5. Preview the report. Cells that were previously empty now display --.

#### Change the format of the numbers

To clearly indicate that the numbers displayed in the cross tab are dollar amounts, use the currency format. It is also easier to read the numbers if they are aligned to the right.

1. Choose Layout to return to the layout editor.
2. Choose Element->New Style.
3. In Custom Style, type the following name for the new style:

    crosstab_currency

4. Choose Format Number from the list of property categories.
5. In Format As, select Currency from the drop-down list.
6. In Currency Setting, specify the values as shown in Figure 17-19.

1. Set Decimal places to 2.
2. Select Use 1000s separator.
3. In Symbol, select $.

Figure 17-19 Format Number, showing the currency settings

7. Choose Text Block from the list of property categories.
8. In Text alignment, select Right, as shown in Figure 17-20.

Figure 17-20 Text Block, showing the change in text alignment
9. Choose OK to save the crosstab_currency style.
10. In the layout editor, select the four [EXTENDED_PRICE] data elements, as shown in Figure 17-21.

Figure 17-21 Data elements selected
11. In General properties of Property Editor, in Style, select crosstab_currency from the drop-down list, as shown in Figure 17-22.

Figure 17-22 Select the crosstab_currency style to apply to the data elements
12. Preview the report to verify the formatting change.

#### Edit the column headings

When you insert a summary field, BIRT adds a label that displays the field name as a column heading. In the generated cross tab, the heading EXTENDED_PRICE appears in every column below the product line names. Because the cross tab displays values from one summary field only, the label is not necessary.

1. Choose Layout to return to the layout editor.
2. Select the two labels that display EXTENDED_PRICE, as shown in Figure 17-23. These labels are directly below [PRODUCTLINE] and Grand Total.

Figure 17-23 EXTENDED_PRICE labels selected
3. Press Delete.
4. Preview the report. Figure 17-24 shows a portion of the generated cross tab.

Figure 17-24 Preview of the cross tab

#### Use darker lines around the cross tab and around the cells

BIRT uses two predefined styles, crosstab and crosstab-cell, to set the default appearance of the overall cross tab and the cells, respectively.

1. Choose the Outline view.
2. Expand the Styles item to show the crosstab and crosstab-cell styles, as shown in Figure 17-25.

Figure 17-25 Outline view, displaying the crosstab styles
3. Right-click the crosstab style then choose Edit Style.
4. On Edit Style, choose Border. The colors of the borders are set to Gray.
5. Change the color of all the borders to Black, as shown in Figure 17-26.

Figure 17-26 Border colors set to Black
6. Choose OK to save your change to the crosstab style.
7. In Outline, right-click the crosstab-cell style, then choose Edit Style. Use the same steps to set all the border colors to Black, then choose OK.
In the layout editor, the lines around the cells and around the cross tab appear in black, as shown in Figure 17-27.

Figure 17-27 Cross tab, showing black borders
8. Preview the report. Figure 17-28 shows a portion of the generated cross tab.

Figure 17-28 Preview of the finished cross tab
Figure 17-29 Cross tab with multiple dimensions in the row and column areas
Figure 17-30 Cross tab displays sales subtotals and grand totals
Figure 17-31 Cross tab displays sales totals by state and product line
Figure 17-32 Cross tab displays full state names
Figure 17-33 Cross tab displays sales totals grouped into regions
Figure 17-34 Data sorted by default
Figure 17-35 Data sorted by product line grand totals, in ascending order
Figure 17-36 Data sorted by product line totals in ascending order for the West