asked on

Crystal Reports - Finding date where a Certain Giving Level has been reached

Hi There

I am trying to get the date that a donor has reached a certain level and am not really sure how to approach it.

Here is what I have:

Date        Amount      Total Giving
01/26/2006 $200.00      $200.00
03/01/2007 $100.00      $300.00
07/14/2008 $200.00      $500.00
09/24/2009 $200.00      $700.00
10/29/2009 $200.00      $900.00
10/06/2011 $200.00      $1,100.00
12/04/2012 $500.00      $1,600.00
10/17/2013 $500.00      $2,100.00

The Total Giving column is a running total formula, while the Date and Amount columns come directly from the data.

What I need to do is report on the date where giving has reached the $1,000+ threshold.

How can I get the 10/06/2011 date to appear in the report footer?

This seems like a fairly simple issue, but I just can't wrap my head around how to get this information out....

Any help would be greatly appreciated.

Thanks!
HMK

Mike McCracken

Try this idea

In the report header add a formula

WhilePrintingRecords;
Global DateVar dtReachedLevel;
dtReachedLevel := Date(1900,1,1);
""

Open in new window

In the detail section add a formula

WhilePrintingRecords;
Global DateVar dtReachedLevel;
If {#RunningTotal} >= 1000 and dtReachedLevel = Date(1900,1,1) then
    dtReachedLevel = {YourDateField};
""

Open in new window

In the report footer add a formula

WhilePrintingRecords;
Global DateVar dtReachedLevel;
If {#RunningTotal} >= 1000  then
     dtReachedLevel;

Open in new window

mlmcc

rporter45

ASKER

Hi mlmcc

I have tried this and am getting the result of "01/01/1900" for all records. Each field in the detail is showing as empty...

I updated the formulas as follows:

Details formula became:

WhilePrintingRecords;
Global DateVar dtReachedLevel;
If {#Total Giving} >= 1000 and dtReachedLevel = Date(1900,1,1) then
dtReachedLevel = {CnGf_1.CnGf_1_Date};
""

Report Footer formula became:

WhilePrintingRecords;
Global DateVar dtReachedLevel;
If {#Total Giving} >= 1000 then
dtReachedLevel;

Thanks!
HMK

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

rporter45

ASKER

Thank you very much mlmcc!!

This worked like a charm :)

HMK