Overview
Version
Report Server Version | Functional Change |
11.0.4 | Added the ACCSUM function. |
11.0.7 | Adapted the ACCSUM function to the chart module. |
Application Scenario
Layer-by-layer accumulation: In a group table, the data of each group is cumulatively summed from the first layer to the current layer separately.
Cross-layer accumulation: In a group table, the data of each group is not cumulatively summed by group, but summed from top to bottom across all groups.
For example, the following figure shows layer-by-layer accumulation and cross-layer accumulation on the payable amount of different order months in the 2008 and 2009 grouped data (two separate groups).
Function Description
The ACCSUM function, which supports calculating cumulative values based on different groupings, is added in version 11.0.4.
Note:Function Explanation
(1) Overview
Syntax | ACCSUM(number,range) | Accumulating data based on different groupings |
Parameter 1 | number | Range of data to be calculated |
Parameter 2 | range | Grouping basis for accumulation, which can be empty |
(2) Example
Cell A2 contains the year; cell B2 contains the month; cell C2 contains the payable amount; Expansion Direction of cells A2 and B2 are both set to Vertical; their parent cells are set to Default.
ACCSUM(C2,A2): indicates accumulating PAID (payable amount) by year, that is, layer-by-layer accumulation.
ACCSUM(C2): indicates accumulating all data of PAID, that is, cross-layer accumulation.
Procedure
Template Creation
Create a general report, as shown in the following figure.
Data Preparation
Create a database query ds1, and enter the SQL statement:
SELECT STRFTIME('%Y',SIGNDATE) AS Year,STRFTIME('%m',SIGNDATE) AS Month,PAID
FROM ORDERS_old
WHERE STRFTIME('%Y',SIGNDATE) IN ('2008','2009')
This indicates filtering the year 2008 or 2009 from the ORDERS_old table (extracting the year part from the SIGNDATE field, and naming it as Year), month (extracting the month part from the SIGNDATE field, and naming it Month), and payable amount (the field PAID), as shown in the following figure.
Report Design
(1) Enter text in cells A1 to C1 and drag the corresponding data columns to cells A2 to C2, as shown in the following figure.
(2) For cell C2, set Format to Currency, select ¤#0.00 from the drop-down list box, and set Data Setting to Summary and Sum, as shown in the following figure.
Insertion of the Hierarchical Coordinate Function
(1) Enter text in cells D1 and E1, insert a formula in cell D1, and insert a formula in cell E1, as shown in the following figure.
(2) The following table explains the formulas.
Formula | Description |
ACCSUM(C2,A2) | Indicates accumulating PAID by year, that is, layer-by-layer accumulation. |
ACCSUM(C2) | Indicates accumulating all data of PAID, that is, cross-layer accumulation. |
Format Setting
Set Format of cells D2 and E2 to Currency, and select ¤#0.00 from the drop-down list, as shown in the following figure.
Effect Display
(1) PC
Save the report and click Pagination Preview. The following figure shows the preview effect.
(2) Mobile terminal
The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.
Template Download
Download the completed template by clicking ACCSUM.cpt.
Previous：SORT — Ranking
