Report development instance-dynamic multi-level KPI drilling report (II)

Report development instance-dynamic multi-level KPI drilling report (II)

InReport development instance-dynamic multi-level KPI drilling report (I)In, we talked about the scenario where dynamic hierarchical reports require hierarchical drilling, and introduced the implementation method of using oracle Complex SQL. Here, we still use this report to learn about the development method of "script + local file.

KPI reports for all levels of departmentsSee (I ). The difference is that the report data source changes from a database table to a local file: tree. B (tree structure dimension table) and kpi. B (indicator fact table). The data is as follows:

<喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KPHAgYWxpZ249 "center"> Tree file data

Kpi file data

The leaf node of the Tree, which is associated with the kpi table through the id field. Every ID in the KPI file adds a kpi record every day.

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 input parameter is the id of the specified node number. The computing script is as follows:

A1: Create a sequence with the title of "province, city, district, district, sales department, Architecture 4, architecture 5, architecture 6... architecture 13 ".

B1: reads data from tree. B.

C1: create a file cursor to connect to the kpi. B file. Using a cursor to read data in segments is suitable for scenarios where kpi. B is large and cannot be loaded into memory at a time.

A2: Switch the PID field of B1 to B1. the condition is PID = ID.

B2: retrieve the record corresponding to the specified id in B1.

C2: Use the prior function to find C2 itself and all ancestor nodes, and reverse the order.

A3: Use C2 to generate a new sequence table. The fields are the organizational structures of IDS, names, and A1.

B3: The variable xtitle value is assigned to the title of the next node corresponding to the specified id.

C3: Find the direct subnode of B2 in B1.

A4: If C3 does not have a member, it indicates that the current node is a leaf. Use the B1 STRUCTURE to create a sequence table (only one empty record exists ). Otherwise, C3 is assigned to A4 directly.

B4: Use B1 to generate a new sequence table. The fields are ID and GID (group id ). GID uses the prior function to find the path from each record to the ancestor B2. Then, remove the record with the GID null, which is a non-B2 descendant record.

C4: the loop assigns the GID of B4 to the second-to-last of all ancestor, that is, the subnode of the current id.

A5: Switch the ID field of C1 to the corresponding records of C4, and remove the records that cannot find the corresponding value of ID, which is not the leaf of B2.

B5: group A5 by ID. GID to summarize KPIs.

C5: Align B5 with A4.

A6: Use C5 to generate a new sequence table. If the id is a leaf, the id and name are empty, so that the report can hide columns and the leaf is not displayed twice.

B6: returns two result sets to the report.

The second to fourth steps of the production process are not described here. refer to the previous article.Report development instance-dynamic multi-level KPI drilling report (I).

This code is written using a local file as a data source and can be easily transformed into a database as a data source. This type of report can be implemented in databases that do not support recursive queries.