Using crosstab formulas

Crosstab formula is a kind of extended formula used only in page report crosstabs. By using crosstab formulas, you can create custom aggregate functions in a crosstab to get the desired results. Crosstab formulas are private resources on crosstab level. They cannot be used beyond its crosstab.

Applying custom aggregate functions in a crosstab by creating crosstab formulas

Below example shows how to apply custom aggregate function in a crosstab simply by creating a crosstab formula:

  1. Open the catalog file JinfonetGourmetJava.cat in <install_root>\Demo\Reports\TutorialReports.
  2. Create a crosstab report with the query WorldWideSales stored in Data Source 1 of the catalog.
  3. In the Display screen of the Crosstab Wizard, add Customers_Country as the row field and Customers_Region as the column field.
  4. Click <Add Crosstab Formula...> in the Crosstab Formulas node in the Resources box.
  5. In the Input Crosstab Formula Name dialog, enter CustomAggregate and click OK. The Crosstab Formula Editor is displayed. Define the formula as follows: 
    currency ctsv1 = @(Sum(@Price));
    currency ctsv2 = @(@Customers_Country:ALL,@Customers_Region:ALL,Sum(@Price));
    if(ctsv1/ctsv2 >0.005)
    return ToText(ctsv1)
    else
    return "N/A"
  6. Save the crosstab formula and add it to the Summaries box as the aggregate field.
  7. Click Finish to create the crosstab and preview it. The crosstab will be shown somewhat as follows. You can see that in the aggregate cell, based on the formula expression, "N/A" is displayed if the price value equals to or is less than 5‰ of the grand total price $16,337.85, while the actual price is displayed if the price is more than 5‰ of the grand total price.

See also Crosstab Formula Editor window for detailed explanation about options in the window.

Managing crosstab formulas

You can manage the crosstab formulas of a crosstab in the Resource View panel as follows:

Notes: