Report development instance-dynamic multi-level KPI drilling report (I), multi-level kpi

Report development instance-dynamic multi-level KPI drilling report (I), multi-level kpi

In report projects, there are sometimes dynamic hierarchical reports that require hierarchical drilling, making development more difficult. This document describes how to develop the KPI report for all levels of departments for the rundry computing report.

The initial status of the KPI report for all levels of departments is as follows:

The current node is the root node "Hebei Province". The report is required to display the KPI values summarized by the "city" of the next level node of the current node. KPIs are classified into general indicators and VIP indicators. There are four categories. If you click "Shijiazhuang" to drill down, you must be able to display the KPI summary indicators for the next level in Shijiazhuang, such:

Click "center" to drill down. You must be able to display the KPI summary indicators for the next level, and so on until the last level is displayed. For example:

The first four levels are fixed as "province, city, district, district, and sales department", while the back is dynamic "Architecture 4, architecture 5, architecture 6... architecture 13 "(the root node" Province "corresponds to" Architecture 0 ").

The report corresponds to two oracle database tables, tree (tree structure dimension table) and kpi (indicator fact table), such:

Tree table

Kpi table

The leaf node of the Tree table, which is associated with the kpi table through the id field. The difficulty of this report is: 1. Dynamic Multi-layer data and titles; 2. Association of tree-structured data with fact tables.

The first step of using the rundry set computing report is to write the Set Computing script tree. dfx to complete source data computing. The computing script is as follows:

A1: connect to the pre-configured oracle database.

A2: Create a sequence with the following content: "province, city, district/county, sales department, Architecture 4, architecture 5, architecture 6... architecture 13 ".

A3: Use the connectby statement provided by the oracle database to write SQL statements, and retrieve the IDs and names of all parent nodes with the specified id (node number) from the database. Id is a pre-defined grid parameter. If the input value is 104020, the A3 calculation result is:

A4: Add a field title for A3, which corresponds to the level in A2 in order. The result is:

A5: calculates the variable level, which is the length of the A3 sequence table, that is, the level Number "4" of the input node "104020" ("Province" is the first level ).

A6: Calculate the level name "Architecture 4" corresponding to the next level of the input node "104020" and assign the value to the variable xtitle.

A7: Write an SQL statement to extract all the leaf nodes of the input node "104020" from the tree table and split the sys_connect_by_path string to obtain the next level node of the input node "104020" corresponding to these leaf nodes. A temporary table leaf and kpi table association group are formed. To get the name of the next level node of the input node "104020", leaf also needs to be associated with the tree once. Note that if the input node number itself is a leaf node, the name in the result will be empty. The complete SQL statement is as follows:

with leaf as(SELECT tree.id id,REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(id,';'),'[^;]+',1,2) x FROM treewhereconnect_by_isleaf=1START WITH ID =?     CONNECT BY NOCYCLE PRIOR id = pid)select nvl(leaf.x,max(leaf.id))id,'"+xtitle+"' title,max(tree.name) name, sum(kpi.kpi1)kpi1,sum(kpi.kpi2) kpi2,sum(kpi.vipkpi1) vipkpi1,sum(kpi.vipkpi2) vipkpi2from leaf left join kpi on leaf.id = kpi.idleft join tree on leaf.x=tree.idgroup by leaf.x order by leaf.x

The calculation result is:

A8: Close the database connection.

A9: return two result sets A4 and A7 to the report.

Step 2: Define report parameters and set datasets in the report designer and call tree. dfx. For example:

Define the report parameter "id"

Define a set computing dataset (the parameter name "id" is the output parameter name of the Set Computing script, and the parameter value "id" is the report parameter.

Step 3: design a report, for example:

Column A is the left half of the report and is the parent node and itself of the input node (for example, "104020"). It is horizontally expanded. The A1 value is id, the title is displayed. A3 displays the name.

Column B is the middle part of the report. It is a subnode of the next level of the input node (for example, "104020") and is vertically expanded. The condition for setting the hidden column in the B3 lattice is value () = null. If the input node is a leaf node in this province, name = null, and column B is hidden and not displayed.

Column C to column F is the right half of the report. The kpi statistical value corresponding to ds2.name is displayed.

To implement the report format, cell A3 needs to set the left primary grid to B3.

To implement the drilling function, you must:

1. Define the hyperlink attribute of cell A3 as an expression: "/reportJsp/showReport. jsp? Rpx = r4.rpx & id = "+ ds1.ID. The hyperlink points to the current table, and the report parameter is the ds1.ID corresponding to the current column.

2. Define the hyperlink attribute of cell B3 as an expression: "/reportJsp/showReport. jsp? Rpx = r4.rpx & id = "+ ds2.ID. The hyperlink points to the current table, and the report parameter is the ds2.ID corresponding to the current row.

Step 4: publish and run the report. The actual hyperchain value is as follows:

From the above implementation steps, we can see that the most difficult is the complex SQL statement of cell A7 in tree. dfx. It should be said that the tree-like recursive query of the oracle database is rich, and it is difficult to implement the SQL statement in A7 using other databases (such as MySQL, PostgreSQL, and Greenplum. So what other solutions can these databases achieve the preceding complex tree structure computing?