Running total - subtotal and gran total level 1

[jraheja]

I am using CR10 and CE10. I have a summary field at header level that gives a summation and at the footer level I give a subtotal of the group. I had to do a running total since the header summary field has a formula calculation so I could not do a simple summation. Now at the change of group at header level I am resetting the running total to 0. I do want the total in the report footer i.e grand total so do I need another new variable in the 'Evaluate' of running totals that does a grand total??

I have 3 such totals which would amount to so many variables so this question. Sounds complicated so feel free to ask questions. Did not know how else to put it.

 

[synapsevampire]

you don't require a Running Total for this anyway, but since I don't know what you're doing, it's hard to advise.

In general post:

Example data
Expected output
Any formulas required to create the above

-k

 

[jraheja]

I have at the detail level data for the year
Transdate Transamount Club code
01/03/2003 $3500 012
01/06/2004 $2000 015

stringvar percent;
numbervar perc;

if Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) <> 0 AND Sum ({@YearEntered_YTD_ Sales}, {@Merge Club}) <> 0
then perc:= (Sum ({@YearEntered_YTD_ Sales},{@Merge Club})-Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}))/Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) * 100

This perc value is calculated based on 2 formulas that take the sum for current year and prev year. This gives the YTD prec diff. All the clubs are grouped as per their sales category. I want to total the sum of perc values for a group. Since this is a formula as shown above, the summation option was grayed and I am using running formula.
I have to do the above for Sales Per 100 Members and perc change per 100 members over prev years.

I can post the other formulas too. I am looking if there is an option to avoid using so many formulas. Simpler option... Thanks.

 

[lbass]

So you have one group on {@merge club}? Change your formula to the following and place it in the club group header or footer:

Whileprintingrecords;
numbervar perc;

if Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) <> 0 AND Sum ({@YearEntered_YTD_ Sales}, {@Merge Club}) <> 0 then
perc:= perc + (Sum ({@YearEntered_YTD_ Sales},{@Merge Club})-Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}))/Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) * 100

In the report footer, use:

Whileprintingrecords;
numbervar perc;

You shouldn't be using a reset it you are trying to get a grand total.

-LB

 

[jraheja]

I will have to define a new variable in addition to perc since perc takes the sum total for each club. Subtotal is for a group of clubs and that is why I was using reset. Grand Total is for all clubs. How will I achieve that if I have one perc variable. It is like a grand total variable..

 

[lbass]

Change the formula to:

Whileprintingrecords;
numbervar perc;
numbervar grperc;

if Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) <> 0 AND Sum ({@YearEntered_YTD_ Sales}, {@Merge Club}) <> 0 then
perc:= perc + (Sum ({@YearEntered_YTD_ Sales},{@Merge Club})-Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}))/Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) * 100;

if Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) <> 0 AND Sum ({@YearEntered_YTD_ Sales}, {@Merge Club}) <> 0 then
grperc:= grperc + (Sum ({@YearEntered_YTD_ Sales},{@Merge Club})-Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}))/Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) * 100;

Use the reset for perc:

whileprintingrecords;
numbervar perc := 0;

Place the following in the group footer:
whileprintingrecords;
numbervar perc;

Place the following in the report footer:
whileprintingrecords;
numbervar grperc;

-LB

 

[jraheja]

This takes care of subtotal and grand total but not of the club at header level which is what perc was doing... Correct???

 

[lbass]

Just create a separate formula that doesn't use variables to place in the group header:

if Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) <> 0 AND Sum ({@YearEntered_YTD_ Sales}, {@Merge Club}) <> 0 then
(Sum ({@YearEntered_YTD_ Sales},{@Merge Club})-Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}))/Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) * 100

-LB

 

[jraheja]

So basically I will need 2 variables - one for subtotal that can be reset and one for grand total that SHOULD not be reset but I can avoid the Running totals. Correct?

This is much better than creating so many formulas with running totals! Sometimes simple solutions skip the mind- Thanks a lot.

 

[lbass]

These variables are essentially manual running totals. P.S., for the reset formula, if you are using "repeat group headers on each page" for the group, you should add a clause so that it reads:

whileprintingrecords;
numbervar perc;

if not inrepeatedgroupheader then
perc := 0;

-LB

 

[jraheja]

I just realised for reset I have to include another formula. So in effect we are doing the manual running totals. The only diff. is the location of the summation of the values for subtotal and grand total. They will be in the actual formula v/s evaluate... Correct? Appreciate your help

 

[lbass]

I'm confused about what you mean. All of the formulas I have given you work without using the running total expert. You would create them in the formula expert.

-LB

 

[jraheja]

I mean we will use manual running totals using formula expert.....not the running total expert. But I will need 2 variables EACH for subtotal and grand total and a reset formula.
I have 2 other summations so I was trying to reduce additional variables. Thanks a lot for your help... It helped a lot... I am leaving now so will be back Monday in case of clarifications...