Skip to Content

In case you missed the first two parts of my blog series I have included the links to them here:

BusinessObjects and SAP Part I

BusinessObjects and SAP Part 2

 

In this blog I will explain how Crystal Reports is able to leverage hierarchies from a SAP BW query and in particular I will also show a time-dependent hierarchy and the impact of a keydate variable for Crystal Reports.

 

The query that I will use contains a characteristic Product with an activated hierarchy in the rows and several keyfigures in the columns. In addition the query contains a Keydate variable for the Product Hierarchy that allows the user to select a specific date for the time-dependent product hierarchy.

 

When I create a new report using the Crystal Reports designer based on the above query I receive the following meta-data from the BW query shown in the Field Explorer:

 

 

In the picture the characteristic Product is shown with a Product Node ID and a Product Parent Node ID. These two fields return the hierarchy information in form of a parent-child relationship to Crystal Reports.

 

To leverage this parent-child relationship in Crystal Reports I am creating in the first step a new group in my report (Menu Insert > Group) and select the Product Node ID as the grouping field.

 

 

In the second step I need to configure the group to become a hierarchical group.

I select the menu Report > Hierarchical Grouping Optionsand select the Product Parent Node ID as the parent for the just created group.

 

I configure the Group Indent to be 0.2 inches and preview my report.

Based on the Keydate Variable in the underlying BW query, a Crystal Reports parameter is coming up and I need to select a date for my report. I enter the current date and continue to preview my report.

 

 

The image below shows the grouping in my report on page one (right hand side of the image) and the navigation tree in Crystal Reports for all created groups (left hand side of the image).

 

 

 

Based on the parent-child relationship Crystal Reports resolved dynamically the hierarchy and created a report for me that allows me to navigate along the hierarchy from SAP BW.

 

As the next step I can add now the keyfigures.

I am going back to the design of my report and add the keyfigure Order Amount into the detail section.

 

Now I select the Order Amount and select the menu Insert > Summary from the context menu.

 

 

Based on the hierarchical grouping, Crystal Reports Designer is offering me the option to summarize the Order Amount across the hierarchy.

This option is important in cases where the hierarchy nodes are not containing already the summaries.

In my case the BW query did already return all the aggregated totals to Crystal Reports, so I leave the option unchecked and include my new summary into the report.

 

This results in a very simple report using a hierarchy from the underlying BW query and creating dynamically a grouping in my report.

 

Now I refresh my report and I am being asked to provide a value for the keydate variable.

 

In the initial run I enter the value Feb 02, 2008 and receive the result as shown in the image above.

 

Now I refresh the report again and enter the value Feb 02, 2004 and receive the following:

 

 

In the second refresh the country FRANCE is shown to be part of Northern Europe and in the initial run the country FRANCE was part of the hierarchy node for Southern Europe.

 

This shows that Crystal Reports is also able to leverage the keydate variables from a BW query and resolve the time dependency of the hierarchy.

 

I hope this quick description is giving everyone a good idea how easy it is to create a report in Crystal Reports and leverage hierarchies – including time dependent hierarchies – inside the report.

To report this post you need to login first.

31 Comments

You must be Logged on to comment or reply to a post.

  1. swapna gollakota
    Hi Ingo,

    Your blogs are really helping me to understand how to work on crystal reports.Your way of explanation is quite simple ,short and to the point.

    Awaiting for your future blogs to discover the hidden functionalities(atleast to me) of crystal reports.

    Concern:can you please tell me sources for crystal report(ex:query,data provider,query view…)

    (0) 
      1. sasi kanth
        Thanks Ingo its very useful blog .iam trying to design a crosstab Crystal report there I am accessing  bw hierarchy(0gl_account).could you please explain me How  bw hierarchy can be used in crosstab report.

        Thanks and Regards,
        Sasikanth.

        (0) 
  2. Ian Curtis
    Hi Ingo,

    I like the blog, but i am so waiting for number 5 as I am struggling to integrate a web service into Xcelsius. When will this be ready to go ??

    Also I am using the web service linked to function module QUERY_VIEW_DATA in BI7 but when I link it into Xcelsius I only seem to get the key figure values and not the Customers. A number of people on SDN talk about writing a program to run this module and populating tables, but not sure about this as they are not using it to link into Xcelsius.

    Your views on this would be interesting to me.

    Thanks

    Ian

    (0) 
  3. Alfredo Borunda
    I am trying to use a Bex query that has a cost center hierarchy with a variable that allows users to select specific nodes or even cost centers at run time.

    In Report Designer, this query behaves by providing users with a drop down menu so they can select the nodes, just like it would in BEx Analyzer.

    Can this be done in Crystal? If so, how? I looked at the blog 3 and it has an example for a static hierarchy embedded in the report, but not for a variable. Thanks for your help and great blog!

    Alfredo Borunda

    (0) 
    1. Rammohan Meshinani
      Ingo,

      I have CostCenter hierrachy in my Bex query – but when I opened the same query in Crystal, it is not showing in the same way as shown in the picture in blog. It is diplayed with Hierarchy Name Node.Description, Text.String and Level 01.Text.

      I couldn’t find CostCenter NodeID and Parent NodeID as explained in the blog – Am I missing any settings?

      (0) 
      1. goriparthi surendra
        Hi Ingo,

        I a have one Gl Account Hier archi,i have to make them as prompts in the crystal reports,In the BeX Analyzer it is showing the hier archi,But in Crystal Reports 2008 It is showing the flat list with Enterprise Authentication,With the SAP Authentication I am able to see the tree structure.

        but the Node variables coming from BeX are not Filtering the data,Every time it is bringing the entire data.

        Please help me on this issue?

        I am working on the following tools:

        Business objects repository 3.0
        BeX—–7.0
        Crystal Reports ——–2008.

        Best Regards,
        Surendra.

        (0) 
        1. Ingo Hilgefort Post author
          Hi,
          could you open a entry in the Forums ?

          In Crystal Reports Design the hierarchy will be flat, in InfoView the hierarchy will be shown as a tree when using the SAP Authentication.

          ingo

          (0) 
        2. sasi kanth
          Hi Ingo,

          Thanks Ingo its very useful blog .iam trying to design a crosstab report there I am accessing  bw hierarchy(0gl_account).could you please explain me How  bw hierarchy can be used in crosstab report.

          Thanks and Regards,
          Sasikanth.

          (0) 
        3. Ramesh Ravilla
          Hi Ingo,

          I tried to do the same way as you did to get the Hierarchy for 0Material from BI to Crystal Reports. Also i used MDX Driver between Crystal and BI. And I am still unable to get the Hierarchy into Crystal Reports.Could you please suggest me if iam missing any of teh Steps or any other settings to be done.

          Regards,
          Ramesh

          (0) 
      2. goriparthi surendra
        Hi,

        I think you have to go to click on SAP Tool Bar
        and go to settings over there.
        Check the option use MDX driver with support for multiple structure,Then It will show the paren id and nod id.

        Please try this and let me know whether it is working or not…….

        Best regards,
        surendra.

        (0) 
  4. Thor Naess
    I have an SAP BI query with a hierarchy in the rows and key figures in the columns, much similar to the one you are working on here. The query generates data when I execute it from Query Designer.

    However, when I preview the CR report, with the same group configurations as in your example, it does not show any data for the hierarchy. It shows data for the key figures. When I try to browse data from the Field explorer on the product Node ID field or the Parent ID field I get no values. It seems that CR is unable to read the data from the SAP BW hierarchy in this case. Can you think of anything I might be doing wrong here?

    (0) 
    1. Ingo Hilgefort Post author
      Hello Thor,

      would suggest to open an entry in the SAP Integration Kit forum.

      When you put the node ID and Parent ID from the Hierarchy characteristic into the detail section of your report – do you see the key values from the hierarchy being returned ?

      thanks
      Ingo

      (0) 
  5. Martin Sahlin
    Hi Ingo,

    I have a P&L query in BEX with one structure in the rows and one in the columns.

    The rows structure is handled as one field in CR. This consists of all the fields in my BEx-structure with severeal hidden fields and in a formatted design.

    How would you work with multiple structures in CR? I want to format the report line by line, but have only one line to work with (the whole structure)

    Thanks in advance

    Kind regards
    /martin

    (0) 
    1. Ingo Hilgefort Post author
      Hi Martin,

      in case of a multi-structure query the structure with keyfigures will get resolved in single fields, the structure with the characteristics is shown in a single field and as soon as you show it as in the detail area each element from the characteristics structure will become a row in your report.

      You can use conditional formatting to format the line items

      hope this helps

      Ingo

      (0) 
          1. Rico Portner
            Hi Ingo

            in our CR implementations we always have that issue of multiple structures to handle. In particular if the keyfigures are positioned in the lines the axis of the report is getting transposed and we have to deal with multi column format with all its restrictions (fixed column width, 1 detail section etc.)
            Could you give a statement if that issue is on the worklist for further improvements of SAP integration??

            Regards
            Rico

            (0) 
    2. Ingo Hilgefort Post author
      Hi,

      I would suggest that you open an entry in the SDN forum and enter your scenario and question there so that people can provide you answers .

      thanks
      Ingo Hilgefort

      (0) 
  6. Thomas Ringe Pontoppidan
    Hi Ingo

    I followed your guide and got the hierarchy in my report, but unlike your example my values are displayed with the same indent as the group header text.
    Is there a trick that I missed or how do I make the numbers align straight underneath each other?

    Best regards
    Thomas

    (0) 
  7. Bill Ignelzi
    Ingo,

    Thanks for posting this entry. I have successfully duplicated your steps but have one problem. I cannot figure out how to reverse the appearance of the hierarchy. Uisng your data the hierarchy would appear like this on the report:

    GERMANY        6,622,920
    UK            30,361,680
       NORTH      36,984,600
    ….
    ….
          WORLD  519,266,760

    I thought that setting the flag for Group 1 to “in descending order” would do it, but, that has been unsuccessful.

    Can you help?

    Thanks,
    Bill I.

    (0) 
  8. Andrew Barker
    Hi Ingo,

    Is it possible to chart on a specific hierarchy level e.g. level 2 in the hierarchy?

    So far I’ve created a formula that that checks if the hierarchy level is 2 and setup the chart to plot when this value changes. This works to an extent, but sums all other levels and includes it on the chart as a single series.

    Any help would be appreciated and thanks in advance,
    Andrew

    Best regards,
    Andrew

    (0) 
    1. Ingo Hilgefort Post author
      Hi,
      I would suggest to enter the question into the SDN Forum and to continue the conversation there as it is much easier to follow up.

      regards
      Ingo Hilgefort

      (0) 

Leave a Reply