Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Calculating the SUM of formula field Post Reply Post New Topic
Page  of 3 Next >>
Author Message
wafaa
Newbie
Newbie


Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
Quote wafaa Replybullet 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 IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 IP Logged
wafaa
Newbie
Newbie


Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
Quote wafaa Replybullet Posted: 08 Oct 2008 at 10:13pm
Thank you very much for explaining in details.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 IP Logged
wafaa
Newbie
Newbie


Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
Quote wafaa Replybullet 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 IP Logged
kvwielink
Newbie
Newbie


Joined: 09 Oct 2008
Location: Thailand
Online Status: Offline
Posts: 8
Quote kvwielink Replybullet 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 IP Logged
wafaa
Newbie
Newbie


Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
Quote wafaa Replybullet 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 IP Logged
kvwielink
Newbie
Newbie


Joined: 09 Oct 2008
Location: Thailand
Online Status: Offline
Posts: 8
Quote kvwielink Replybullet 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 IP Logged
wafaa
Newbie
Newbie


Joined: 21 Sep 2008
Location: Kuwait
Online Status: Offline
Posts: 18
Quote wafaa Replybullet 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 IP Logged
kvwielink
Newbie
Newbie


Joined: 09 Oct 2008
Location: Thailand
Online Status: Offline
Posts: 8
Quote kvwielink Replybullet Posted: 09 Oct 2008 at 2:56am
Try X := Time(0)
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.032 seconds.