• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

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
0
rporter45
Asked:
rporter45
  • 2
  • 2
1 Solution
 
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
 
mlmccCommented:
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
 
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now