Collapsible Tree by Condition Attribute

Problem

The report with an existing dynamic collapsible tree can only take effect in Data Analysis Preview. How can you use the collapsible tree in other preview modes? The following figure shows the effect.

Solution

You can set Dynamic Parameter in Hyperlink and Row Height in Condition Attribute.

The ID of the group to be clicked will be stored in the dynamic parameter. The row will be displayed or hidden according to whether the group number exists in the parameter.

Data Preparation

Create a dataset ds1 with the database query statement SELECT * FROM Sales_Volume.

Report Design

Design the table style, as shown in the following figure.

The following table shows the specific cell settings.

Dynamic Parameter Setting

Select cell A1, click Hyperlink on the right attribute panel, click +, and select Dynamic Parameter from the drop-down list. Name the parameter a, select Formula in Value, and enter the formula IF(LEN($a) = 0, &A1, if(inarray(&A1, split($a, ",")) > 0, joinarray(GREPARRAY(split($a, ","), item != &A1), ","), $a + "," + &A1)).

The whole formula takes effect as follows: During initialization, &A1 (namely ID of the group to be clicked) will be assigned to the dynamic parameter a. After you click a certain group, the position of the group ID in a will be returned, and the group ID will be filtered out from a. When you click the same group, 0 will be returned since the group IDr is not in a. You can set the row height in Condition Attribute to expand or collapse the tree based on whether the returned value is 0.

The following table explains each part of the formula.

The following figure shows the specific procedures.

Condition Attribute Setting

Select cell B2, click Condition Attribute on the right attribute panel, add two condition attributes, and rename the two condition attributes Expand and Collapse, as shown in the following figure.

With the Expand attribute, when the formula condition inarray(&A1, split($a, ",")) > 0 (namely when the ID of the group clicked is in the dynamic parameter a ) is met, the row height is 5 mm, as shown in the following figure.

With the Collapse attribute, when the formula condition inarray(&A1, split($a, ",")) = 0 (namely when the ID of the group clicked is not in the dynamic parameter a), the row height is 0 mm, as shown in the following figure.

Effect Display

PC

Save the report and click Pagination Preview. The following figure shows the effect.

Mobile Terminal

The report can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.

One-Click Collapsing/Expanding

1. If you want to collapse/expand the tree by one-click based on example one, insert a row before the first row, enter Expand by One-Click in cell A1, and enter Collapse by One-Click in cell B1, as shown in the following figure.

2. Set a dynamic parameter named a for cell A1 and enter joinarray(range(count(A2)), ",") in Value. Set a dynamic parameter named a for cell B1 and leave the parameter value empty, as shown in the following figure.

Data Preparation

Create a built-in dataset, as shown in the following figure.

Table Design

1. Design the table, as shown in the following figure.

2. Set Expansion Direction and Left Parent Cell for all cells, as shown in the following table.

Dynamic Parameter Adding

Add two hyperlinks of the dynamic parameter type to cell B2.

1. pre: if(find(C2, concatenate($pre, $cur)) > 0, replace(concatenate($pre, $cur), C2, ""), concatenate($pre, $cur))

 The function set for the pre parameter above is used to retrieve the value obtained from the last click. (However, if you have clicked once before, the function is used to remove the clicked status.)

2. cur: if(find(C2, concatenate($pre, $cur)) > 0, 1, if(C2 = $cur, 1, C2))

The function set for the cur parameter above is used to retrieve the value obtained from the current click. 

The following figure shows the setting.

Condition Attribute Adding

Add two condition attributes to cell A3.

1. Condition Attribute 1: Set Row Height to 0, and enter the formula reverse(or($$$ = $cur, find($$$, $pre) > 0)). This formula is used to hide the row if the current value is not the result of the current or previous click.

2. Condition Attribute 2: Set Column Width to 0.

The following figure shows the setting.

Arrow Icon Adding

Find two small icons, as shown in the following figure.

Add two condition attributes to cell B2.

Condition Attribute 1: Select the downward arrow image in Background, and enter the formula reverse(or(C2 = $cur, find(C2, $pre) > 0)).

Condition Attribute 2: Select the upward arrow image in Background, and enter the formula or(C2 = $cur, find(C2, $pre) > 0).

Effect Display

PC

Save the report and click Pagination Preview. The following figure shows the effect.

Mobile Terminal

Save the report and click Mobile Preview. The report can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.

1. Example One

For details, you can download the template Collapsible Tree by Condition Attribute.cpt.

2. Example Two

For details, you can download the template Two-Level Collapsible Menu by Dynamic Parameter.cpt.

Additionally, the following lists a template with a three-level collapsible menu. The three-level collapsible menu has the same principle as but the more complex expansion of parent cells than the two-level collapsible menu.

For details, you can download the template Three-Level Collapsible Menu by Dynamic Parameter.cpt.

1. Example one uses the dataset from the database, and example two uses the built-in dataset. To use an Oracle dataset, you can use select distinct Major category,Minor category from Table name, and perform a left join on the same table to obtain the required value data. Then, you can place the data in the desired location and may need to configure settings (for example, filtering) to complete the overall table.

2. Due to the use of dynamic parameters in both methods, the report with a large amount of data will be paginated automatically and redirected to the first page when you click the collapsing or expanding icon. To avoid this situation, you can display data without pagination. For details, see Data Display Without Pagination.

3. In example two, whether the row will be hidden or displayed is determined by category names, and data of the same category name is grouped together. Therefore, you need to pay attention to distinguishing the category names.