In Report Studio, complete the following tasks to create a cross tab:
Select data for the cross tab.
Lay out the cross tab.
Specify display options for the aggregate values.
Sometimes data objects created in BIRT Designer Professional contain links to enable users to drill through the data hierarchy in the data object to view either summary or detail information. For example, consider data organized in years, in a quarter‑month‑week hierarchy. If the data object contains defined hyperlinks for this hierarchy, choosing a year displays the quarters that make up the year, choosing a quarter displays the months in the quarter, and so on.
Selecting data for a cross tab
When building a cross tab, you group the aggregate data by at least two dimensions. To create the cross tab, one dimension is inserted in the row area, and one dimension (Productline) is inserted in the column area.
Each additional dimension by which you group data appears as a column or row, and provides a more comprehensive and detailed view of the data. If you calculate aggregate data by too many dimensions, the resulting cross tab can display several empty cells, causing a problem known as data sparsity. Additionally, a cross tab that contains more than two or three dimensions in either the row or column area is difficult to read. Avoid these problems by dividing the data into multiple cross tabs, enabling users to understand the data more quickly and easily.
How to select data for a cross tab
1 In Report Studio, choose New. Report Template appears.
2 In Report Template, complete the following steps:
1 In Category, select Standard from the drop-down list.
2 In Available Templates, select Crosstab, as shown in Figure 4‑2.
Figure 4‑2 Choosing the cross tab template
3 In Themes, select a theme from the drop-down list if your cross tab template contains associated themes.
Choose OK. Crosstab Builder—Data appears.
3 In Use Data From, choose New Data.
Select Data appears as shown in Figure 4‑3, displaying the available data objects. If you use customized data objects, ensure that they are placed in subfolders of your file repository.
4 In Available Data, select a data object design or data object store file containing a cube or a data model. Then, choose the right arrow.
The data object appears in Selected Data, as shown in Figure 4‑3.
5 To modify your selection, select the data object in Selected Data, then choose the left arrow. Then, repeat step 4. Crosstab Builder—Data displays the data from the cube or data model. Choose OK.
Figure 4‑3 Selecting a data object
Laying out data in a cross tab
When you lay out data in a cross tab, provide the following information:
In Dimensions, specify:
The grouped data fields to display as rows
The grouped data fields to display as columns
In Measures, specify the summary fields to display in the cross tab.
How to lay out data in a cross tab
1 In Crosstab Builder—Data, in Dimensions, in Available Data, select a grouped data field. To select multiple data fields, press Ctrl as you select each field.
2 Choose the right arrow next to Row to set the selected field as a row, or choose the right arrow next to Column to set the selected field as a column. The example shown in Figure 4‑4 specifies State as a row, and Product Line as a column.
Figure 4‑4 Selecting data to display in the cross tab
3 In Measures, in Available Data, select a data field to aggregate, then choose the right arrow to set the selected field as a summary field. The example in Figure 4‑4 sets the Extended Price field as the summary field.
Choose OK. If the OK button is not visible in your web browser, select the up arrow button in the Dimensions or Measures area until the OK button becomes accessible. The selected fields appear in a cross tab in Report Studio.
Filtering data in a cross tab
To display a subset of the available data in the cross tab, you can specify a filter condition for the cube or data model, as shown in Figure 4‑5.
To create a filter at the data object level, choose Filter, and specify one or more filter conditions, as you would do for a BIRT report. For example, to view data only for Pennsylvania (PA), create a filter on the state dimension using the Equal To operator, as shown in Figure 4‑5.
Figure 4‑5 Specifying a data object filter
Figure 4‑6
The cross tab displays aggregate extended price data by product line, for only the state of Pennsylvania, as shown in Figure 4‑7.
Figure 4‑7 A cross tab displaying filtered data rows
You can also select the In operator in Condition to set a filter condition for more than one value. For example, you can define a filter condition that displays data for Pennsylvania (PA), California (CA), and Massachusetts (MA) in the cross tab.
Displaying aggregate values
The cross tab in Figure 4‑7 displays sales data by product line and state.
You can display totals for each dimension that you add to a cross tab and for each level within a multilevel dimension. The rows and columns that display the subtotals and grand totals are highlighted in the cross tab. In the example cross tab you just created, no subtotals appear. Subtotals are displayed when you set a multidimensional data field as a row or column.
Each number displayed in a cross tab represents an aggregate total. Grand totals display the total sales of all products for each state, the total sales of each product, or the total of all sales across products and states. Subtotals display the sales of each product in each state, and so on. You specify the aggregate totals that appear in a cross tab.
How to display aggregate values in a cross tab
1 To specify display properties for the aggregate values in the cross tab, in Crosstab Builder, choose Crosstab Builder—Format, as shown in Figure 4‑8.
Figure 4‑8 Specifying display options for the aggregate values
2 In Grand Totals, do the following:
Select Show Grand Totals for Rows to display grand totals for each row.
Select Show Grand Totals for Columns to display grand totals for each column.
3 You can display subtotals for multilevel dimensions used in rows or columns. Because the example in Figure 4‑8 does not contain multilevel dimensions, the Sub Totals section is not highlighted. In Sub Totals, if highlighted, do the following:
Select Show Sub Totals for Rows.
Select Show Sub Totals for Columns.
4 In Page Break, as shown in Figure 4‑8, select Enable Page Break to specify pagination properties for the cross tab, and do the following:
In Row Interval, type a value, such as 40.
In Column Interval, type a value, such as 10.
Choose OK. Sample data appears in the cross tab, displaying grand totals for rows and columns, as shown in Figure 4‑9.
Figure 4‑9 Displaying grand totals for rows and columns in a cross tab
Saving and viewing a cross tab
As with other BIRT design files, Report Studio displays only a preview of the actual data in the cross tab. You save and view a cross tab in the same way that you save and view a BIRT design, as described in earlier sections of this document.
How to save and view a cross tab
1 In Report Studio, choose Save and View.
2 Save As appears. Specify a file name and a folder location in which to store the saved file. Choose OK.
The cross tab appears in Actuate Viewer, as shown in Figure 4‑10.
Figure 4‑10 Viewing the cross tab in Actuate Viewer
Select data for the cross tab.
