I. Overview
1. Application scenarios
When the report is previewed, click the column heading to sort the column up and down, and the column heading is derived from the horizontal expansion of the field. As shown below:
2. Solutions
Get all the values of the click column, and then set the expanded sort in the cell properties of the salesperson, and the salesperson will sort according to all the values in the click column.
II. Example
1. Data preparation
Create a new general report, create a new database query ds1, and enter the SQL statement: SELECT * FROM Sales_Volume.
2. Design report
The design table style is shown in the figure below:
The cell attribute settings are shown in the following table:
Note: When the field is set as a summary, the extended attribute will automatically become non-expandable. You can check the expansion direction of the cell through the gray arrow in the upper left corner of the cell.
| Cell | Dataset | Data Column | Attributes |
|---|---|---|---|
| A2 | ds1 | Salesperson | Expansion Direction: vertical, center, the rest are default |
| B1 | ds1 | Product | Expansion Direction: horizental, center, the rest are default |
| B2 | ds1 | Sales_volume | No Expansion Data Setting: summary-sum, center, the rest are default |
3. Add dynamic parameters
Add a Hyperlink>Dynamic Parameter to cell B1, rename it to sort, and add two parameters: order and index. The parameter value of order is the formula: if($order = 1, -1, 1), and the parameter value of index is the formula: &B1, as shown in the figure below:
Note: The index parameter value is &B1, that is, the index value is set to the number of the clicked cell when clicked.
4. Set the sort after expansion
Select cell A2, click Cell Attributes> Expand in the right property panel, set Expandsort to Ascending, enter the formula: if(isnull($index), A2, if($order = 1, EVAL( "B2[;B1:" + $index + "]"), EVAL("-B2[;B1:" + $index + "]"))).
The meaning of this formula is: when the order is 1, the clicked column values obtained by EVAL("B2[;B1:" + $index + "]") will be sorted in ascending order; when the order is -1, the obtained values will be obtained The value of the click column is treated as a negative number, and then sorted in ascending order according to the negative number.
The formula description is as follows:
| Formula | Description |
|---|---|
| isnull($index) | Determine whether $index is an empty string. $index is empty during initialization, and is assigned the value &B1 after clicking the column header, which is the number of the clicked cell. Note: When the parameter is empty, sort by cell A2, which can improve the performance of sorting when the title is clicked for the first time. |
| "B2[;B1:" + $index + "]" | Return the value of cell B2 corresponding to the $index value expanded from cell B1. For details, please refer to: Relative and Absolute Coordinates. |
| EVAL() | Return a string expression. |
The steps are shown in the figure below:
5. Effect preview
Save the template, click Pagination Preview, the effect on the PC terminal is as follows:
III. Template download
The completed template can be found in:
%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\FeaturesApplication\Sorting on multiple
fields after horizontal expansion.cpt
Click to download the template: Sorting on multiple fields after horizontal expansion.cpt
