Crosstab and % calculation

[Kim321]

Hi,

This is similar to my previous issue with calculating percentage in a crosstab, but that solution doesn't work for me now

I need to create a report with a crosstab. The report has a selection on wether to see the data on order detail level or on summary level. The data we are reporting on is on the detail level.

The report has mostly $ values, which we can just sum up to the summary level and the data will be correct. However, we need to have some percentages such as Margin % which is Margin / Revenue, but we can sum it from the detail level since that will make the % incorrect.

The report has this layout:

Jan Feb
Sales Revenue 100 150
GST 10 15
Cost of Sales 0 5
COS adjustment 0 10
Margin 20 15
Margin % 20% 10%

There is only one measure.

Is this possible?

I am using Crystal Reports for Enterprise 4.0 which is very similiar to Crystal Reports 2008/2011.

Thanks
Kim

 

[lbass]

What do you mean by "there is only one measure"? Please explain the current crosstab set up by identifying row, column, and summary fields.

Also I think you meant to say "but we canNOT sum it from the detail level"...

-LB

 

[Kim321]

Thank lbass,

Rows are accounts (Sales Revenue, GST, Cost of Sales, COS adjustment, Margin, Margin %, etc.)

Column is Month of the year

Summary field is "Value", which can contain both $ values and percentage.

And yes I meant "but we canNOT sum it from the detail level"...

 

[lbass]

Can you clarify whether there is one row "Account" with values as shown or whether these are separate row fields entered into the crosstab expert?

-LB

 

[Kim321]

Account is just one field as shown in OP.

 

[lbass]

It wasn't obvious to me.

Select the summary field->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar rev;
numbervar marg;
if gridrowcolumnvalue("table.account")= "Sales Revenue" then
rev := currentfieldvalue else
if gridrowcolumnvalue("table.account")= "Margin" then
marg := currentfieldvalue;
false

For gridrowcolumnvalue you should be selecting your account field from the field list and then replace the curly brackets with ", as shown.

In the same format screen->DISPLAY STRING->x+2, enter:

whileprintingrecords;
numbervar rev;
numbervar marg;
if gridrowcolumnvalue("table.account") = "Margin %" then
if marg = 0 then
"0%" else
totext(marg%rev,1)+"%" else //1 for 1 decimal
totext(tonumber(currentfieldvalue),0)

Then add a reset formula by selecting the column label (for month)->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar rev := 0;
numbervar marg := 0;
false

-LB

 

[Kim321]

Thank you so much for your effort LB. I will try to implement this and get back to you soon

Cheers,
Kim

 

[Kim321]

Thank you so much LB! Your solution worked! Not that I'm surprised since you are a genious