on 09-02-2008 4:25 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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.
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
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
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.