asked on

Crystal Reports Sum Question on Amount Field Help Please

Hello all. I have the following in a report. I have a view that shows me the following breakout.

BatchID - Group Header

Client CheckNo CheckAmount Apply AppliedAmt
KMart 4444 800.00 Pillow 450.00
KMart 4444 800.00 Bed 350.00
Cosco 54652 150.00 TV 250.00
Walmart 741325 310.00 Lamp 520.00

The problem I am having is I want to do a SUM of the Total(CheckAmount) on the DISTINCT CheckNo. I am getting 2060.00 as the total when it should be 1260.00. I cant use the AppliedAmt either because its possible the check amount on the report might be higher that what is shown for the applied amount. How do I handle this? I cant figure it out.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dabas

I suggest using a Running Total.
In the evaluate section of the running total you can specify that it should only evaluate when the checkno changes.

As long as the report is also sorted by CheckNo, it should solve your problems.

Please let me know if you need more help on how to set up your running total

Dabas

InvisibleMan

ASKER

Ok all here is where I am at thanks so much so far:

My report is setup as follows:

Group Header: Batch Date
Group Header: BatchID
Group Header: CheckNo, Client, CheckAmt
Detail: Client, CheckNo, CheckAmt, Apply Type, Amt Applied

Group Footer: BatchID Total # of Checks
Check Tot Actual
Total Applied
Unapplied

The problem I now have it Unapplied. I have a running total field as the Check Total Actual #RTotal0 which equals 1450.00. Total Applied is a Sum of th Amt Applied which in this case lets say its 1450.00. How do I get the unapplied. I tried using a formula with Sum(#RTotal0)-Sum of vwExtract Total Applied. Give me an error says it cannot create a running total.

Dabas

Sum(#RTotal0) does not make sense. #Rtotal0 already is a sum!

So, If I understand correctly, you have three totals in your footer:

Check TotActual, which you get from your RT.
Total Applied, which is the sum of AmtApplied

Why not simply state the Unapplied formula as

{Check TotActual} - {Total Applied}

Is this too simple, or am I missing something?

Dabas

SpideyMod

InvisibleMan

ASKER

No answers worked but you get the points due to an expert not realizing that many questions I have placed out on Expert Exchange have provided no answers. Thanks for your input.

Mike McCracken

Please don't take a attitude like that. If the answers or comments aren't working keep posting a comment to them. I currently have comments on probably several hundred questions. I can't keep track of which ones are still active especially after they get off the first page or so.

The main problem is a question that hasn't been fully answered that isn't being commented on by the questioner looks a lot like a question where you received the answer and are hoping the question will be lost or deleted so you can get your points back. Also some people go on unexpected trips and can't get back to the questions.

When you add a comment I get an email and then start thinking about the question again. I have found answers or further comments and examples for question but am not able to find them.

All you need to do is post something like
I will be out of touch for some period of time. I will reenergize this when back. OR
That suggestion didn't work It is usually a good idea to add more information. In the case of this question I was waiting for a reply to the last comment to see if that solved your problem.

I thought Dabas's formula would give you the answer.

Are you trying to get an overall total for the amount due, applied and remaining?

I am not clear as to what is left to do and what is and isn't working.

If you still need help let's continue the discussion either in this question or in a new question with low points. If you use a new question be sure to link this question to it.

Looking forward to helping you
mlmcc

Dabas

I agree with mimcc

Please be aware that we spend quite a lot of our FREE time answering questions.

I tried to help you, and suggested a workaround, but you did not respond to that.

You have to help us help you too! Without having the report in front of us, it is hard to determine what the cause of your problem is.

I also do not understand what you mean by

"you get the points due to an expert not realizing that many questions I have placed out on Expert Exchange have provided no answers."

Mike McCracken

References the moderator's list of questions he still has open.

mlmcc

SpideyMod

All C grades issued are being changed to A grades. InvisibleMan, if you have issue with this, please post a comment in Community Support and I will have another moderator or an admin look at my actions. If the answer didn't work, you should have opted to request a refund instead of accepting an answer which any one of us would have gladly assisted with.

SpideyMod
Community Support Moderator @Experts Exchange

Mike McCracken

InvisibleMan
I am ready to help if you still need it.

This forum is supposed to be for the interchange of ideas and expertise. I take a lot of pride in providing good answers.

In this case Dabas had provided what I felt might be the answer and had no further thoughts to extend the discussion. I was waiting for your reply to see if it worked and if not then what further assistance might be needed.

The problem we have, is many questions are asked and seemingly abandoned either with or without an answer.

The moderators are only trying to do a very difficult job of reducing the number of open questions without closing or deleting questions that are still under discussion. Your obligation is to keep the discussion going if by nothing else responding with I tried that and here is the result.

If you know you will be away for a period a comment can be added that you will return on DATE.

good luck
mlmcc

Netminder

Dabas,

Per mlmcc's comments above, I have transferred points for you to https://www.experts-exchange.com/questions/20541381/For-Dabas-re-20480517.html

InvisibleMan,

Expect email.

Netminder
EE Admin

Dabas

Thanks to both Netminder and mimcc

Dabas

Mike McCracken

Thank you Netminder. An excellent job and decision as usual.

InvisibleMan
As my earlier post stated if you still need help just ask.

mlmcc