Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: sum A based on field B condition Post Reply Post New Topic
Author Message
SAN2007
Newbie
Newbie


Joined: 23 Oct 2007
Online Status: Offline
Posts: 3
Quote SAN2007 Replybullet Topic: sum A based on field B condition
    Posted: 23 Oct 2007 at 3:25am
Hi there!  This is my first post and I am new on Crystal Reports.
 
I need your help on this:
 
I have
fields:
tableA.id (number)
tableA.date
tableA.qtyproduct (number)
tableA.year
 
tableB.id
tableB.date
tableB.condition (delivered, partial_delivered)
tableB.year
 
Relations: id=id, year=year (RIGHT JOIN)
 
I want to sum all qtyproducts with condition "delivered". I grouped by tableB.date, by quarter and month inside quarter. I tryed a formula:
 
if {tableB.condition} = "delivered"
then {tableA.qtyproduct}
else 0
 
and I place it in the detail section, then I summarized it and... I got the sum of tableA.qtyproduct that do not meet the condition "delivered".
What is wrong?Cry
 
Eg:
tableA.qtyproduct = 1 -- delivered
                             = 2 -- partial delivery
 
After the formula, I got 2 instead 1.
 
 Would you please help me? Confused
 
Thank you very, very much! (and thanks for the forum, it is great! Clap)
 
 
 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 23 Oct 2007 at 9:53am
At first I thought that I knew what your problem was, until I got to the end. Your formula looks okay, so I don't know why you are getting a summary on the partial delivery. Something must be slightly off b/c it looks like it should work.

Also, anytime you do summary formulas on groups, make sure you create your own formulas and put them inside the Details section. If you use running totals, then it ignores the group condition and summarizes all data (I discuss this on page 135 of my new book).

Glad you like the forum - please link to it on a blog or website so other people know about it.
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
SAN2007
Newbie
Newbie


Joined: 23 Oct 2007
Online Status: Offline
Posts: 3
Quote SAN2007 Replybullet Posted: 23 Oct 2007 at 4:33pm

Hi Brian!

Thank you for your prompt response. There is no way. I will keep on trying. If I find out what was wrong I let you know.
 
Thanks for your time Brian. I will get your book.
Sandra
IP IP Logged
wattsjr
Groupie
Groupie
Avatar

Joined: 25 Jun 2007
Location: United States
Online Status: Offline
Posts: 51
Quote wattsjr Replybullet Posted: 24 Oct 2007 at 10:01am
Hi SAN2007,
I agree with Brian that formula looks OK, but if I had this problem, I'd want to see what was actually happening in the formula.  So I'd do the following as a debugging technique.
 
1. Create 2 formulas. The 1st one (called D) would be like yours:
 
    if {tableB.condition} = "delivered"
    then {tableA.qtyproduct}
    else 0
the 2nd one (called P) would be the reverse of "D"
 
    If {tableB.condition} = "partial delivery"
    then {tableA.qtyproduct}
    else 0
2. Place both formulas in your detail along with {tableB.condition}
 
This should let you see what the {...condition} value is along with the results of the formulas.
 
As a side note, if you don't need to see 'detail' for "partial delivery" items, you could exclude them using the Select Expert (i.e. {tableB.condition} = "delivered")
 
Regards,
 
-jrw
IP IP Logged
SAN2007
Newbie
Newbie


Joined: 23 Oct 2007
Online Status: Offline
Posts: 3
Quote SAN2007 Replybullet Posted: 24 Oct 2007 at 4:09pm
Hi JRW!
 
Thaaaankkk you for your help. We all were right. I made it up!!!!
 
You will never believed what happened: there was an extra relation.  Donīt ask me why or what but there it was. When I realized, I removed the third relation and there we were! It works.
Thank you for your time and assistant.
I will be around.
Sandra
IP IP Logged
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.020 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.