NEED TO SHOW MOST RECENT DATE & HIDE OTHERS

[mamarockstar]

I have a report where I need to show the most recent date and suppress the others. It's a historical date field that keeps track of staffing dates. It looks something like this:

Client ID Staffing Date
12345 1/5/03
12345 3/19/04
12345 1/7/05
12345 2/10/06

According to this report, I only want the "2/10/06" entry to show. I tried this formula: {staff.date}=next({staff.date}), but it didn't work.

I'm using CR 8.0 with a Cache database.

Thanks for your help!

 

[lbass]

Go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date})

If you really mean the most recent date within a group, it would be:

{table.date} = maximum({table.date},{table.groupfield})

If you have the option of creating a SQL expression, you could also do that and use it to return only the most recent date to the report (no suppression necessary). You would insert a SQL expression {%maxdate}:

(select max(A.`date`) from table A
where A.`groupfield` = table.`groupfield`)

If you have no group, then you would remove the where clause. You would have to adjust the SQL expression to use the punctuation appropriate to your datasource.

Then you would go to report->edit selection formula->RECORD and enter:

{table.date} = {%maxdate}

-LB