Crosstab Data Loss

[Former Member]

Hello All

I need help with a Crystal Reports 2008 crosstab linked to BW. The problem is as follows:

When reporting data on two Hierarchies within a Cross Tab I get correct result as follows:

_______________Region 1 Region 2 Region 3 Region 4

Article Group 1..... 100...... 2000..... 300....... 400

Article Group 2..... 6000..... 200..... 3000....... 500

When adding a third Hierarchy to the cross tab I lose data for Region 3 and 4.

__________________________Region 1 Region 2

Article Group 1 Department1..... 50..... 1000

................. .. . Department2..... 50..... 1000

Article Group 2 Department3..... 3000..... 100

.................... .. Department4..... 3000..... 100

Please note that Regions, Article Group are grouped by specified order.

This seems like I have hit a data limit on which the Cross Tab can report upon. Can anyone suggest how I could fix this ?

Thanks

Phillip

Edited by: Phillip L on Jan 6, 2011 12:12 PM

[Former Member · Accepted solution]

This is not a cross tab data limit.. I have made them much bigger than this one.

If the Department number is in a different table than the other fields, it may be a linking issue where you are only getting the data where the Department number exists. Try a left outer join to get the Department.

[Former Member · Accepted solution]

Thanks Debi

Sadly with BW I cannot impact the join.

Your response though does hint that something isn't quite right with the BW Query and MDX (SQL) that calls the data or that Crystal Reports has a problem with the join of 3 different Hierarchies. As other reports work as expected when utilising only 2 hierachies within the cross tab.

Thanks for your input

Phillip

[Former Member · Accepted solution]

I have crosstabs that go 6 or 7 hierarchies deep with no problem so I don't think that is it.

Did you try a formula for the Department that tests for null values to see if this is the issue?

My SQL is not strong enough to help you with your statements. Be patient and perhaps one of the SQL gurus will jump in.

[Former Member]

Thanks for the tips. I am investigating both directions now and will let you know if the problem is solved.

Your help is appreciated

Phillip

[Former Member]

Hi,

As Debi said, use the formula for department and region to see if it is a table join issue.

If isnull(region) then 'No Region' else region.

[Former Member]

I have investigated this problem further and seem to have some strange behaviour within Crystal Reports When combining Hierarchy and Key Figures

When Running SQL to display the 3 Hierarchy elements of my report using the below SQL

SELECT NON EMPTY CROSSJOIN([PGORU CC00_ORU_INTERNAL C00].MEMBERS, CROSSJOIN([PMAG Z_PMAG_HIERARCHY].MEMBERS, [NMATERIAL__PBS].[LEVEL01].MEMBERS)) DIMENSION PROPERTIES [NMATERIAL__PBS].[2NMATERIAL__PBS], [PMAG Z_PMAG_HIERARCHY].[2PMAG], [PGORU CC00_ORU_INTERNAL C00].[2PGORU] ON ROWS FROM [NTMIPVIPP/NFR_NTMIPVIPP_SCCKFO_001] SAP VARIABLES [REP_CURR] INCLUDING [0CURRENCY].[EUR], [PRI] INCLUDING [0FISCPER].[PH2009012]

I get a nicely formated report

Region........ Article Group Node ID........ Department Node ID

I_WORL..............CCMAG......................................... 6906

When adding Key Figure Measures using SQL

SELECT {[Measures].[38Q6G91JCCLI0HZVI2KSXRJ57], [Measures].[3AUWLL04PQC65O6FPQABS36AZ], [Measures].[3RWPS8OVOS9JB1MXEZYIIOBL7], [Measures].[3U1FXKNH2607G7THMNO1CZYQZ], [Measures].[4JM5PK6LIQW96A086K6BGVOOB]} ON COLUMNS, NON EMPTY CROSSJOIN([PGORU CC00_ORU_INTERNAL C00].MEMBERS, CROSSJOIN([PMAG Z_PMAG_HIERARCHY].MEMBERS, [NMATERIAL__PBS].[LEVEL01].MEMBERS)) DIMENSION PROPERTIES [NMATERIAL__PBS].[2NMATERIAL__PBS], [PMAG Z_PMAG_HIERARCHY].[2PMAG], [PGORU CC00_ORU_INTERNAL C00].[2PGORU] ON ROWS FROM [NTMIPVIPP/NFR_NTMIPVIPP_SCCKFO_001] SAP VARIABLES [REP_CURR] INCLUDING [0CURRENCY].[EUR], [PRI] INCLUDING [0FISCPER].[PH2009012]

After 300 pages of nicely formatted data the format changes and dat is no longer in allignment with heading.

Region is populated with EURO

Article Group is polutated with Euro

Department Node if populated with Region

Seems like a bug to me. Any ideas or should I just raise an OSS?

Thanks

Phillip

[Former Member]

>

> After 300 pages of nicely formatted data the format changes and dat is no longer in allignment with heading.
> 

This sounds like a memory problem, considering the length of your report.