How to display unbalanced/ragged hierarchy in Cognos DMR

Check list of all posts

While TM1 and Transformer can naturally handle unbalanced/ragged hierarchy, Cognos framework manager can’t handle it and display it.

Please see sample data below, which is a typical unbalanced and fully extended hierarchy

create table H1(L1 varchar(10),L2 varchar(10),L3 varchar(10),L4 varchar(10),LO varchar(10));

insert into datamart.H1 values

 ('L1_1', NULL,  NULL, NULL,'L1_1')

,('L1_1','L2_1', NULL, NULL,'L2_1')

,('L1_1','L2_2', NULL, NULL,'L2_2')

,('L1_1','L2_1','L3_1',NULL,'L3_1')

,('L1_1','L2_1','L3_2',NULL,'L3_2')

,('L1_1','L2_2','L3_3',NULL,'L3_3')

,('L1_1','L2_2','L3_4',NULL,'L3_4')
When displayed as Cognos DMR member, it looks as diagram below

In order to avoid these NULL columns, or members in FM, we fill all empty level with lowest level element. This solution can satisfy the self-services BI requirement, as well as develop complicated report with drill up and down in a normal way.  it is probably better hadnle it in FM business layer.

create table H1(L1 varchar(10),L2 varchar(10),L3 varchar(10),L4 varchar(10),LO varchar(10));

insert into datamart.H1 values

 ('L1_1','L1_1','L1_1','L1_1','L1_1')

,('L1_1','L2_1','L2_1','L2_1','L2_1')

,('L1_1','L2_2','L2_2','L2_2','L2_2')

,('L1_1','L2_1','L3_1','L3_1','L3_1')

,('L1_1','L2_1','L3_2','L3_2','L3_2')

,('L1_1','L2_2','L3_3','L3_3','L3_3')

,('L1_1','L2_2','L3_4','L3_4','L3_4')

However, this solution give users to all the levels to the bottom. the best solution is to stop where NULL occurs. Interestingly, Cognos DMR somehow stops at empty string.

 create table H1(L1 varchar(10),L2 varchar(10),L3 varchar(10),L4 varchar(10),LO varchar(10));

insert into datamart.H1 values

 ('L1_1','',         '',        '','L1_1')

,('L1_1','L2_1',     '',        '','L2_1')

,('L1_1','L2_2',     '',        '','L2_2')

,('L1_1','L2_1',     'L3_1',    '','L3_1')

,('L1_1','L2_1',     'L3_2',    '','L3_2')

,('L1_1','L2_2',     'L3_3',    '','L3_3')

,('L1_1','L2_2',     'L3_4',    '','L3_4')

Furthermore, we can change Cognos Member caption as below

case when [Data Layer].[H4].[L2] ='' then '-' else  [Data Layer].[H4].[L2] end

case when [Data Layer].[H4].[L3] ='' then '-' else  [Data Layer].[H4].[L3] end

case when [Data Layer].[H4].[L4] ='' then '-' else  [Data Layer].[H4].[L4] end

Then the final hierarchy looks like below