Duplication of columns in a crosstab in Crystal Reports for Enterprise 4.0

[Former Member]

Hi Experts,

We are currently using Crystal Reports for Enterprise 4.0 to develop the required reports. One of the reports we are generating needs to provide the P&L actuals or budgets based on G/L hierarchy and fiscal period. We are connecting to a BW query with prompts for fiscal period range and GL account.

I created a crosstab with G/L hierarchy in the rows and fiscal year period in the columns and actuals as the delgated measure. For ex : I selected July 2011 to June 2012 as my fiscal period range, the report sorts the columns in ascending order by default which would be April, August and so on. When I change the sort order to original order, the columns gets sorted as required, but create duplicate columns of the fiscal period and repeats for many number of times. Even for the G/L Hierarchy, when I change the sort order to original order, I have the same problem. We have observed this issue of original order sorting when we use the cross tab. Could anyone suggest a solution for this? Thanks.

Regards,

Rohini.

[Former Member · Accepted solution]

Hi Rohini,

I experienced the same issue with CR4E and Hierarchies.

It seems to happen when you try to select a Hierarchy and use the original order.

To be able to use this make sure that your hierarchy is the first objects in the query panel.

If you do so you are able to use the original order. It seems that if the position of the hierarchy is used on a different possition all other Query-objects are grouped and you seem to have duplicate data.

Somehow goofy, but with this workaround you should be able to get a result.

If you want to use 2 hierarchy with original order there is unfortunately no way.

I raised a ticket after experiencing the error and workaround, but i did not get a answer if this is a bug or not.

Best regards

Manfred

[Former Member · Accepted solution]

Hi Manfred,

I had used hierarchy as the first object in the query panel but the problem occurs for a normal attribute (as column) like fiscal year/period which is not an hierarchy. Even if I do pivot the fiscal period starts repeating on the rows and G/L hiearchy data doesn't repeat on columns. Its weird the way its working.

I tried to sort the fiscal year/period which is like Jan 2011, feb 2011 in order using formula. I wrote a formula to sort by period and then display the fiscal year period as text, but faced a problem with the measures as delegated measures does not work for formulas. If I use the sum of measures, I do get the values but has warning messages displaying in red color saying its recommended to use delegated measures which cannot be sent to the management. I did search through the SAP notes but didnt find anything useful.  Thanks for the response anyways.

Kind Regards,

Rohini Sreedhar.

[Former Member]

Hello, i am having a similar issue only its happening with a chart. e.g

I cannot sort in the chart the fiscal year's it displays the following (Used asceding sort)

2013.01, 2013.02, 2012.51, 2012.51, 2012.53

When i select Orignal Order i get duplicate...

2013.01, 2013.02, 2012.51, 2012.51, 2012.53, 2013.01, 2013.02

Using Crystal Enterprise X4.0 with Sp18

the dimension i am using is called Fiscal Year/Week and returns yyyy.ww, source is a Bex query from BW.

any ideas?