Hi,
I've got a query driven from an organisational unit HR hierarchy. Unfortunately, neither way of presenting the information from the hierarchy is satisfactory.
The two options I know of are to have the rows showing as a flat structure, but org. unit displaying as a hierarchy, or have all the rows displayed within a hierarchy.
For example, with org unit as hierarchy, but the rows flat I'd get something like this:
Org Unit Grantholder GrantNo Sponsor Grant Value
---------- ----------- ------- ---------- -----------
University Prof X 441 Test Co. 100
442 TestCorp 75
Prof Y 443 Test Co. 120
Prof Z 444 TestCorp 80
Result: 375
Faculty Prof X 441 Test Co. 100
442 TestCorp 75
Prof Y 443 Test Co. 120
Prof Z 444 TestCorp 80
Result: 375
Res Sch Prof X 441 Test Co. 100
442 TestCorp 75
Prof Y 443 Test Co. 120
Prof Z 444 TestCorp 80
Result: 375
Dept A Prof X 441 Test Co. 100
442 TestCorp 75
Prof Y 443 Test Co. 120
Result: 295
Dept B Prof Z 444 TestCorp 80
Result: 80
There will be potentially thousands of Grantholders at University level through to dozens within a department. This approach becomes grossly unwieldly for a user.
The obvious way to overcome this is to show grantholders only within the node they are assigned by setting the entire set of rows to use a hierarchical structure:
Hierarchical Rows Grant Value
-------------------- -----------
University 375
Faculty 375
Research School 375
Department A 295
Prof X 175
441 100
Test Co. 100
442 75
TestCorp 75
Prof Y 120
443 120
Test Co. 120
Department B 80
Prof Z 80
444 80
TestCorp 80
As the characteristics aren't laid out in labelled columns, the results are less clear and with even a small number of characteristics, drilling down within the hierarchy becomes a chore. Also, the BEx Analyser can't handle all the indents required for four levels of hierarchy and then a dozen characteristics.
I'm aware there are a few cosmetic options to play with, such as opening nodes above instead or below in the first example or defaulting open all nodes at a specified level in the second example, but none of those solve the core problems.
Ideally, the solution I'm looking for would produce the results shown below:
Org Unit Grantholder GrantNo Sponsor Grant Value
---------- ----------- ------- --------- -----------
University Result: 375
Faculty Result: 375
Res Sch Result: 375
Dept A Prof X 441 Test Co. 100
442 TestCorp 75
Prof Y 443 Test Co. 120
Result: 295
Dept B Prof Z 444 TestCorp 80
Result: 80
(To be honest, to me this seems the most obvious way to present hierarchical data, so I find it surprising that it's not a standard option).
Another option might be if the org unit hierarchy can be split into separate columns:
Node 1 Node 2 Node 3 Node 4 Grantholder, etc...
University Faculty Res Sch Dept A Prof X
Prof Y
Dept B Prof Z
This is the approach used in the original versions of the reports that we're trying to re-create in BW. However, in that case, each org unit record held information for all its parent nodes and this isn't the case with the org unit hierarchy we get from SAP.
These reports will be delivered predominantly via the web.
Any suggestions for compromises would also be appreciated.
Thanks.