I. Overview
1) FineReport cells are expansible, so values in some reports cannot be calculated and fetched before expanded cells have been located. To execute such calculation, hierarchical coordinates can be used in the formula to locate these cells.
2) Hierarchical coordinates consist of two categories:
Relative coordinates
Absolute coordinates
This article will introduce the basic formats and usage scenarios of the two categories of hierarchical coordinates.
3) When using hierarchical coordinates, you may set conditions to filter cells.
II. Syntax of relative and absolute coordinates
1. Relative coordinates
1) Basic format
First identify the target cell to be located. Conditions in square brackets identify shifts in both vertical and horizontal directions. The left parent cells (and the target cell itself) identify the shift in the vertical direction, while the up parent cells (and the target cell itself) identify the shift in the horizontal direction.
Format of shift: a parent cell corresponds to a shift, separated by a colon. If multiple left parent cells are used to identify the shift in the vertical direction, they should be separated by commas; if multiple up parent cells are used to identify the shift in the horizontal direction, they should also be separated by commas.
The shift in the vertical direction and that in the horizontal direction are separated by a semicolon, with the shift in the vertical direction on the left and the other on the right. If there is only shift in the vertical direction, the semicolon can be omitted; if there is only shift in the horizontal direction, then the semicolon cannot be omitted.
The shifts in relative coordinates must be preceded by “+” or “-“, which indicates downward (rightward) or upward (leftward) shift.
2) Example of formulas: identify the shift in the vertical direction
Suppose data columns in A1, B1 and C1 expand vertically, A1 is the parent cell of B1 and B1 is the parent cell of C1. Cells in which the following formulas are input are located in Row 1, and the left parent cell is C1.
| Formula | Return value |
|---|---|
A1[A1:+1] | The first downward value expanded vertically from A1 |
A1[A1:-1] | The first upward value expanded vertically from A1 |
C1[B1:-1] | The value of C1 corresponding to the first upward value expanded vertically from B1 |
3) Example of formulas: identify the shift in the horizontal direction
Suppose data columns in A1, A2 and A3 expand horizontally, A1 is the parent cell of A2 and A2 is the parent cell of A3. Cells in which the following formulas are input are located in Column A, and the up parent cell is A3.
Formula | Return value |
|---|---|
A1[;A1:+1] | The first posterior value expanded horizontally from A1 |
A1[;A1:-1] | The first preceding value expanded horizontally from A1 |
A3[;A2:-1] | The value of A3 corresponding to the first preceding value expanded horizontally from A2 |
2. Absolute coordinates
1) Basic format
Absolute coordinates are in similar format to relative coordinates and the difference lies in that the value after a colon represents an absolute position, rather than a relative shift.
If an absolute position is positive, it indicates one among the first, and the positive should not be preceded by + (recommended not to do so, so as to be distinguished from relative coordinates); if an absolute position is negative, the negative should be preceded by !, indicating one among the last.
2) Example of formulas: identify the shift in the vertical direction
Suppose data columns in A1, B1 and C1 expand vertically, A1 is the parent cell of B1 and B1 is the parent cell of C1.
Formula | Return value |
|---|---|
A1[A1:1] | The first value expanded vertically from A1 |
A1[A1:!-1] | The last value expanded vertically from A1 |
C[B1:1] | The value of C1 corresponding to the first value expanded vertically from B1 |
C1[A1?:2,B1:3] | The value of C1 corresponding to the third value expanded from B1 corresponding to the second value expanded vertically from A1 |
3) Example of formulas: identify the location in the horizontal direction
Suppose data columns in A1, A2 and A3 expand horizontally, A1 is the parent cell of A2 and A2 is the parent cell of A3.
Formula | Return value |
|---|---|
A1[;A1:1] | The first value expanded horizontally from A1 |
A1[;A1:!-1] | The last value expanded horizontally from A1 |
A3[;A2:1] | The value of A3 corresponding to the first value expanded horizontally from A2 |
A3[;A1:2,A2:3] | The value of A3 corresponding to the third value expanded from A2 corresponding to the second value expanded horizontally from A1 |
3. Other common usages
Scenario | Formula | Return value |
|---|---|---|
Absolute coordinates | A1[!0] | All values expanded from A1 The type of the return value is array |
Absolute coordinates | A1[!0]{A1!=3} | All values expanded from A1, excluding those equivalent to 3 The type of the return value is array |
Relative coordinates | $A1 | The value in the corresponding position of A1 after it expands, which follows the expansion direction of A1 |
III. Locate vertically expanded cells
1. Create a new dataset [ds1]
1) Create a new DB Query,
2) Input the following SQL statement:
SELECT * FROM SProduct limit 10
2. Use relative coordinates
1) Drag data columns into cells. Drag SupplierID in [ds1] into A2 and B2 respectively and enter headers in A1 and B1.
2) Insert formulas into C2~F2 and input text in C1~F1, with such text used to indicate the formulas in C2~F2
Cell | Formula inserted | Expected result |
|---|---|---|
C2 | A2[A2:-1] | The value of the previous A2, or the previous Supplier. |
D2 | B2[B2:-1] | The value of the previous B2, or the previous Product. Here, each group is calculated separately (values sharing A2 fall within a group), so the first value in each group is null. |
E2 | if(&B2>1,B2[B2:-1],B2[A2:-1,B2:!-1]) | If &B2>1, or numbering in the group does not begin from 1, then execute B2[B2:-1] to fetch the previous Product; or otherwise execute B2[A2:-1,B2:!-1] to fetch the last Product of the previous Supplier (here, relative and absolute coordinates are combined). |
F2 | A2[A2:+1] | The value of the next A2, or the next Supplier. |
3. Use absolute coordinates
Insert formulas into B4~B7 and input text in A4~A7, with such text used to indicate the formulas in B4~B7
Cell | Formula inserted | Expected result |
|---|---|---|
B4 | A2[A2:2] | The second value expanded from A2, or the second Supplier |
B5 | B2[A2:2,B2:1] | The first Product in the second Supplier |
B6 | B2[!0] | All Products |
B7 | B2[!0]{A2!=2} | All Products, but the corresponding Supplier is not the second one |
4. Data Analysis preview
Preview the result in Sheet1 and you can further understand the meanings of these formulas by comparing the expected results in the steps above.
IV. Locate horizontally expansible cells
1. Create a new dataset [ds2]
1) Create a new DB Query.
2) Drag the table STSCORE into the DB Query.
2. Design a report
Create a new sheet. In Sheet2, drag [COURSE] and [GRAGE] in [ds2] into B1 and B2 respectively, and change expansion direction to horizontal. Input headers in A1 and A2.
3. Use relative coordinates
Insert formulas into B3 and B4, and input text in A3 and A4, with such text used to indicate the formulas in B3 and B4
Cell | Formula inserted | Expected result |
|---|---|---|
B3 | B2[;B1:-1] | To fetch GRADE of the previous COURSE |
B4 | B2[;B2:+1] | To fetch GRADE of the next COURSE |
4. Use absolute coordinates
Insert a formula into C7 and input text in C6, with such text used to indicate the formula in C7
Cell | Formula inserted | Expected result |
|---|---|---|
C7 | B2[;B1:!-1] | GRADE of the last COURSE fetched |
5. Data Analysis preview
Preview the result in Sheet2 and you can further understand the meanings of these formulas by comparing the expected results in the steps above.
V. Locate bi-directionally expanded cells
1. Design a report
Create a new sheet. In Sheet3:
Drag [CLASSNO] in [ds2] into B2 and set expansion direction as vertical.
Drag [COURSE] in [ds2] into C1 and set expansion direction as horizontal
Drag [GRADE] in [ds2] into C2 and set [Data Setting] as [Summary]>[Sum]
2. Use absolute coordinates
Insert a formula into E6 and input text in E5, with such text used to indicate the formula in E6
Cell | Formula inserted | Expected result |
|---|---|---|
E6 | C2[B2:!-2;C1:2] | To fetch the sum of Math scores in Grade of the second Course of the penultimate class, namely Class3 |
3. Data Analysis preview
Preview the result in Sheet3 and you can further understand the meanings of these formulas by comparing the expected results in the steps above.
Download the template
Hierarchical Cell Indexing.cpt
Previous：Learning Path of Hierarchical Coordinate
