I. Overview
1. Problem description
For grouped reports, there are various subtotals, totals, and lists. How to sort them layer by layer according to each grouping?
As shown in the figure below, first sort the Salesperson Subtotal, then sort the Product Type Subtotal, and finally sort the Product List.
2. Solution
The sorting of different values is aggregated according to the left parent cell, and the sorting can be achieved by setting the expansion on the corresponding left parent cell.
II. Example
1. Data preparation
Create a new general report, create a new database query ds1, and enter the SQL statement as: SELECT * FROM Sales_Volume WHERE Region='North China'. As shown below:
2. Report design
1) Table content
Drag the data column in ds1 into the table according to the following figure, enter the formula in cell D3: SUM(D2); enter the formula in cell D4: SUM(D3), as shown in the following figure:
2) Set the left parent cell
Set the left parent cell of cells B3 and D3 to B2; set the left parent cell of cells A4 and D4 to A2.
Take the setting of the left parent cell of B3 as an example, the steps are as shown in the figure below:
3. Set the sort after expansion
The specific settings are shown in the following table:
| Cell | Expandsort | Formula |
|---|---|---|
| A2 | Ascending | D4 |
| B2 | Ascending | D3 |
| C2 | Ascending | D2 |
Take cell A2 as an example, the steps are as shown in the figure below:
4. Effect preview
1) PC terminal
Save the report, click Pagination Preview, the effect is as shown in the figure below:
2) Mobile terminal
Both App and HTML5 are supported, and the effect is shown in the figure below:
III. Template download
The completed template can be found in: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\Sort by group summary.cpt
Click to download the template: Sort by group summary.cpt
