Only want to count date in last record

[mmwdmr]

I have a report (8.5) that is looks like this:

EXAMPLE 1
GROUP 1: Client ID: 222-22-2222
DETAIL:Updated:01/01/04 Moved: 12/20/03 Address: 123 Elm St
DETAIL:Updated 02/01/04 Address: 123 Elm St
DETAIL:Updated 03/01/04 Address: 123 Elm St
DETAIL:Updated 04/01/04 Moved: 03/15/04 Address: 111 Oak Ave

EXAMPLE 2
GROUP 1: Client ID: 222-22-2222
DETAIL:Updated:01/01/04 Address: 999 Maple
DETAIL:Updated 02/01/04 Moved: 01/20/03 Address: 111 Oak Ave
DETAIL:Updated 03/01/04 Address: 123 Elm St
DETAIL:Updated 04/01/04 Address: 123 Elm St

What I am trying to do is count clients that have a move date in their LAST address record - like Example 1. Client records may have an earlier move date but none in their last address record - Example 2. OR client records could have no move dates at all (which means they have never moved). I want to count client records like Example 1 but not Example 2.

Thanks!

 

[mbarron]

You can use a variation of the 3 formula running total to collect your data.

This one goes in the group header. You don't need it if you are looking to get a grand total (count). It is a reset for the variable. The field gets suppressed:

whileprintingrecords;
numbervar out:=0;

Put this formula in the details band to collect the count and then suppress the field:

whileprintingrecords;
numbervar out;
if onlastrecord and {cf.status}="moved" then out := out +1 else
if ({cf.name}<> next({cf.name}) and {cf.status}="moved") then out:=out+1 else out:=out;
out

Use this to display the data (in a footer)
whileprintingrecords;
numbervar out;



Mike

 

[mmwdmr]

I had a hunch there was an "OnLastRecord" function (I mean, there is an "OnFirstRecord", right) and I am totally familiar with the running totals setup. I will plug it in and let you know if it works - thanks!

 

[mmwdmr]

Strange - your formulas produced a total of 56. I didn't page through all 600 pages of detail but with a report that large, I knew the number should be higher. I tried this evaluation formula for the running total and it produced a higher number and the running total was easier to see in the report detail:

WhilePrintingRecords;
NumberVar moved;
OnLastRecord;
if {WO14323fromAM.MOVE_OUT} <> date(0,0,0) and Previous({WO14323fromAM.DE_TENANT_ID}) = {WO14323fromAM.DE_TENANT_ID}
then moved:=moved + 1

I managed to verify the total through a series of spearate cross-check reports. Thanks for the response though - it put me on the right track!

 

[Madawc]

Put a formula field in the group footer that teste the 'date moved'. E.g.
if isnull({Moved}) then "No move since last update
else Totext({Moved}, "dd MMM yyyy")

You could also count them using a running total. You'd need do a maximum-date summary total for the date updated for each group, and count only records with this date updated and not isnull({Moved})

Madawc Williams
East Anglia, Great Britain