Overview
Problem
In a cross report, data are expanded both vertically and horizontally. How can you define the hierarchical coordinates of data in this case?
l Relative hierarchical coordinates
l Absolute hierarchical coordinates
l Getting only one column or one row is also one type of absolute hierarchical coordinates.
Idea
In bidirectional expansion, whether writing relative hierarchical coordinates or absolute hierarchical coordinates, you should use the combination of vertical and horizontal coordinates and follow the principle of vertical coordinates first and horizontal coordinates next.
Hierarchical Coordinate Type | Formula | Calculation Result | Parameter Explanation |
Relative hierarchical coordinate | Cellx[Celly:-k ;Cellz:p] | Relative offset of column p -k | Cellx: The cell where the horizontal and vertical expansion cross. Celly: Vertically expanded cell, which is the left parent cell of Cellx Cellz: Horizontally extended cell, which is the up parent cell of Cellx k: number p: number |
Cellx[Celly:+k ;Cellz:p] | Relative offset of column p +k | ||
Cellx[Celly:p ;Cellz:-k] | Relative offset of row p -k | ||
Cellx[Celly:p ;Cellz:+k] | Relative offset of row p +k | ||
Absolute hierarchical coordinate | Cellx[Celly:k ;Cellz:p] | The value at the intersection of row k and column p. | |
Cellx[Celly:k ;Cellz:!-p] | The value at the intersection of column p from the right and row k. | ||
Cellx[Celly ;Cellz:k] | Obtain all values of column k. | ||
Cellx[Celly:k ;Cellz] | Obtain all values of row k. |
Creating a Sample Template
Create a cross report for the demonstration of defining bidirectionally expanded hierarchical coordinates.
Preparing Data
Create a dataset ds1 with SQL statement SELECT * FROM Sales_Volume.
Designing a Table
Drag the field Product to cell B1 and set its Expansion Direction to H.
Drag the field Salesperson field to cell A2 and set its Expansion Direction to V.
Drag the field Sales_Volume to cell B2, and set its Expansion Direction to No Expansion. Cell B2 is the bidirectionally expanded data of cell B1 and cell A2.
Demonstration
Click Pagination Preview.
Relative Hierarchical Coordinate
Fill in the formulae of relative hierarchical coordinates in the corresponding cells.
The actual calculation result is shown below.
Explanations of each hierarchical coordinate are shown below.
Cell | Formula | Explanation | Effect |
C2 | B2[A2:-1 ;B1:1] | ;B1:1: Horizontal absolute hierarchical coordinate, which limits to the first row. A2:-1: Vertical relative hierarchical coordinate, which achieves vertical negative offsetting. | The first column relatively offsets -1. |
D2 | B2[A2:+1 ;B1:2] | ;B1:2: Horizontal absolute hierarchical coordinate, which limits to the second column. A2:+1: Vertical relative hierarchical coordinate, which achieves vertical positive offsetting. | The second column relatively offsets +1. |
B3 | B2[A2:1 ;B1:-1] | A2:1: Vertical absolute hierarchical coordinate, which limits to the first row. ;B1:-1: Horizontal relative hierarchical coordinate, which achieves horizontal negative offsetting. | The first row relatively offsets -1. |
B4 | B2[A2:2 ;B1:+1] | A2:2: Vertical absolute hierarchical coordinate, which limits to the second row. ;B1:+1: Horizontal relative hierarchical coordinate, which achieves horizontal positive offsetting. | The second row relatively offsets +1. |
Absolute Hierarchical Coordinate
Fill in the formulae of absolute hierarchical coordinates in the corresponding cells. From cell B4 to cell B6, set Expansion Direction to No Expansion, and Left Parent Cell and Up Parent Cell to None.
The actual calculation result is shown below.
Explanations of each hierarchical coordinate are shown below.
Cell | Formula | Explanation | Effect |
B4 | B2[A2:4 ;B1:2] | A2:4: Vertical absolute hierarchical coordinate, which limits to the fourth row. ;B1:2: Horizontal absolute hierarchical coordinate, which limits to the second column. | Return the value at the intersection of the fourth row and second column. |
B5 | B2[A2:6 ;B1:4] | A2:6: Vertical absolute hierarchical coordinate, which limits to the sixth row. ;B1:4: Horizontal absolute hierarchical coordinate, which limits to the fourth column. | Return the value at the intersection of the sixth row and the fourth column. |
B6 | B2[A2:!-1 ;B1:!-1] | A2:!-1: Vertical absolute hierarchical coordinate, which limits to the last row. ;B1:!-1: Horizontal absolute hierarchical coordinate, which limits to the last column. | Return the value at the intersection of the last row and the last column. |
Getting One Column or One Row Only
Fill in the formulae of hierarchical coordinates in the corresponding cells.
The actual calculation result is shown below.
Explanations of the formulae are shown below.
Cell | Formula | Explanation | Effect |
C2 | B2[A2 ;B1:1] | A2: Return all vertically expanded values of cell A2. ;B1:1: Horizontal absolute hierarchical coordinate, which limits to the first column. | Return all values of the first column. |
B3 | B2[A2:1 ;B1] | A2:1: Vertical absolute hierarchical coordinate, which limits to the first row. ;B1: Return all horizontally expanded values of cell B1. | Return all values of the first row. |
Downloading the Template
You can see the completed templates under the directory:
%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Advanced\Coordinate\Cross Report Relative Hierarchical Coordinate.cpt
%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Advanced\Coordinate\Cross Report Absolute Hierarchical Coordinate.cpt
%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Advanced\Coordinate\Getting Values of a Row or a Column.cpt
Click to download the templates:
Cross Report Relative Hierarchical Coordinate.cpt
Previous：Hierarchical Cell Indexing
