Solved

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

Posted on 2013-11-13
390 Views
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
Question by:rporter45
• 2
• 2

LVL 100

Expert Comment

Try this idea

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

In the detail section add a formula
``````WhilePrintingRecords;
Global DateVar dtReachedLevel;
If {#RunningTotal} >= 1000 and dtReachedLevel = Date(1900,1,1) then
dtReachedLevel = {YourDateField};
""
``````

In the report footer add a formula
``````WhilePrintingRecords;
Global DateVar dtReachedLevel;
If {#RunningTotal} >= 1000  then
dtReachedLevel;
``````

mlmcc
0

Author Comment

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

LVL 100

Accepted Solution

mlmcc earned 500 total points
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

Author Closing Comment

Thank you very much mlmcc!!

This worked like a charm :)

HMK
0

## Featured Post

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…