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
rporter45Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mlmccConnect With a Mentor Commented:
Sorry the assignment should use := not =

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

mlmcc
0
 
mlmccCommented:
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
0
 
rporter45Author Commented:
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
0
 
rporter45Author Commented:
Thank you very much mlmcc!!

This worked like a charm :)

HMK
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.