Overview
Note: In 11.0.4 and later versions, users are advised to use the ACCSUM function to achieve cross-level accumulation.
Application Scenario
Layer-by-layer accumulation is to add the data of the previous level to that of each level in each group of the grouped report to obtain the cumulative result (separated by year) at this level.
For example, as shown in the following figure, level-by-level accumulation is performed on the data grouped by year (2010 and 2011).
Implementation Method
Relative hierarchical coordinates are introduced in the calculation formula.
Example
Data Preparation
Create a general report and create a data query named ds1. The SQL statement is as follows:
SELECT STRFTIME('%Y', Order Date) AS Year, STRFTIME('%m', Order Date) AS Month, Amount Due
FROM Order
WHERE STRFTIME('%Y',Order Date) IN ('2010','2011')
Report Design
Design the table as shown in the following figure. Drag the fields in ds1 to cells A2, B2, and C2. Perform layer-by-layer accumulation for cell D2.
Enter the condition summary formula C2 + D2[B2:-1] for cell D2. The following table describes the formula.
| Formula | Description |
|---|---|
D2[B2:-1] | Represents the value of cell D2 corresponding to the previous cell (last month) expanded from cell B2. |
C2 + D2[B2:-1] | Represents the cumulative amount of the previous month plus the amount of the current month in cell C2 within a year. |
Effect Preview
Save the template and click Pagination Preview. Then 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 Level-by-Level Accumulation.cpt.
Previous：Row Sequence Number
