Author |
Message |
wafaa
Newbie
Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
|
Topic: Calculating the SUM of formula field Posted: 08 Oct 2008 at 4:03am |
Shift Starts Entry Mins Late Shift Ends Exit Mins Early
08:00am 08:30am 00:30 03:00:00 02:50 00:10
08:00am 08:15 00:15 03:00:00 02:55 00:05
Total: 00:45 Total: 00:15
"Mins Late" and "Mins Early" are formula fields, I want to calculate the total for each at the end by writing SUM(@Mins_late) but i gives me error "This field can not be summariezed" why??? how can I calculate the total as shown above??
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 08 Oct 2008 at 10:22am |
Crystal Reports builds reports in two passes. The first pass does basic reading of data and simple formulas. The second pass performs complex fomrulas and summing data. When you get this error it means that the formula you are using has to be calculated in the second pass and since this is the same pass where the summing is done, it gets confused. It can only do sums on formulas that had their values calculated in the first pass.
To fix this prolbem, create a running total formula which uses a global numbervar to add the numbers as they are printed.
My encyclopedia book goes into detail about how the two-pass report processing model works, as well as how to use running totals to get around the problems it creates. You can find out more about my books at Amazon.com or reading the Crystal Reports eBooks online.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
IP Logged |
|
wafaa
Newbie
Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
|
Posted: 08 Oct 2008 at 10:13pm |
Thank you very much for explaining in details.
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 08 Oct 2008 at 11:33pm |
Glad to help.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
IP Logged |
|
wafaa
Newbie
Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
|
Posted: 09 Oct 2008 at 12:13am |
Please can you teach me how to do it in details step by step? I read a lot but it still not working with me
|
IP Logged |
|
kvwielink
Newbie
Joined: 09 Oct 2008
Location: Thailand
Online Status: Offline
Posts: 8
|
Posted: 09 Oct 2008 at 1:40am |
In the group header, place a variable defined as :
WhilePrintingRecords;
Global TimeVar X;
X := 0
Then, in the detail section, add the following formula:
WhilePrintingRecords;
Global TimeVar X;
X := X +{@mins_late}
Finally, in the group footer:
WhilePrintingRecords; Global TimeVar X;
This should do the trick, although I'm not 100% sure if it should be a timevar or numbervar. Give this a go and see if it works.
|
IP Logged |
|
wafaa
Newbie
Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
|
Posted: 09 Oct 2008 at 2:04am |
I tried to do it using these formulas but it give me the total as zeros.
I created "calc" formula and placed it in footer section:
WhilePrintingRecords; Global numberVar PageTotal; PageTotal := PageTotal + {@Early_Time1};
"reset" formula and placed it in header section:
WhilePrintingRecords; Global numberVar PageTotal := 0;
"print" formula and placed it in the page footer:
WhilePrintingRecords; Global numberVar PageTotal; PageTotal;
please tell me if there is anything wrong I'm doing here
|
IP Logged |
|
kvwielink
Newbie
Joined: 09 Oct 2008
Location: Thailand
Online Status: Offline
Posts: 8
|
Posted: 09 Oct 2008 at 2:29am |
I think you misplaced the calculation formula. This should be in the detail section. Basically what crystal does, is it takes the variable with value 0, and every time you pass a detail record, it adds the time of the record to the variable. So for the first record, it's 0 + @early_Time1 = Pagetotal. For the second record it's Pagetotal(old) + @early_time1 = PageTotal(new).
Once all the records have been processed, you want to show this in the page footer. That's where you put the "print" formula.
So the "reset" formula is in the header, the "calc" formula in the details, and the "print" formula in the footer.
See if that helps. If not, please specify your headers, detail and footers, and where you calculate which formula.
|
IP Logged |
|
wafaa
Newbie
Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
|
Posted: 09 Oct 2008 at 2:33am |
I tried your formulas and in this one:
WhilePrintingRecords; Global TimeVar X; X := 0 when I'm trying to save it, it gives me error that a time required instead of 0
|
IP Logged |
|
kvwielink
Newbie
Joined: 09 Oct 2008
Location: Thailand
Online Status: Offline
Posts: 8
|
Posted: 09 Oct 2008 at 2:56am |
Try X := Time(0)
|
IP Logged |
|
|