Extract earliest date upon condition met in Crystal Reports XI

[rguittar]

Hello. I am working on a Finance Report where the report requests the date that an account goes into a credit balance status. There could be multiple transactions on the same account spread across different dates so it is important to pull the exact date the account dips below zero.

My groupings go like this:
1. Location
2. Account ID
3. Posting Date
4. Transaction #

In each, I have a running total to add up the transaction amounts and determine when the account becomes a credit balance (below 0).

I've come up with the following formula to try determining the necessary date: if {#RTotal2} < 0 then minimum({Command.TX_POST},{Command.HSP_ACCOUNT_ID}) else ""

I know this isn't correct but have no idea if it is a whileprintingrecords logic to try and pull that earliest date, but can't figure it out. Any help is HUGELY appreciated.

 

[lbass]

In what report section do you need to show the date? Could it be in a group footer? Please be specific about which group section.

-LB

 

[rguittar]

LB,

Yes, it is in the group footer since I am keeping it on the same line as the running total, which has to be in the footer section as well. I was trying to pull it on the Posting Date footer section.

BTW, I've read and used so many of your tips and tricks over the years so it is a privilege that you're answering this one for me.

Thanks.

 

[lbass]

Okay, so posting date is grouped in ascending order, correct?

Create a formula like this and place it in the detail section:

whileprintingrecords;
numbervar sumamt := sumamt + {table.amount};
numbervar cnt;
datevar crdt;
if sumamt < 0 then (
cnt := cnt + 1;
if cnt = 1 then
crdt := {table.date}
);
crdt

Then add a reset formula in the account group header (assuming you want this per account):

whileprintingrecords;
numbervar sumamt;
numbervar cnt;
datevar crdt;
if not inrepeatedgroupheader then (
sumamt := 0;
numbervar cnt := 0;
datevar crdt := date(0,0,0)
);

Add a display formula to the postdate group footer like this:

whileprintingrecords;
datevar crdt;

-LB

 

[rguittar]

Ok, I know it's very close, but barely off. When I implement the proposed solution, I'm running into a snag as follows ...

The display formula listed above is put in the GF3 with all the drilled data for the Posting Date. Here is an example of what I am getting:

Post Date Name Charges Cred Adj Dbt Adj Pmts
5/4/11 Smith 124.00 0.00 0.00 0.00
5/18/11 Smith 0.00 -124.00 124.00 0.00
7/14/11 Smith 0.00 0.00 0.00 -15.00
7/18/11 Smith 0.00 0.00 0.00 -15.00
7/20/11 Smith 0.00 0.00 0.00 0.00
9/16/11 Smith 0.00 0.00 0.00 -721.18

[carry over]

Post Date Running Total Cred Bal Date
5/4/11 124.00
5/18/11 124.00
7/14/11 108.00
7/18/11 93.00
7/20/11 93.00 7/20/11
9/16/11 -628.18 7/20/11


In this example, it's obvious to see that the date should be 9/16/11, but it is giving me that 7/20 date. I'm not sure if it is because of the line of 0's that is throwing it off? I don't know why the line of 0's is even there as I can't manage to get those filtered out either since I would want to see a line such as the line with the + and - 124.00 adjustments. Any suggestions? Thanks.

 

[lbass]

Please explain exactly where you placed each formula. I tested this, and it worked properly. The zeros should have nothing to do with it.

As a test, add a formula like this to the date group footer:

whileprintingrecords;
numbervar sumamt;

By the way, what did you use in place of {table.amount} in my formula? It should have been something like:

charges+Cred Adj+Dbt Adj+Pmts

-LB

 

[rguittar]

The detail formula went suppressed in the details. The account group header formula went suppressed in GH2. The

whileprintingrecords;
datevar crdt;
formula went in the GF3 section to display the date.

I used the transaction amount as your {table.amount} which was determined with an additional qualifier to separate charges, payments, adj.

I'll try changing it to the charges + pmts + adj to see if that works and pop your other formula to the date group footer.

-RG