Cross tab with percentage and sub totals

[Kim321]

Hi,

I'm trying to create a cross tab with the following structure:

State Product Revenue Margin Margin%
---------------------------------------------------
State1 Product1 1000 200 20%
State1 Product2 2000 500 25%
State1 Product3 0 0 0%
---------------------------------------------------------
Sub Total State1 3000 700 23%
---------------------------------------------------------
State2 Product1 1500 250 16%
---------------------------------------------------------
Sub Total State2 1500 250 16%
---------------------------------------------------------
Total 4500 950 21%

I am able to calculate the percentage for each products, but the sub total and total adds the percentage up so for sub total state1 adds up to 45% instead of 23%

Is it possible to do this with a cross tab?

I'm using Crystal for Enterprise 4.0

 

[lbass]

I don't know what you mean by "Crystal for Enterprise 4.0"--never heard of that. What version appears in Help->About Crystal->Product Version?

In your sample above, are the non-bold rows at the detail level or are they also summaries?

-LB

 

[Kim321]

Ok, version is Crystal Reports for Enterprise 4.0.

The non-bold rows are detail level. I made the bold ones since they are sub-totals and totals. But it doesn't matter what they are, I just need to get the report as shown.

btw. I got months across the columns as well...forgot to include that. The months will be from January to December, but depending on when the report is run it will show from January up to the current month.

So the layout is:
January 2011 February 2011
State Product Revenue Margin Margin% Revenue Margin Margin%

 

[lbass]

Okay, I researched this to determine that this is a new version of CR (similar to CR 2011), and I am not familiar with the functionality. In CR 2008 or CR XI, I would have suggested the following. I assume you have inserted a crosstab and that you currently have the desired layout. To fix the percentage, select the subtotal and grand total summary for Revenue and then right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar rev := currentfieldvalue;
false

Repeat the process for the margin totals and enter:

whileprintingrecords;
numbervar marg := currentfieldvalue;
false

Then select the current percentage for subtotals and grand total->right click->format field->common tab->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar marg;
numbervar rev;
if rev = 0 then
"0%" else
totext(marg%rev,1)+"%" //1 for one decimal

-LB

 

[Kim321]

Thanks a million for this! It works great!