cancel
Showing results for 
Search instead for 
Did you mean: 

How to show different levels of hierarchy in different columns

DebjitSingha
Active Contributor

Hi,

Our requirement is to display Level 1 hierarchy of an infoboject in one column and level 2 hierarchy in another column of an report. How can we achieve this in CR 2011 (data source BEx)?

Thanks,

Debjit

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Debjit

I assume you have read Ingo's blog on how to group BW Hierarchies.
http://scn.sap.com/community/bi-platform/businessobjects-bi-for-sap/blog/2009/07/09/crystal-reports-...

The only problem I have with Ingo's solution is that all the hierarchy nodes are trapped in GH1.

I have been using an alternate solution that flattens out the hierarchy nodes.  I'm not sure this will work for you but it may be worth a try.

If your BW system is similar to mine then you your BEx query should be returning a [NodeID] field for each record in the Hierarchy.  The [NodeId] is delimited by a period and looks something like this:


ROOT
ROOT.20005001
ROOT.20005001.20005002
ROOT.20005001.20005002.20007562
ROOT.20005001.20005002.20007562.20007562
ROOT.20005001.20005002.20007562.20005501
ROOT.20005001.20005002.20007562.20005501.20005501
ROOT.20005001.20005002.20007562.20005501.20016984
ROOT.20005001.20005002.20007562.20005501.20016985
ROOT.20005001.20005002.20007562.20005501.20016986
ROOT.20005001.20005002.20007562.20005501.20016986.20016986
ROOT.20005001.20005002.20007562.20005501.20016986.20017109
ROOT.20005001.20005002.20007562.20005501.20017084
ROOT.20005001.20005002.20007562.20005612
ROOT.20005001.20005002.20007562.20005612.20005612

To parse the [NodeID] into different hierarchy levels I create a set of formulas and name them:  node01, node02, node03, etc..  The code looks something like this:


@node01
stringvar array nodes:=split([NodeId],".");
if ubound(nodes)>=1 then nodes[1]

@node02
stringvar array nodes:=split([NodeId],".");
if ubound(nodes)>=2 then nodes[2]

@node03
stringvar array nodes:=split([NodeId],".");
if ubound(nodes)>=3 then nodes[3]

I then place these formulas as seperate columns on the report canvas.  These formulas also can be used as groups in the Group Expert.

To display the node description I create another set of formulas and name them:  L01name, L02name, L03name, etc..  These formulas contain code like this:

@L01name
whileprintingrecords;
shared stringvar L01_name;

stringvar array nodes:=split([NodeId],".");
if ubound(nodes)=1 then L01_name:=[NAME]


The main problem with this solution is that you need to know the maximum number of levels to the Hierarchy and create a pair of formulas for each node.

let me know if this works for you.

DebjitSingha
Active Contributor

Thanks Mike,

I used :

whileprintingrecords;
shared stringvar L01_name;

stringvar array nodes:=split([NodeId],".");
if ubound(nodes)=1 then L01_name:=[NAME]

only issue is that the node is shown once and other rows are left blank. Is it possible to repeat node descriptions?


Former Member
0 Kudos

the easy answer is to add one more line of code:

else L01_name

Unfortuantely, it's not that easy.  You need to add some logic to re-initialize when the parent nodes change.

try something like this

whileprintingrecords;
shared stringvar L07_name;

stringvar array nodes:=split({NodeId},".");
if ubound(nodes)<7 then L07_name:="";  //re-initialize when parent node changes
if ubound(nodes)=7 then L07_name:={Name}
else L07_name  //repeat name on each record

Also, realize that you can't group on whileprintingrecords formulas.  That's why I create the node01, node02, node 03 formulas.

DebjitSingha
Active Contributor

Mike Thanks again,

I used both of your suggesting and hide the node01 (made it small enough to be invisible) and use the node01 column for grouping.

but as soon as inserted key figures the layout changed and start showing level 3 lines as well (which I do not want). so have written a formula for that key figure:

{FORMULA1}

If  {node2} <>"" then totext (keyfigure) else ""

this solved my layout issue. But now when I am trying to show subtotal under group footer I am not able to select  {FORMULA1}.

Can you give me a workaround to show totals and subtotals.

Required Layout:

Header 1Header 2
Level 1 (A)
          Level 2 (B)1
          Level 2 (C)2
          Level 2 (D)3
          Level 2 (E)4
          Level 2 (F)5
Sub Total15
Former Member
0 Kudos

hmmm . . . lots of things going on here . . .

It sounds like your original question has been answered so you should probably close this thread and open new threads for your other questions.  (isn't it funny how resolving one issue creates new issues to resolve)

meanwhile . . . .

1.  You shouldn’t need a sub-total formula – the BEX should provide you with an aggregated key figure value for each hierarchy node


2. You cannot summarize a text field . . . in your {FORMULA1} you are converting keyfigure to text

3. Another option for displaying a GF total is to use a variable to collect values while the detail records are printing.  You need three more formulas:


@value_GH
whileprintingrecords;
shared numbervar my_value:=0  //initialize variable

@value_Detail
whileprintingrecords;
shared numbervar my_value;
If {node02}<>”” then
my_value:= my_value + {keyfigure}  //accumulate value
else my_value

@value_GF
whileprintingrecords;
shared numbervar my_value   //display value


P.S.:   You don’t need to try to hide your node1 field.  In Section Expert you can insert another Detail section.  Place node1 in “Details a” and then suppress that section so it doesn’t print – the formulas in the suppressed section will still execute.

Answers (0)