Report Development Example-dynamic multi-level KPI drillthrough report (II)

in the report development Instance-dynamic multi-level KPI drillthrough report (I) , we talk about the scenario where the dynamic hierarchy report needs to be drilled, and introduce the implementation method of Oracle complex SQL. This report is still used to find out how to develop a script + local file.

The format and specific requirements of departmental KPI reports are described in (I). The difference is that the report data source changes from a database table to a local file: tree.b (tree-structured dimension table) and KPI.B (indicator fact table), data such as:

Tree file Data

KPI file data

Tree's leaf node, which is associated with the KPI table by an ID field. KPI files each day each ID is added to the KPIs record.

The first step in the implementation of the run-dry set of reports: Write the set of the script tree.dfx, complete the source data calculation, the input parameter is the specified current node number ID. The collection scripts are as follows:

A1: Create a new sequence with the title of each layer node: "Province, city, county, sales department, Architecture 4, Architecture 5, architecture 6 ... Schema 13 ".

B1: Read the data into the tree.b.

C1: Create a new file cursor to connect to the Kpi.b file. Use cursor fragmentation to read data, for cases where the KPI.B is large and cannot be loaded in memory at a time.

A2: Switch the PID field of the B1 to B1 's own reference, provided the Pid=id.

B2: Removes the record for the specified ID in the B1.

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

A3: Generates a New order table with C2, which is the corresponding organizational structure in ID, name, and A1.

B3: Defines the variable xtitle assignment to the specified ID corresponding to the title of the next layer node.

C3: Locate the direct child node of the B2 in the B1.

A4: If C3 does not have a member, it means that the current node is a leaf and creates a new order table with the B1 structure (with only one empty record). Otherwise, assign the C3 directly to A4.

B4: Generate a New Order table with B1, the field is ID, GID (group ID). GID is using the prior function to find the path of each current record to the ancestor B2. Then the record of the empty GID is removed, which is the record of the non-B2 descendants.

C4: The loop assigns the B4 GID to the penultimate of all ancestors, which is the child node of the current ID.

A5: Switch the ID field of the C1 to C4 corresponding record, remove the record that cannot find the corresponding value of ID, that is, the leaf of non-B2 descendants.

B5: The A5 is grouped by Id.gid, summarizing KPIs.

C5: Aligns the B5 with the A4.

A6: Creates a new Order table with C5. If the ID is a leaf, id, name is empty, easy to hide the report column, avoid the leaf display two times.

B6: Returns two result sets to a report.

The second to fourth step of the production process is not described here, see the previous Report development example-dynamic multi-level KPI drillthrough report (I).

This code is written as a data source in a local file and can be easily transformed to use a database as a data source, enabling such reports to be implemented on databases that do not support recursive queries.

Report Development Example-dynamic multi-level KPI drillthrough report (II)