Filtering on dates

[Cognoscat]

I am relatively new to writing Cognos reports (computer-literate accountant vs. programmer so bear with me).  I am trying to do a query where I extract the most recent labor distribution record for each active employee.  For example, I may have an employee who has four labor distribution records such as:

Jun 24, 1997 12:00:00 AM
Jan 01, 2003 12:00:00 AM
Sep 01, 2001 12:00:00 AM
Feb 16 2007 12:00:00 AM

Since I currently do not have a filter on the effective date field, my query brings me back all four of the above records and I only want the record that is still in effect, not all four.

The field I am extracting the effective date from from is [Employee].[Labor Cost Distribution].[EFFECTIVE_DATE].

How would I write my filter for this field to be able to extract the February 16, 2007, record only for this employee since that is the most current record?

Your help is sincerely appreciated.

[Lynn]

Try a filter like this:

Code Select Expand


[Employee].[Labor Cost Distribution].[EFFECTIVE_DATE]
=
maximum([Employee].[Labor Cost Distribution].[EFFECTIVE_DATE] for  [Employee].[Labor Cost Distribution].[EMPLOYEE])

I assume this is a relational data source? If you are using DMR or a cube then this might not be the ticket...

Good luck!

[Cognoscat]

Thanks Lynn.  Your filter worked to provide me with the most recent record.