Skip to main content

Using Crosstab Formulas

  • Updated

Previous Topic  Next Topic

Using Crosstab Formulas

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

Below is a list of the sections covered in this topic:

Applying Custom Aggregate Functions

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

  1. Open the catalog file SampleReports.cat in <install_root>\Demo\Reports\SampleReports.
  2. Select File > New > Page Report.
  3. In the Select Component for Page Report dialog, select Crosstab and select OK. The Crosstab Wizard appears.
  4. In the Data screen, select the query WorldWideSales in Data Source 1 of the catalog.
  5. In the Display screen, add Country as the row field and Region as the column field.
  6. Select <New Crosstab Formula...> in the Crosstab Formulas node in the Resources box.

    Create Crosstab wizard

  7. In the Enter Crosstab Formula Name dialog, input CustomAggregate and select OK. The Crosstab Formula Editor is displayed.

    Crosstab Formula Editor window

  8. Define the formula as follows: 
    currency ctsv1 = @(Sum(@Price));
currency ctsv2 = @(@Country:ALL,@Region:ALL,Sum(@Price));
if(ctsv1/ctsv2 >0.005)
return ToText(ctsv1)
else
    return "N/A"
  9. Save the crosstab formula and add it to the Summaries box as the aggregate field.
  10. Switch to the Style screen and select Neutral as the report style from the Style list.
  11. Select Finish to create the crosstab and preview it. The crosstab is 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.

    Crosstab in Page Report

Managing Crosstab Formulas

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

  • Creating crosstab formulas
    To create a crosstab formula, select <New Crosstab Formula...> in the Crosstab Formulas node, enter a name for the crosstab formula in the Enter Crosstab Formula Name dialog and select OK, then in the Crosstab Formula Editor, compose the formula as required. You can then insert the crosstab formula as aggregate field to the crosstab or use it to control the properties of objects in the crosstab.
  • Editing a crosstab formula
    To edit a crosstab formula, select it from the Crosstab Formulas node, then right-click the crosstab formula and select Edit Formula on the shortcut menu. In the Crosstab Formula Editor, edit the crosstab formula as required.
  • Deleting a crosstab formula
    To delete a crosstab formula, right-click the crosstab formula and select Delete from the shortcut menu.
  • Renaming a crosstab formula
    To rename a crosstab formula, right-click the crosstab formula and select Rename from the shortcut menu. Input a new name in the Enter Crosstab Formula Name dialog and select OK to confirm the change.
  • Viewing properties of acrosstab formula
    To view the properties of a crosstab formula, right-click the crosstab formula and select Properties to show the Properties table.

Notes:

  • Crosstab formulas can only be used as aggregate fields in a crosstab.
  • Global variables are not supported in crosstab formulas.
  • Crosstab formulas cannot reference detail fields and record level pass one formulas, except for referencing them in simple Sum expression.
  • In crosstab formulas, the Array functions cannot be used as array_function (field_variable, groupby). Here array_function refers to any Array function defined in Logi JReport. For example, Maximum(@dbfield, "group_field") and Average(@formula, @parameter) are not allowed in crosstab formulas.

Previous Topic  Next Topic

Related articles