cancel
Showing results for 
Search instead for 
Did you mean: 

help needed again on Sub Total Report

Former Member
0 Kudos

Dear all, I have created sub total in my report by introducing group headers (Grouped by Reason Code).

Everything is fine however, the places where I have used formulas to calculate averages, the sub totals are getting cumulative.

For example


Reason Code - Description - Jobs - Lenses - Averages
01
02
03
45
50
60
89
99

Sub Total ---                  12        xx       xx          8

100
123
134
159
187
199                                                                 
Sub total          xxxxx    30        xx                    85

In the above example the range 100 - 199 has total of 77 not 85. The formula I am using calculates the average sub totals in cumulative manner.

Formula I am using to calculate the sub total for averages is


whileprintingrecords;
Numbervar FAvg;

Please note: FAvg is declared in my formula to calculate the averages and to get total sum I used the above formula under Group footer.

How can I re-use this formula so that it only counts the total averages from within that group order specified?

Many thanks

Regards

Jehanzeb

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Jehanzeb Navid,

In order to get rid of cummulative sum you need to reset the value in each group header like

whileprintingrecords;
Numbervar FAvg:=0;

Regards,

Raghavendra

Former Member
0 Kudos

Yes! Yes! and then suppress them so that it doesn't show 0.00 on the group header.

Why didn't I think of that!!!!

You are the man! - 10 Stars!!

Regards

Jehanzeb

Answers (1)

Answers (1)

Former Member
0 Kudos

Its not working. It resets the counter however it does not reset it on each group.

For example first set of group = 53=8, it shows 8 however in the next set it shows 2 whereas it should be 134+3..=79.

Former Member
0 Kudos

Could you please explain me how exactly you are calculating the sum or average by placing the formulas in group headers, details section and group footer?

Former Member
0 Kudos

Yes! sure. I think that is needed here otherwise it doesn;t make sense.

My Report is laid out like this


Reason code - {Group header 1}
Reason Code - {Group Header 2} - Description - {Database field}
Group footer 1 - Subtotal Total Jobs, Total Lenses,Total Averages
Group footer 2 - Total Jobs, Total Lenses, Total Averages

Here is the screen shot which explains much better then typing the whole book here.

[SCREENSHOT|http://i46.photobucket.com/albums/f115/jaytheguru/Groupingproblem.jpg]

Im sure that will make more sense.

Regards

Jehanzeb

Former Member
0 Kudos

Thank you for posting a brief description. If possible I would also nee the screenshot of the preview by making the calculation in red color so that i can see where they are placed. I think you are not getting the correct total in report footer?

Also can you paste the formula how you are calculating the average?

Regards,

Raghavendra

Edited by: Raghavendra Gadhamsetty on Sep 2, 2008 6:30 PM

Former Member
0 Kudos

Here is the results preview.

The total on the footer Grand total is fine. Its the sub total which is playing up (Cumulative).

[SCREENSHOT2|http://i46.photobucket.com/albums/f115/jaytheguru/Groupingproblem124.jpg]

Many thanks

Regards

Jehanzeb

Former Member
0 Kudos

It seems like everything is fine. Do you want to reset the value for each GH2 or GH1. I think it would be easier if you could see the suppressed sections also and the reset value so that you can see how it is calculating and where exactly the calculation is breaking. Did you place the reset value in GH2 if you want to reset for each GH2 or else place in GH1 for resetting at GH1 level?

Regards,

Raghavendra.G

Former Member
0 Kudos

I think you need to reset the sum value for each group and the avg value.

Can you paste the formula here?

Also can you attach the report with saved data here?

Edited by: Raghavendra Gadhamsetty on Sep 2, 2008 7:21 PM

Former Member
0 Kudos

I have put global numbervar (RESETS) on Report header.

What I want to reset is the number Group Footer 1 (Group header 1).

When I put reset on Group header 1, it resets the counter and shows the results pretty weirdly.

For example now

Sub total Stage 1 (5+3 = 😎

Sub Total Stage 2 (347....=24 instead of 97).

For some reason it is not getting the right result when I reset the counter on grouping level.

I do not wish to reset at the Group header 2 level, although I have tried that too but that makes the sub total to count only last number. So for example if 5+3 = 8, the report shows 3 as 3 is the last number in the list.

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Sep 2, 2008 6:24 PM


Whileprintingrecords;
Numbervar JAvg:=0;

This is I am using on the Group header 1 reset.


Whileprintingrecords;
Global Numbervar JAvg:=0;

This on the report header.

I have tried taking report header one out but it display the same results.

Former Member
0 Kudos

can you attach the report with saved data here?

Former Member
0 Kudos

Can you paste the formula(JAvg) evaluating at details section level?

Former Member
0 Kudos

Morning Raghavendra,

Sorry for delayed answer I went home later last night.

Here is full formula

Report Header


Whileprintingrecords;
Global Numbervar JAvg:=0;

Group Footer 2


Whileprintingrecords;
Numbervar JAvg;
Numbervar myaverage=0;
Numbervar CAvg=0;
myaverage := {#Total Jobs};  //RunningTotal count of Jobs
CAvg:= myaverage /{@WorkingDays}; //Working Days formula
JAvg:=JAvg + CAvg;
CAvg

Group Footer 1


Whileprintingrecords;
Numbervar JAvg;

Report Footer


Whileprintingrecords;
Numbervar JAvg;

Working Days formula


WhilePrintingRecords;
Local DateVar Start := currentdate;
Local DateVar End := currentdate;
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start -
dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0)  +
(if DayOfWeek(End) = 7 then -1 else 0);  

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays<i> ) in 2 to 6 and
Holidays<i> in Start to End then 
Hol:=Hol+1 );

Weeks + Days - Hol;

That is everything, Holidays formula contains only holiday dates which grows upto year 2020. I don't think I need to put that here.

Regards

Jehanzeb

Former Member
0 Kudos

I think you are using JAvg for both cummulative sum in group footer1 and sum in group footer 2. If you can use two different sums for GF1 and GF2 and initialize the variable for each GH2 gives no cummulative sum at GF2 and initialize at GH1 gives cummulative sum of that group in GF1.

I hope you understood!

Raghavendra