Dynamic parameters realize multi-level drop-down folding menu

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

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:

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:

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

• 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.