Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
IngoH
Active Contributor
0 Kudos

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.

31 Comments
Former Member
0 Kudos
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...)
Former Member
0 Kudos
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
IngoH
Active Contributor
0 Kudos
Hi,
look into the first part of the series where it outlines what the different source options are

ingo
IngoH
Active Contributor
0 Kudos
Hi Ian,

most likely I need another one or 2 weeks to get to that point.

Feel free to contact me directly and I will see what we can do in the short term.

ingo.hilgefort@sap.com

Ingo
Former Member
0 Kudos
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
Former Member
0 Kudos
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?
Former Member
0 Kudos
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.
IngoH
Active Contributor
0 Kudos
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
Former Member
0 Kudos
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.
Former Member
0 Kudos
Hi,

Which InfoCube/s are you working on for this example?

Cheers,
Thor
IngoH
Active Contributor
0 Kudos
HI,
this was an example based on a customized cube but you can also take a look at this material here where I leverage standard demo cubes:

BusinessObjects Integration with SAP NetWeaver BI - Technical Material

Ingo
Former Member
0 Kudos
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?
Former Member
0 Kudos
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.
Former Member
0 Kudos
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.
IngoH
Active Contributor
0 Kudos
Hi,
unfortunately you can not use hierarchies inside a cross-tab

Ingo
Former Member
0 Kudos
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
IngoH
Active Contributor
0 Kudos
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
Former Member
0 Kudos
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
IngoH
Active Contributor
0 Kudos
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
Former Member
0 Kudos

Hi Ingo,/martin

IngoH
Active Contributor
0 Kudos
I will pass it on but there is not that level of detail in regards to the roadmap available.

Ingo
rico_portner2
Explorer
0 Kudos
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
IngoH
Active Contributor
0 Kudos
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
Former Member
0 Kudos
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
IngoH
Active Contributor
0 Kudos
Hi,

take a look here:

CR & Hierarchies Part 1
Crystal Reports and SAP BW Hierarchies - Part 1 of 2
CR & Hierarchies Part 2
Crystal Reports and SAP BW Hierarchies - Part 2 of 2


Ingo
bill_ignelzi
Explorer
0 Kudos
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.
Former Member
0 Kudos
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
IngoH
Active Contributor
0 Kudos
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
Former Member
0 Kudos
Hi Ingo,

I have done that, but nobody is replying.

Charting on a specific hierarchy Level

Thanks,
Andrew
former_member193430
Participant
0 Kudos
I cannot see the screen shot on the page for the part 1, second part is good.
IngoH
Active Contributor
0 Kudos
Hi,

just tried it myself with Internet Explorer and Firefox and screens are showing up for me.

Ingo