Displaying multiple dimensions in row and column areas
When building a cross tab, you typically group the aggregate data by at least two dimensions, for example, sales totals by year and product line, or sales totals by year and state. Often, report users want to view aggregate data by more than two dimensions.
The cross tab in Figure 24‑36 shows the sales total by territory, year, product line, and product. To create the cross tab, two dimensions (Territory and Year) are inserted in the row area, and one dimension (ProductLines) is inserted in the column area. The ProductLines dimension has two levels, ProductLine and ProductName.
Figure 24‑36 Cross tab using multiple dimensions in row and column areas
As the example shows, each additional dimension by which you group data appears as a column or row, and each additional dimension provides a more comprehensive and detailed view of the data. Just as you can define an unlimited number of dimensions for a cube, you can build a cross tab that displays aggregate data by as many dimensions as you want.
Calculating aggregate data by too many dimensions, however, can result in many empty cells, a problem commonly referred to as data sparsity. When designing a cube that contains more than two dimensions, make sure that processing time is not spent calculating zeros.
In addition, a cross tab that contains more than two or three dimensions in either the row or column area is difficult to read. Rather than display data by too many dimensions in a single cross tab, consider dividing the data into multiple cross tabs.
How to insert multiple dimensions in the row or column area
1 Drag a dimension from the cube and drop it in the row or column area of the cross tab.
2 Drop the second dimension either on the left or right side of the first dimension, depending on the order in which you want to display the data. Figure 24‑37 shows placing the second dimension on the right side of the first dimension, as indicated by the position of the line cursor.
Figure 24‑37 Dropping a second dimension in the row area of a cross tab