Defining comparison functions in a crosstab

A comparison function refers to calculations of percentage, permillage, or difference between:

• subtotal and grand total
• subtotal of inner group and subtotal of outer group
• values of aggregate field and subtotal
• values of aggregate field and grand total

To define comparison functions in a crosstab:

1. Right-click the crosstab and select Crosstab Wizard from the shortcut menu to open the Crosstab Wizard.
2. In the Display screen, select an aggregate field in the Summaries box and then click the Comparison Function button. The Comparison Function dialog appears. See the dialog.

   

3. From the Function drop-down list, select the required function: Percentage, Permillage or Difference.
4. Specify a position for the comparison function.
   • Comparison Function Spans on Row Direction
     The comparison function will be placed into the column total cell of the crosstab.
   • Comparison Function Spans on Column Direction
     The comparison function will be placed into the row total cell of the crosstab.
5. Numbers that form the calculation of the comparison function are determined by the Break by and Refer to drop-down lists.

   Items in the Break By drop-down list vary with the position of the comparison function. It specifies the first parameter (aggregate or subtotal) of the comparison function.

   All available items are displayed in the Refer to drop-down list according to what you have selected from the Break By drop-down list. These items are outer group subtotal and grand total. Select one as the other parameter of the comparison function.

6. Click OK and you can see that a new field is added into the Summaries box. Set the display name for the field in the Label column as required.
7. Repeat the above steps to define more comparison functions.
8. When done, click Finish in the Crosstab Wizard to apply the settings.
9. View the report. You will get the values of the comparison function.

Example of using the comparison function

Assume that you have created a crosstab on the query WorldWideSales in the catalog file SampleReports.cat as follows: added Product Type Name and Category in the Products table as the column fields, Country and State in the Customers table as the row fields, Quantity in the Orders Detail table as the aggregate field and specified Sum as the aggregate function, applied a filter "Country = Canada OR Country = France", set the crosstab to be Vertical Layout (Number of Rows: 1), and applied the style Classic. With these settings, the crosstab shows information about product sales volume in each state of Canada and France as follows:

Now, you want to define a comparison function in the crosstab to show the percentage of each state's sales volume to the grand total. To do this:

1. Right-click the crosstab and click Crosstab Wizard on the shortcut menu.
2. In the Display screen of the Crosstab Wizard, select Quantity in the Summaries box, then click the Comparison Function button.
3. In the Comparison Function dialog, select Percentage from the Function drop-down list, check Comparison Function Spans on Row Direction, specify Product Type Name as the break by field and choose Grand Total from the Refer to list.
4. Click OK in the Comparison Function dialog to return to the Crosstab Wizard, then click Finish in the wizard to accept the settings.
5. In the Report Inspector, modify the value of the Format property of the fields corresponding to the percentage to #,###.##% (the fields are represented as QUANTITY9, QUANTITY10 and QUANTITY11 respectively in the Report Inspector).
6. View the crosstab again and you will find that a percentage is added to the right of each state's sales volume.