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

Setting up/Fetching hierarchies in Relational Data

Started by joe22, 25 May 2010 11:03:35 AM

Previous topic - Next topic

joe22

I am working in Framework Manager of Cognos 8.4. I have a field called "Account ID" and another field called "Parent Account ID".
The data can't be modeled dimensionally. But using the two mentioned fields I need to setup or have a hierarchical structure up to 4-5 levels.
Something like this
Level 1: X1111
Level 2: X1111.1
Level 3: X1111.1.0
Level 4: X1111.1.0.0
Level 5: X1111.1.0.0.0

It will be kind of recursive relationship to the same table as it will be coming out of the two fields
What is the best way to create this hierarchical structure. Do I create aliases and join it to the parent table since this is recursive?

Anybody with any ideas will help greatly.
Thanks in advance.
Joe.

rockytopmark

I would say yes.  Create a query that builds a flattened out structure, so you have a column that represents each level in the hierarchy. Like:

AccountID, Level1ID, Level2ID, Level3ID, Level4ID, Level5ID, etc... 

You would indeed be using the same source table in a recursive relationship for as many levels as your Account hierarchy has.

note: If you have Account Desc available, have a column for each of those as well in same query.