I. Overview
1. Application scenarios
In report development, we will encounter the effect that the report needs to implement the collapsed menu that expands and collapses the row headers. This effect is generally achieved by using drop-down tree controls in analysis preview or data entry , but how to achieve it in pagination preview or dashboard?
expected result:
Applicable in pagination preview or dashboard
As the data grows and changes, the foldable menu changes automatically (reusable), so there is no need to write hard
The drop-down function is the same as the current commonly used drop-down tree button
2. Implementation ideas
Here we use dynamic parameters to achieve the desired folding effect:
Through the expansion of the parent cell to achieve the effect of all menu expansion
Use dynamic parameters to mark which rows are expanded or collapsed
Control line hiding and display through conditional fromatting
II. Example
1. Prepare data
Create a new built-in data set, as shown in the figure below:
2. Design table
Design the table, as shown in the figure below:
Set the expansion direction and left parent cell of each cell, as shown in the following table:
Cell | Expansion direction | Left paternity |
B1, C1 merged cells | Does not expand | default |
D1 | Does not expand | default |
A3 | Does not expand | C2 |
B2 | Vertical expansion | C2 |
C2 | Vertical expansion | default |
C3 | Vertical expansion | C2 |
D3 | Vertical expansion | default |
3. Add dynamic parameters
Add two hyperlinks of dynamic parameter types in cell B2:
pre: if(find(C2, concatenate($pre, $cur))> 0, replace(concatenate($pre, $cur), C2, ""), concatenate($pre, $cur))
The content of the pre parameter function above is to get the value obtained from the last click. (But if you have clicked once last time, remove the clicked state)
cur: if(find(C2, concatenate($pre, $cur))> 0, 1, if(C2 = $cur, 1, C2))
The content of the cur parameter function above is to obtain the value obtained by this click. For details of the concatenate function, please refer to: String Concatenate
As shown below:
4. Add conditional formattings
Add two conditional formattings to cell A3:
Condition formatting 1: Row height is 0: The formula is reverse(or($$$ = $cur, find($$$, $pre)> 0)) This formula is to determine if the current value is not this time or the previous The one clicked is hidden.
Conditional formatting 2: The column width is 0.
As shown below:
5. Add arrow icon
Find two small icons, as shown in the figure below:
Add two conditional formattings to cell B2:
Conditional formatting 1: Select the image with the background downward, the formula is: reverse(or(C2 = $cur, find(C2, $pre)> 0))
For details of the reverse function, please refer to: Logical Function
Condition formatting 2: The background is an upward picture, the formula is: or(C2 = $cur, find(C2, $pre)> 0)
6. Effect preview
1) PC side
Save the report, click "Pagination Preview", the effect is as shown in the figure below:
2) Mobile
Save the report, click "Mobile Preview", App and HTML5 are supported at the same time, the effect is as shown in the figure below:
III. Template download
The completed template can be found at: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\DynamicCol\Dynamic_parameters_to_achieve_secondary_drop-down_folding_menu.cpt
Click to download the template:Dynamic_parameters_to_achieve_secondary_drop-down_folding_menu.cpt
In addition, a three-level drop-down template is attached. The principle is the same as that of the second-level drop-down, except that the expansion of the parent grid may be slightly more complicated than just before.
The completed template can be found at: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\DynamicCol\ Dynamic_parameters_to_achieve_a_three-level_drop-down_folding_menu.cpt
Click to download the template: Dynamic_parameters_to_achieve_a_three-level_drop-down_folding_menu.cpt
IV. Matters needing attention
The above is implemented by the built-in data set. If you are using the Oracle data set, you can use select distinct large category, small category from table name , and then left join the same table, get the value data needed in it, and then place it where needed You may need to configure filtering, etc., and the overall table can be made.
If you need to click the drop-down and fold instead of the small triangle button, it is also possible to implement it directly on the text. Just change the C2 in the formula to the current value $$$, and then set the dynamic parameter B2 of this article on C2. Can.
Other style issues, such as left alignment, borders, and fonts, can be set by yourself.