Solved

Crystal Reports - How do I calculate the difference between two summary fields in a group footer

Posted on 2009-07-07
25
3,948 Views
Last Modified: 2012-05-07
Hi,

What should be incredibly simple has taken me hours, and still no closer.  I have two numeric summary fields in a crystal report, and i need a third field to show the difference (subtract) between them.

Have tried to figure out the formula, but when I use the field names with a "@" in front of them I get "Field is not known".

As these are summary fields in a footer section i cannot (for other reasons also) use the table values for this.

I know this must be simple - please help,

Dave
0
Comment
Question by:hymie42
  • 11
  • 7
  • 3
25 Comments
 

Expert Comment

by:cjonline
ID: 24794423
You would have to subtract the Summary fields from each other in a formula
ie.

Sum({Fieldname}) - Sum({2ndFieldname})

Craig.

0
 
LVL 77

Expert Comment

by:peter57r
ID: 24794430
Summary fields would show with a sigma prefix in the report fields and show like..
<sigma>Group#1:Customers.Country-A:Sum of Customers.LastYearsSales

To take one from another is no different to using any other fields , double click (do NOT type) the first one, type a minus sign and doubleclick the second.

0
 

Author Comment

by:hymie42
ID: 24794576
Hi and Thanks for the fast response.  I am glad to say i already tried this and it doesnt work, mainly because of the structure of the table I expect.

This is why i need to use the values shown and not the formulas, if that makes sense.

if i do this by using the formulas I get totals for the whole table rather than just the section - i am expecting values around 40-50 and get 50000 upwards instead.

is there not a way to directly access the value of the summary field?

Dave
0
 

Expert Comment

by:cjonline
ID: 24794590
can you post the formula you are using to calculate.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24794622
"As these are summary fields in a footer section"
If the summaries are standard Crystal summaries there is no reason at all why you can't take one from another.

If they are not standard crystal summaries then we are all in the dark here aren't we.
0
 

Author Comment

by:hymie42
ID: 24794719
Hi again,

i have attached a pic showing (hopefully) what I am trying to do.  i need to subtract (2) from (1) using the field values and place the result in the field to the right of (2).

Field names are (1) HoursBooked1 and (2) TimeSheethours and the result should go in field HourDiff1.

Hopefully this makes sense.

Have also attached the formula workshop view so you can see what the options are and maybe make suggestions from there.

I have used access extensively along with some other development tools, but must say I am finding Crystal very frustrating...

Regards,

Dave
ee.gif
ee1.gif
0
 

Expert Comment

by:cjonline
ID: 24794775
in the formula editor for time difference,, double click on the first Report Area (at the bottom of the  screenshot) then type '-' then double click on the 2nd Report Area (at the bottom of the screen shot)
0
 

Author Comment

by:hymie42
ID: 24794824
Tried that - it hightlights everything to the right of the Minus sign and says "The remaining text does not appear to be part of the formula".


ee2.gif
0
 

Expert Comment

by:cjonline
ID: 24794844
can you post your report with saved data?
0
 

Author Comment

by:hymie42
ID: 24794900
I would have to create a table, as the report gets its data from an access query in our clocking system, but as the report gets its data from the dataset associated with the form that the report is viewed on ,  it would be a bit messy to try do it that way. (its on a vb.net system)

Really frustrated on this as i have spent nearly the whole day - really apprecite your help:)

Any other way we can do this that you can think of?

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:cjonline
ID: 24794910
You can save the report with saved data in Crystal 10.. even if you can just post the RPT file it might help.
0
 

Author Comment

by:hymie42
ID: 24794914
I can post just the .rpt file if that will help.....

And some data in a csv or spreadsheet?
0
 

Expert Comment

by:cjonline
ID: 24794924
no just the rpt will be fine.. there is an option in Crystal when saving the Report to "Save Report with Data".
0
 

Author Comment

by:hymie42
ID: 24794952
Bah - tried to post it - says "The extension of one or more files in the archive is not in the list of allowed extensions: rptHoursAttendedVsHoursBooked.rpt"

Could email it?
0
 

Author Comment

by:hymie42
ID: 24794980
OK - changed extension to .txt - you will need to change it back again
rptHoursAttendedVsHoursBooked.txt
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24795075
Can't see a problem.
Updated file attached.
rptHoursAttendedVsHoursBooked.txt
0
 

Expert Comment

by:cjonline
ID: 24795098
I think you are suming some of the values in the qry then summing them again.. its a bit confusing.. Can you not just use the query to return the values to the dataset in an un-summed form first. it might help.
0
 

Author Comment

by:hymie42
ID: 24795147
Hi Peter,

Thanks for that - still gives the incorrect figures though - 168-110 does not give -2617, which is the problem.

Its is because I am joining several queries together for the data and using the underlying summary formulae takes all the data rather than just the value of the text field.

See attached hopefully this will clarify further.

Thanks for input - really appreciate having people to talk to when stuck like this:)
ee3.gif
0
 

Author Comment

by:hymie42
ID: 24795162
Hi cjonline,

There are multiple segments / groups, so returning anything unsummed to the dataset on the underlying form....not sure how that would work..

Regards,

Dave
0
 

Author Comment

by:hymie42
ID: 24795688
Hi mlmcc,

If you follow the thread above you will see that in the Crystal Reports section you DONT ALLOW us to upload an rpt file without much gymnastics, hence the email address - it wast a help by email, rather just a means to send a file for examination.

Maybe with this being the Crystal Reports section you could allow the transfer of Crystal files?

Regards,

Dave
0
 

Accepted Solution

by:
hymie42 earned 0 total points
ID: 24802064
Hi all,

Found the solution *blush* - needed to use the summary field (4th down) in the formula workshop instead of the lower down ones, and I positioned the field in the header section of the group - seemed to do the trick.

Thanks for the help guys - admin what do I do with points - appreciated the help so dont mind splitting between contributors even if I did eventually solve it myself?

Regards,

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now