Calculating the SUM of formula field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=4483
Printed Date: 06 May 2024 at 2:18am
Topic: Calculating the SUM of formula field
Posted By: wafaa
Subject: Calculating the SUM of formula field
Date 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??
|
Replies:
Posted By: BrianBischof
Date 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 http://www.amazon.com/exec/obidos/ASIN/0974953601/bischofsystem-20 - Amazon.com or reading the http://members.crystalreportsbook.com - 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> |
Posted By: wafaa
Date Posted: 08 Oct 2008 at 10:13pm
| Thank you very much for explaining in details. |
Posted By: BrianBischof
Date 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> |
Posted By: wafaa
Date 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 |
Posted By: kvwielink
Date 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. |
Posted By: wafaa
Date 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 + mailto:%7b@Early_Time1 - {@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
|
Posted By: kvwielink
Date 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. |
Posted By: wafaa
Date 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 |
Posted By: kvwielink
Date Posted: 09 Oct 2008 at 2:56am
| Try X := Time(0) |
Posted By: wafaa
Date Posted: 09 Oct 2008 at 3:17am
| X mailto:+%7b@Early_Time1 - +{@Early_Time1 } it gives me error "Numeric Overflow" on this section |
Posted By: wafaa
Date Posted: 09 Oct 2008 at 3:23am
|
Below are my formulas:
Early_time1 formula:
if ( mailto:%7b@MAXEXITTIME - {@MAXEXITTIME } <= mailto:%7b@TimeExit1 - {@TimeExit1 }) then
( mailto:%7b@MAXEXITTIME - {@MAXEXITTIME }- mailto:%7b@TimeExit1 - {@TimeExit1 }) / 60
else 0 ; Late_time1 formula:
if ( mailto:%7b@MINENTRYTIME - {@MINENTRYTIME } > mailto:%7b@TimeEntry1 - {@TimeEntry1 }) then
( mailto:%7b@MINENTRYTIME - {@MINENTRYTIME }- mailto:%7b@TimeEntry1 - {@TimeEntry1 }) / 60
else 0; MAXEXITTIME
DateTimeToTime ({#MAXEXIT})
MINENTRYTIME
DateTimeToTime ({#MinEntry})
Time_Entry1 formula:
timevalue (08,00,00)
Time_Exit1 formula:
timevalue (15,00,00)
and the rest formulas for totals I took do it like you did exactly and when I run the report it gives me the previous error
|
Posted By: kvwielink
Date Posted: 09 Oct 2008 at 3:40am
|
Hmm, you can try X + time({@early_time1}). Otherwise I'm also not sure. Don't really have a lot of experience with time functions, but the formula's I gave you worked well for me so far for numeric values and string values. |
Posted By: wafaa
Date Posted: 09 Oct 2008 at 3:47am
|
What if I want calculate the total for each employee alone?
Each page will display the attendance for certain employee and at the end of this page, total of the min. he's late or went early.
In my report it shows the same but the total till now I can't do it correctly
|
Posted By: kvwielink
Date Posted: 09 Oct 2008 at 3:58am
| Sent you a pm with my email address. Please send me a screenshot of the design view. |
Posted By: wafaa
Date Posted: 09 Oct 2008 at 4:13am
| I've already sent you a screen shot to your e-mail add. please check it |
Posted By: kvwielink
Date Posted: 09 Oct 2008 at 4:15am
|
Noticed that you have a lot of headers and footers. This might complicate matters. Can you show me the design tab as well, instead of the results? Or better yet, send me the report file. I'm off home for the day, but should have some time to look at it tomorrow. |
Posted By: wafaa
Date Posted: 09 Oct 2008 at 4:37am
|
Ok, I'll send it to you now, thank you so much for your help
Waiting for your remarks
|
Posted By: wafaa
Date Posted: 12 Oct 2008 at 1:09am
|
Thank you for your effort, I tried the report you modified and the strange thing is that one record is calculating the total correctly and the other one is not and so on, please check the attached file, in your e-mail, is it something wrong with the equation or the place of it? |
Posted By: joeyg13
Date Posted: 25 Mar 2011 at 7:42am
|
Wow, "Crystal Reports builds reports in two passes. The first pass does basic reading of data and simple formulas. The second pass performs complex formulas 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 problem, create a running total formula which uses a global numbervar to add the numbers as they are printed."
Thanks Brian. I just read more in your book. Now it makes sense how/why I could not sum/count on certain fields that are the result of other sum/counts or shared variables from subreports. I see what you explain above. However, for my task at hand I am working with 20+ data fields from secondary calculations. (Accessing tables with one-to-many relationships; therefore a need for complex rules to only extract certain data.) Thus, this current project requirement is BEYOND the capability of Crystal. |
Posted By: Keikoku
Date Posted: 25 Mar 2011 at 8:05am
| I think with sub-reports it would be possible. Sub-reports seem to be an option when you need to do complicated calculations. |