Overview
Application Scenario
You can perform both horizontal accumulation and vertical accumulation (like level-by-level accumulation and cross-level accumulation) in cross tables.
For example, calculate the accumulation of monthly order amount for each employee, as shown in the following figure.
Implementation Method
Perform the accumulation through the formula B3 + C3[;B2:-1].
Example
Data Preparation
Create a general report and add the dataset named ds1. The SQL statement is as follows:
SELECT Employee ID, STRFTIME('%m', Order Date) as Month, Amount Due from orders where STRFTIME('%Y', Order Date) = '2011'
Table Design
1. Create a cross table as shown in the following figure, drag the fields from ds1 into cells A3, B3, and B3 respectively, and calculate the accumulation amount through cell C3.
Enter the formula =B3 + C3[;B2:-1] for cell C3. The following table describes the formula.
| Formula | Description |
|---|---|
C3[;B2:-1] | Represents the value of cell C3 corresponding to the previous cell (last year) expanded horizontally from the last parent cell B2. |
B3 + C3[;B2:-1] | Represents the accumulative amount of the current year amount plus the previous year amount. |
Effect Preview
Save and preview the template. The preview effect is shown in the following figure.
The template can be previewed on the APP/HTML5 terminal, as shown in the following figure.
Template Download
For details, you can click to download Cross Table Accumulation.cpt.
