I. Overview
For online video learning, please view: Hierarchical coordinates.
1. Application scenarios
After learning hierarchical coordinates, you can combine level coordinates with formula functions to perform dynamic grid operations, such as comparison, proportion, ring ratio, layer-by-layer accumulation, cross-layer accumulation, conditional summary, etc.
2. Function introduction
Traditional report tools generally only provide calculations between grids within the same group and set calculations for a certain group (or the whole group). There are several deficiencies in the following situations:
Inter-row operations, such as: quoting the previous row of data, the typical application is to do cumulative ratio and ring ratio.
Cross-group calculations, such as: the same period, etc., cross-groups are powerless.
Set operations only provide individual fixed functions, such as: taking the first place, calculating the cumulative value, etc.
It is not possible to combine general set operations, such as: taking second place, calculating cumulative products, etc.
Certain operations with conditions, such as: counting the number of data in the same group that is larger than the current number, that is, data ranking, etc.
FineReport uses hierarchical coordinates to express the position of the extended cell, which solves the above dynamic inter-cell calculation problem well.
This article uses examples to introduce how to use hierarchical coordinates to write calculation expressions to perform dynamic grid operations.
II. Example1
1. Expected effect
Expected effect as shown below:
| Field name | Formula |
|---|---|
| Comparison | Comparison = paid in the current period -paid in January Compare each value expanded by the cell with the first value. In the example, it is the difference value. |
| Percentage | Proportion = paid in the current period/paid in all months Calculate the proportion of each value expanded by the cell in the total. |
| Chain ratio | Calculate the ratio of each row of data to the previous row of data. Chain Ratio (Development Speed) = paid in the Current Period/paid in the Last Period |
2. Data preparation
Create a new built-in dataset, as shown in the figure below:
3. Report design
Follow the instructions in the table to design the report.
1) In cells A1~E1, write the field title name in sequence: Month, Paid, Comparison, Percentage, Chain ratio. Select cells A1~E1 and set the predefined style of the heading to Head type, as shown in the figure below:
2) Drag the data column "Month" in the dataset into cell A2, drag the data column "Paid" in the dataset into cell B2, and check the cell in "Cell Element> Data Settings",set grid data is "Summary>Sum", as shown in the figure below:
3) Write the formula B2-B2[A2:1] in cell C2 to calculate the expanded data of cell B2, and the difference between each row and the first row, as shown in the following figure:
Formula description:
For details of formula explanation, please refer to: Hierarchical Coordinate
| Formula | Description |
|---|---|
| B2 | Paid in the current period |
| B2[A2:1] | The paid in January, that is, the value in cell B2 corresponding to the first value in column A2 is returned |
| B2 - B2[A2:1] | Paid for the current period - paid for January |
4) Write the formula B2 / SUM(B2[!0]) in cell D2, calculate the expanded data of cell B2, and the proportion of each row in the total value, as shown in the following figure:
Formula description:
| Formula | Description |
|---|---|
| B2 | Paid in the current period |
| B2[!0] | Return all the values expanded by B2 For details, see: Common Formulas for Hierarchical Coordinates |
| SUM(B2[!0]) | Sum all the returned B2 values, which is the total amount due for all months |
| B2 / SUM(B2[!0]) | Calculate the proportion of the monthly paid to the total amount |
5) Write the formula IF(&A2> 1, B2 / B2[A2:-1], 0) in cell E2 to calculate the expanded data of cell B2, and the ratio of each row to the previous row, as shown in the figure below:
Formula description:
| Formula | Description |
|---|---|
| &A2 | Get the position corresponding to each value of cell A2 after expansion |
| B2 / B2[A2:-1] | B2[A2:-1]: Returns the amount due for the previous month B2 / B2[A2:-1]: paid this month/paid last month |
| IF(&A2 > 1, B2 / B2[A2:-1], 0) | If the serial number is not 1, that is, the first cell, then use, if it is the first cell, output 0 |
4. Preview
See section II.1 of this article for details.
III. Example2
1. Expected effect
Effect as shown below:
| Field name | Formula |
|---|---|
| Accumulate layer by layer | Group by year and calculate the cumulative amount payable in each month of the year In the grouping report, each group of data is accumulated row by row. |
| Cross-layer accumulation | Calculate the cumulative amount of all years and months payable In the grouping report, all data are accumulated row by row, and the accumulated results of the previous group will continue to accumulate when crossing groups. |
| Condition summary | Summarize eligible data |
2. Data preparation
Create a new built-in dataset, as shown in the figure below:
3. Report design
Follow the instructions in the table to design the report.
| Cell | Operation |
|---|---|
| A1~E1 | Write the field title name in the cell in turn: Year, Month, Paid, Accumulated layer by layer, Cross-layer accumulation. Select cells A1~E1 and set the predefined style of the heading to Head type |
| A2 | Drag the data column "Year" in the dataset into the cell |
| B2 | Drag the data column "Month" in the data set into the cell |
| C2 | Drag the data column "Paid" in the dataset into the cell, set the cell data in the Cell Element>Data Setting>Summary>Sum |
| D2 | Write the formula C2 + D2[B2:-1], the expanded data of cell C2 is accumulated row by row by year |
| E2 | Write the formula IF(&B2> 1, C2 + E2[B2:-1], C2 + E2[A2:-1,B2:!-1]), the expanded data of cell C2 is accumulated row by row, when it crosses years Then the cumulative results of the previous year continue to accumulate, where B2:!-1 means to find the first row from the bottom of column B2 of the group, if it is B2:!-2, it is the second row from the bottom |
| A3~C3 | Combine cells A3~C3 and write the field title name: The number of months in which the payable amount is greater than 2500. Set the left parent cell to A2, and the predefined style of the heading to Head type |
| D3~E3 | Combine cells D3~E3 and write the formula COUNT(C2[!0]{A2=$A2 && C2>2500}) to count the number of months where the paid is greater than 2500 |
Formula description:
1) Accumulation layer by layer: C2 + D2[B2:-1]
| Formula | Description |
|---|---|
| D2[B2:-1] | Each time it returns to the previous value of the accumulated column layer by layer in the current year For example: January 2008 returns 0; February 2008 returns the value of D2 corresponding to January 2206.00. |
| C2 + D2[B2:-1] | The paid in the current year and month + the value of the previous cumulative column cell value |
2) Cross-layer accumulation: IF(&B2> 1, C2 + E2[B2:-1], C2 + E2[A2:-1,B2:!-1])
| Formula | Description |
|---|---|
| &B2 | Get the position corresponding to each value of cell B2 after expansion Note: The position of B2 is accumulated from the beginning according to the year of the left parent For example: January of 2009 will return 1; January of 2008 will return 1 For details, see: Common Formulas for Hierarchical Coordinates |
| E2[B2:-1] | Each time it returns to the previous value of the accumulated column layer by layer in the current year For example: January 2008 returns 0; February 2008 returns the value of E2 corresponding to January 1458.00 For details, see: Hierarchical coordinates |
| E2[A2:-1,B2:!-1] | For the data of January of each year, return the cross-layer cumulative data of the last month of the previous year For example: the cross-layer cumulative data in January 2009 returns the cross-layer cumulative data in April 2008, which is 10717.00 |
| IF(&B2 > 1, C2 + E2[B2:-1], C2 + E2[A2:-1,B2:!-1]) | If it is not the first month of the year, it will return the previous value in the layer-by-layer cumulative column of the current year + the paid in the current month. If it is the first month of each year, return the cross-layer cumulative data of the last month of the previous year + the current month paid |
3) Condition summary: COUNT(C2[!0]{A2=$A2 && C2>2500})
For details of the formula explanation, please refer to: Common formulas for level coordinates
| Formula | Description |
|---|---|
| A2=$A2 | $A2 Get the value at the corresponding position of cell A2 after expansion Then determine whether A2 is equal to the value expanded by A2 That is to judge whether it is in the same year, because the demand is based on annual statistics |
| A2=$A2 && C2>2500 | At the same time, within the same year and the monthly payable amount is greater than 2500 |
| C2[!0]{A2=$A2 && C2>2500} | Take the amount that is met at the same time in the same year and the monthly payable amount is greater than 2500 |
| COUNT(C2[!0]{A2=$A2 && C2>2500}) | Count the returned paid |
4. Effect preview
See section III.1 of this article for details.
IV. Completed templates
See the completed template:%FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\BasicApplication\Hierarchical_coordinates1.cpt
See the completed template:%FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\BasicApplication\Hierarchical_coordinates2.cpt
Click to download：
