Skip to Content

How to show different levels of hierarchy in different columns

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2013 at 05:01 PM

    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-and-sap-bw-hierarchies--part-1-of-2

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Debjit Singha

      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.