Hi All,

I have been trying to create Department structure in our company...

Z (Organisation)
/ \
X Y (Level1)
/ \ \
I J K (Level2)
/\ / \
A B C D (LEVEL n)


In our database we store this information in a dimn. in following
way...

create table person_dimension (
key int not null,
parentkey int,
deptname char(50) );

Data in the table:
Z - null
X - Z
Y - Z
:

G - B
H - D

Now I want to represent this hierarchy in Cognos Transformer... I
created an alias table in Framework Manager and joined original table
to alias table. Then I created 4 shortcuts and joined all the tables.
In Transformer I tried creating department dimension and then adding 4
levels using data source as key and label as deptname.
This gives me tree, with 5 levels, which just gives me in the following
way.


Blank Z
(Organisation) / \
/
Blank Z X

(Level1)
/ \ \ / \
Blank Z X I J

(Level2)
/ \ / \ / \ / \ \
Blank Z X Y I J A B D

(Level3)
/ \ / \ / \ \ / \ / \ / \ \ \
Blank Z X Y I J K A B C D E F G H

(Level4)


Even though I suppress blanks it is giving me blank categories. Since
the tree is not symmetric, generated categories are not correct. I
noticed that since Y has just 1 descendant that path is not correct and
also J has 1 descendant that path is not correct.

Can anybody show me the simple way, using built-in functionality, to
achieve a solution for my problem?

If there is no way on Cognos side can it be done on the database
(creating a column for level)?

Also, I need a count of categories in each level. Is there a way we can
get the count of 1 descendant level in Cognos?

Please advise as soon as possible.

Thanks in advance,