If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Show no duplicates of a "balanced hierarchy" in reports

Started by Rakish, 09 Jul 2012 04:55:12 AM

Previous topic - Next topic

Rakish

Hi all,

we are using a DMR model with a nine level cost center hierarchy and this hierarchy is unbalanced.
To get this hierarchy balanced we pass each node and leaf through to the last level in our ETL process, e.g. the only root member has the same values in each of the 9 levels or e.g. if a leaf is on level 5, we fill values in level 6 to 9 with the same value as level 5.
In a report I want to show the hierarchy levels only until the child and parent are identical.
Here is an example:
Level 1: Cost Center Europe (root)
Level 2: Cost Center Great Britain (node)
Level 3: Country management (node)
Level 4: Corporate communication (node)
Level 5: Fleet management (leaf) <--- this should be the last level shown in report
Level 6: Fleet management (leaf)
Level 7: Fleet management (leaf)
Level 8: Fleet management (leaf)
Level 9: Fleet management (leaf)

I tried a filter with currentmember <> parent in a calculated member, but without success. Do you have further ideas how to get Cognos to show only hierarchy trees until the first leaf appears?

Thanks in advance!

Rakish

Rakish

Hi all,

I solved my problem... wasn´t as easy as presumed...

I does NOT work by using any combination of the filter() function and parent(), currentmember(), firstchild(), members() and so on.
I thought something like the following has to work:
filter(all_members where currentmember <> parent) or filter(all_members where currentmember <> firstchild_of_currentmember) - but without success.

I changed my FM model, the SQL statement (Case When Then) returns now only values for all levels when the value of parent level differ from value of child level, else it returns NULL. Then I added a column to the table that contains the same values as level9 (level9 is usually not nullable but with my "Case-when-then" statement several level9 rows became null) and used this column for the join with the fact table. In my DMR I didn´t use this new column at all and as a result I got my unbalanced hierarchy in my report... This extremely short description might be a little bit confusing, so send your replies if more details are appreciated.

Greetz
A happy Rakish