Print Page | Close Window

Running Total 2 Evaluate conditions?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14135
Printed Date: 04 May 2024 at 8:09pm


Topic: Running Total 2 Evaluate conditions?
Posted By: JFinzel
Subject: Running Total 2 Evaluate conditions?
Date Posted: 19 Aug 2011 at 11:32am
I have been searching around and seem to not be able to find someone who had a similar case to what I need.

I have a report with lines like so:
Gift Date          Amount          Type            Gift ID
5/7/10             100                Cash             25456
5/7/10             100                Cash             25456
6/8/10             200                Pledge           25868
6/8/10             200                Pledge           25868
8/30/10           350                Cash             28497
8/30/10           350                Cash             28497

I want a running total that:
(1) Evaluates on change of Gift ID
(2) Only evaluates if Gift type = Cash

I want to make the same kind of running total for "Pledge" and other gift types, so I can have running totals side by side at bottom, i.e.:

Total Cash        Total Pledge            Total Support
450                   200                         650

Any help here?  I don't know how in "use a formula" to enter both of these conditions.

Thanks!


Replies:
Posted By: comatt1
Date Posted: 19 Aug 2011 at 11:55am
Do you have something messed with your join that is duplicating your output?

if you can resolve that it would be easy

type of summary = sum
field to summ = amount

evaluate

use form ... {Type}='Cash'


Posted By: JFinzel
Date Posted: 19 Aug 2011 at 11:59am
It is not a join problem, and some of the lines are not duplicated.  It cannot be easily fixed.  This is happening because this is from gifts running out of Raiser's Edge, some of which are credited to both spouses, some of which are credited from businesses to two spouses, etc.  There are too many factors to try to eliminate, so it is definitely best to reset it on gift ID.  Thanks though.

Posted By: DBlank
Date Posted: 22 Aug 2011 at 4:18am
use an RT with an evlauate formula
Next(table.giftid)<>table.giftid and and type='cash'

Posted By: JFinzel
Date Posted: 22 Aug 2011 at 4:52am
DBlank:

Well I did not get an error, but it is not adding correctly.  Not sure if it makes a difference but I actually need: type in ["cash", "pay-cash", etc...]

This is what I have:
Next ({CnPrt_1Gst_1CnGf_1.CnPrt_1Gst_1CnGf_1_Gift_ID})<>{CnPrt_1Gst_1CnGf_1.CnPrt_1Gst_1CnGf_1_Gift_ID} and {CnPrt_1Gst_1CnGf_1.CnPrt_1Gst_1CnGf_1_Type} in ["Cash", "MG Pay-Cash", "Pay-Cash", "Recurring Gift Pay-Cash", "Stock/Property (Sold)"]

Posted By: DBlank
Date Posted: 22 Aug 2011 at 4:57am
Looks correct...
a few things,
RTs do not work in headers, they only work in details or footers.
I didn't know if you were subtotaling at groups so I did not give you a reset location as that can also impact your process.
 
to see hat is not working place the RT on the detail section and watch it change per row to see which rows it is acting incorrectly on
You should have something like...
 
5/7/10             100                Cash             25456   100
5/7/10             100                Cash             25456   100
6/8/10             200                Pledge           25868   100
6/8/10             200                Pledge           25868   100
8/30/10           350                Cash             28497   450
8/30/10           350                Cash             28497   450

Posted By: JFinzel
Date Posted: 22 Aug 2011 at 5:02am
Before you sent your reply, I figured out my problem.  First, I think I actually want a Previous formula because I want it to count the first of the duplicates, not the second (this is because, in some cases, one of the duplicate gift IDs shows a gift amount of $0.00).

Now here is my problem: It is not adding the first number, i.e. the one that has no previous value.

How do I include this first gift, or rather, how do I include the first gift that has the correct gift type?

Here is what I have now (I switched it around but it's the same):

{CnPrt_1Gst_1CnGf_1.CnPrt_1Gst_1CnGf_1_Type} in ["Cash", "MG Pay-Cash", "Pay-Cash", "Recurring Gift Pay-Cash", "Stock/Property (Sold)"] and Previous ({CnPrt_1Gst_1CnGf_1.CnPrt_1Gst_1CnGf_1_Gift_ID})<>{CnPrt_1Gst_1CnGf_1.CnPrt_1Gst_1CnGf_1_Gift_ID}

Posted By: DBlank
Date Posted: 22 Aug 2011 at 5:15am

Using Next and previosu can get a little odd on first and last record as it is trying to evaluate NULL values on either end of the data set.

First try in the formula editor in the RT evlauate formula there should be an option to pick list box that says 'Exceptions for Nulls'. CHange that to 'Use default values for Nulls'
 
Did that correct it?

Posted By: JFinzel
Date Posted: 22 Aug 2011 at 6:23am
Perfect!  Thank you once again.  Yes, I've noticed changing to default values for nulls often can fix running total issues.


Print Page | Close Window