In the wizard's first dialog, or alternatively on the "Axis Definition" tab, you first define the grouping for the rows and columns, i.e. the characteristics.

1.  In the "Rows" pane, click on the "Insert a row grouping" button.

2.  In the formula wizard, you now enter the field or the expression for the row grouping e.g. Customers.Country. You have now created a row grouping and the data will be grouped by this characteristic.

3.  In the "Columns" pane, click on the "Insert a column grouping" button.

4.  In the formula wizard, you now enter the field or the expression for the column grouping.

5.  Since you first want to group the data by year, you must enter an expression here that returns the year of the order date. You have the Year() function in the formula wizard at your disposal; i.e. you select this function from the list and insert the order date as the parameter by double-clicking. The formula looks like this: Year(Orders.OrderDate).

Figure 6.48: Definition of the axes of a three-dimensional crosstab

6.  Since we also want to examine the data at another level, insert an additional column grouping via the "Insert a column grouping" button.

7.  Now enter an expression to return the quarter of the order date. You can use the Quarter() function in the formula wizard for this. The formula then looks like this: Quarter(Orders.OrderDate).

Note: You can change the order of the groupings with the arrow button. The column at the bottom is the inner grouping.

To swap lines and rows (Pivot function) use the button on the lower right on the "Axis Definition" tab. This button is only available in the object dialog, not in the wizard.

Figure 6.49:Swap all rows and lines

You have now created the groupings and you can go on to define the value for the intersection of the respective columns and rows.

8.  Click on the "Add a result cell" button located under "Result cells".

9.  Now select the aggregate function that you want for the contents in the "Cell Contents" dialog that appears. You want to create a sales evaluation so choose the "Sum" function in the "Summary by"-Tab. In the upper part of the dialog, you can specify the contents by clicking the formula button to start the formula wizard.

In the Sample Application, the sales per order value is not supplied directly as a field so you must calculate it using the "Sum(Order_Details.Quantity * Order_Details.UnitPrice)" formula.

Figure 6.50: Definition of the result cell

10. Then switch to the "Display as"-Tab to define the appearance of the cell content.