Select only the lastest record

[robert693]

Hello,

I have a report with an ID field (Command.ID) and a date field (Command.CommentDate) as well as several other fields. The ID field can be null. Each ID can have multiple Date fields. I want to pull only records with the latest date field. So for:

ID Date
201 01/01/2015
201 01/15/2015
202
203 01/01/2015
203 01/06/2015
204 12/31/2014
205 12/25/2014
205 12/28/2014
205 01/14/2015

I would want to return:
201 01/15/2015
202
203 01/06/2015
204 12/31/2014
205 01/14/2015

I want to return records where the date field is null as well. I have grouped on the Date field and sorted the group descending and that did not work. I also added the following code to the Group Select Expert: Command.CommentDate} = maximum({Command.CommentDate},{Command.Id})
This worked but eliminated records where the Date field is null. Is there a way to do this?

Thank you for any help!

 

[kray4660]

Seems like if you grouped by ID and sorted by date (ascending), hid the details and put the ID and Date in the Group Footer that would work. Maybe I am missing something (You may want to hide the Group Header also).

 

[ianoctdec]

hi,

Try this in group selection formula:
(if isnull(Command.CommentDate}) then
true
else
Command.CommentDate} = maximum({Command.CommentDate},{Command.Id})
)

Dana