Cross tab advanced calculations 2

[jchewsmith]

Is there a way to add a column to a cross tab to create a calculation of the total and an inputed formula?

Cross tab the budget is a inputted amount based on sales for Jan, Feb, Mar, Apr
div1 sales div2 sales div3 sales total New calculated column
Jan 500 200 100 800 Formula of budgeted sales (1000) less total of 800 so display 200
Feb 200 100 50 350 budget 500 less 350 so display 150
Mar 300 200 100 600 budget 700 less 600 so display 100
Apr 700 100 100 900 budget 800 less 900 so display (100)

 

[jchewsmith]

Column1 column2 column3 column4 Column5 Total Budget Total
July
$9,017,580 $17,659,983 $4,868,445 $4,215,323 $4,492,042 $40,253,373 This field equals an inputted budget # for July less the Total for July
August
$5,823,131 $26,925,942 $3,983,186 $7,820,524 $5,088,941 $49,641,724
September
$5,830,349 $21,485,297 $3,805,019 $6,420,305 $4,682,246 $42,223,216
October
$4,451,164 $17,464,080 $5,599,409 $6,029,141 $5,687,803 $39,231,597
November
$4,041,249 $16,581,585 $3,847,243 $4,717,000 $4,341,604 $33,528,681
December
$3,741,116 $10,657,690 $2,441,764 $3,751,979 $3,216,431 $23,808,980
January
$5,102,203 $12,867,593 $3,350,294 $2,498,894 $3,179,772 $26,998,756
February
$5,672,371 $16,698,689 $4,051,667 $5,516,871 $4,027,190 $35,966,788
March
$7,916,508 $25,187,858 $5,221,223 $7,360,574 $5,747,146 $51,433,309
April
$7,930,279 $22,706,092 $8,240,979 $8,539,161 $5,679,809 $53,096,320
May
$14,483,810 $33,678,359 $8,169,225 $10,083,476 $6,452,539 $72,867,409
3

 

[lbass]

You can add the total field a second time and then go to the crosstab expert customize style tab and select "horizontal" summaries and "show labels". In preview mode, select the (new) inner column and column label and suppress them and then minimize the width by grabbing and decreasing the margins. You will then see the row total twice at the end of the row. Edit the text of the label for the new column to say Budget Less Total. Then select the total->right click->format field->display string->x+2 and enter:

whileprintingrecords;
currencyvar budg := 0;

budg := (
select month(gridrowcolumnvalue("table.date")) //replace "table.date" with your actual date field, replacing the brackets with double quotes as shown
case 1 : 100000 //enter the actual budget figures here for each month e.g., 250,000 for 100,000, etc.
case 2 : 200000
case 3 : 300000
case 4 : 400000
case 5 : 500000
case 6 : 600000
case 7 : 700000
case 8 : 800000
case 9 : 900000
case 10 : 1000000
case 11 : 1100000
case 12 : 1200000
);
totext((budg-currentfieldvalue),0);

Newer versions of CR have an ability to add calculated fields in some way, but I have not learned the method. I think Ken Hamady has a paper on the newer crosstab functionality if you want to look into it.

-LB

 

[IanWaterman]

Like LB I am old school and have not used calculated members very often.
I have booked marked this youtube explanation as it is a good guide to basics and will get you started

https://www.youtube.com/watch?v=RpAEJqFe4IY

Ian

 

[jchewsmith]

LB - This worked perfectly and thank you so much for always being so knowledgeable and helpful!

One thing is that my vertical grand total for the new column is still adding up to the same as the original column. Do you have another formula that will correct this?

 

[lbass]

Change the existing row-level display formula to the following by adding the two lines shown:

whileprintingrecords;
currencyvar budg := 0;
currencyvar sumbudg; //add this line

budg := (
select month(gridrowcolumnvalue("table.date")) //replace "table.date" with your actual date field, replacing the brackets with double quotes as shown
case 1 : 100000 //enter the actual budget figures here for each month e.g., 250,000 for 100,000, etc.
case 2 : 200000
case 3 : 300000
case 4 : 400000
case 5 : 500000
case 6 : 600000
case 7 : 700000
case 8 : 800000
case 9 : 900000
case 10 : 1000000
case 11 : 1100000
case 12 : 1200000
);
sumbudg := sumbudg + budg; //add this line
totext((budg-currentfieldvalue),0);

Then select the grand total of the calculation column->right click->format field->display string->x+2 and enter:

whileprintingrecords;
currencyvar sumbudg;
totext(sumbudg-currentfieldvalue,0);

-LB