Solved

Crystal Reports - Formula - Sum field based on field value

Posted on 2014-11-13
9
1,716 Views
Last Modified: 2014-11-14
I need to programmatically calculate a grand total.  I have a PostTax field which contains either a 1 or 0.  I also have an amount field.

So, if the PostTax field contains a 1 I need to sum all values and it can result in a negative value so I need to default it to 0 if that happens.  

Then I need to add the above result to the sum of amount where PostTax = 0.

I'm trying to write this in a formula.  This is in a subform and the only purpose is to return the grandtotal.

Any ideas?
0
Comment
Question by:CipherIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 40441446
What have you tried?

You could use a running total with the Execute When set to Post Tax = 1

Another way would be to have a formula like
If {PostTax} = 1 then
   {Amount}
Else
   0

Open in new window

Then do a summary on that field

mlmcc
0
 
LVL 1

Author Comment

by:CipherIS
ID: 40441743
Ah, I did taht but didnn't do the summary.  I actually did something like this

Don't have exact code but it's somehting like this

Dim PostTaxAmt as double
Dim PreTaxAmt as double
Dim GrandTotal as double

if postax = 1 Then
  PostTaxAmt = PostTaxAmt + Amount
End If

if pretax = 0 Then
  PreTaxAmt = PreTaxAmt + Amount
End If

If posttaxamt < 0 then
  posttaxamt = 0
end if

If pretaxamt < 0 then
  pretaxamt = 0
end if

grandtotal = postaxamt + retaxamt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40441861
That should work.

How did you display them?

mlmcc
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 35

Expert Comment

by:James0628
ID: 40442309
I see some problems with your formula, but some of it may just be something that was lost in translation, since that's not your actual formula.  But I do see one basic problem with what you seem to be doing.

 The formula is presumably being evaluated for each record (and Amount is a field in the record).  When you check your posttaxamt and pretaxamt totals to see if they're negative, you're looking at the total as of that record.  The problem with that is that if a total -- at that point -- is negative, you reset it to 0, which then throws off the total from that point on.  Say you had records with 5, 10, 15, -50, 20.  If you add all of those together, you get 0.  But with a formula like the one that you posted, the total will be 30 on the 3rd record, and then -20 on the 4th, which the formula will change to 0.  Then it adds 20 for the last record, and you get a total of 20, instead of 0.

 Presumably you don't want to check for a negative total until you have the final total (ie. after the last record has been read).

 Then again, without seeing your actual formula, and knowing where you're using it, and knowing about anything else that might be manipulating your variables, this is just guesswork.

 James
0
 
LVL 1

Author Comment

by:CipherIS
ID: 40442777
Here is the formula.  I'm using Basic Code.

dim posttax as double 
dim pretax as double 

IF {PayrollDeduction.postTax} = true Then
    IF SUM({PayrollDeduction.amount}) < 0 Then
        posttax = 0
    ELSE
        posttax = SUM({PayrollDeduction.amount})
    END IF
END IF

IF {PayrollDeduction.postTax} = false Then
    IF SUM({PayrollDeduction.amount}) < 0 Then
        pretax = 0
    ELSE
        pretax = SUM({PayrollDeduction.amount})
    END IF
END IF
    
formula = posttax + pretax

Open in new window

0
 
LVL 1

Author Comment

by:CipherIS
ID: 40442831
Above code is wrong.  This is what I have.

dim posttax as double 
dim pretax as double 
dim grandtotal
IF {PayrollDeduction.postTax} = true Then
    posttax = posttax + ToNumber(PayrollDeduction.amount)
END IF

IF {PayrollDeduction.postTax} = false Then
    posttax = posttax + ToNumber(PayrollDeduction.amount)
END IF

IF pretax < 0 THEN
    pretax = 0
END IF
    
IF posttax < 0 THEN
    posttax = 0
END IF

GrandTotal = posttax + pretax

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40443122
The question is, when do you care that the total is negative?
The order of the data should NOT affect the result unless you can control the order.

As you have your code, every time that formula runs it checks if the result of the intermediate sums is < 0, is that what you want or do you want to add ALL the pretax values then test for < 0

Where in the report is this formula?

mlmcc
0
 
LVL 1

Author Comment

by:CipherIS
ID: 40443213
The formula is in a subreport.  It seems to be working.  I'm still testing it.
0
 
LVL 35

Expert Comment

by:James0628
ID: 40443215
The last formula that you posted is still wrong.  If PayrollDeduction.postTax is false, you're still updating posttax, instead of pretax.  Can you copy and paste your actual formula, so that we can see exactly what it looks like?

 That aside, as I said before (and mlmcc also said), you're checking for negative totals after each record, which is probably not what you really want (for the reason that I described earlier).

 James
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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