Merge BW Hierarchies in WebI Reports (New GL Reporting)- things to Know

[kohesco]

In order to achieve financial statements reports, I had to work out how to work with the BW hierarchies.  This document is meant to share the things I learned.

This is accomplished in BI 4.1 SP6 for a NEW GL reporting environment.  Virtual BW InfoPorviders - Inverse routines and Hierarchies to set it all up.

• To have the complete Hierarchy, inclusive the nodes/GL accounts not booked, I had to merge hierarchies.  One from a Bex Query based upon Transactional Data, ie InfoCube and one based upon Master Data, ie InfoObject.

2 queries added in query panel

• Select all members to represent all in your report

• To have a correct merge, you have to merge "uniquely", ie you have to have each node and each GL account uniquely defined in your merge.

Use Unique nodes/gl Accounts, no linked hierarchy possible in WebI,

• If it is not possible to work on the KEY/Code of your hierarchies, then merge the Hierarchy as Text and Merge as well the Key

     If Hierarchy is based on TEXTS then MERGE KEY as well, to have an unique Hierarchy

• Set Merged hierarchy properties to the MD-hierarchy to represent the entire hierarchy

• Set Default Hierarchy Expansion if needed. This feature is available in BI 4.1 SP6 and ensures when opening/refreshing the report the hierarchy will be expended as set by default.  Note the selection is level-based!

• Put Make up to the hierarchy to achieve a higher readability, by using formula depth()

You can add Conditional formatting to achieve that.

• Calculate specific parts of your Hierarchy by using depth as well, DO NOT USE sum()

Total of the hierarchy if the hierarchie exists without one upper top node is calculated by just [amount]

OR by sum ([amount] where ([Hierarchy].depth=0)) (In older BI versions we noted, by expanding/collapsing the sum result calculated as above changed)

• Reverse Sign of Hierarchies is accomplished by using a Formula Variable (Replacement Path on your infoobject) in Bex Query Designer, The new measure can be used for calculations, NOTE DO CALCULATE WITH THE INITIAL VALUES DO NOT CALCULATE WITH THE MULTIPLIED BY REVERSE SIGN VALUES

• Multiple Language is set by using next to GL account - infoobject also using a node - infoobject;  Both infoobjects are set as language depended.  By changing the viewer local the user is capable to see the reports/Hierarchies in different languages.

• SAVE REPORT PURGED and set REFRESH ON OPEN, to dynamically retrieve hierarchies updates. The default level will expand the renewed hierarchy nicely. Otherwise a manual handling (purging will be necessary)

• INPUT CONTROL - tree list, only possible on not merged dimension