# Using Crosstab Formulas

Crosstab formulas are a type of extended formulas used only in crosstabs that are created using 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:

- Crosstab Formula Syntax
- Using Crosstab Formulas to Apply Custom Aggregate Functions
- Managing Crosstab Formulas

## Crosstab Formula Syntax

To support more powerful and flexible calculation logic in crosstabs, crosstab formula extends the basic formula syntax in the following aspects:

- A crosstab formula can reference another crosstab formula within the same crosstab. The format is
`@@<Crosstab_Formula_Name>`

, for example,`@@CTF1`

, here CTF1 is a crosstab formula name. In this case, a crosstab formula can be written as`@(@@CTF1)`

. - A crosstab formula can reference summary expression which follows the syntax of custom aggregation expression. Below are some summary expression examples:
`@(Sum(@Sales))`

`@(@Country:"USA",@Year:CHILDREN, Sum(@Sales))`

`@(@Country:"USA",@Year:CHILDREN, @@CTF1)`

## Using Crosstab Formulas to Apply Custom Aggregate Functions

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

- Make sure SampleReports.cat is the currently open catalog file. If not select
**File**>**Open Catalog**to open it from`<install_root>\Demo\Reports\SampleReports`

. - Select
**File**>**New**>**Page Report**. - In the Select Component for Page Report dialog, select
**Crosstab**and select**OK**. - In the Data screen of the Crosstab Wizard, select the query
**WorldWideSales**in Data Source 1 of the catalog. - In the Display screen, add
**Country**as the row field and**Region**as the column field. - Select
**<New Crosstab Formula...>**in the Crosstab Formulas node in the Resources box. - In the Enter Crosstab Formula Name dialog, input
**CustomAgg**and select**OK**. The Crosstab Formula Editor is displayed. - 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" - Save the crosstab formula and add it to the Summaries box as the aggregate field. You can find the aggregate function of the aggregate field is displayed as Custom and cannot be edited.
- Switch to the Style screen and select
**Classic**as the report style from the Style box. - 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.

## 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 formula expression 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 a****crosstab formula**

To view the properties of a crosstab formula, right-click the crosstab formula and select**Properties**. The properties are then displayed in the Properties dialog.

**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 summary 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.

## Comments

0 comments

Please sign in to leave a comment.