I use Cognos Impromptu to extract employee records. People often change jobs/hours/pay. Is there an easy way to get the latest information only, and ignore the older records. For example, if Joe Bloggs started work for our company as a Street Sweeper for 20 hours a week at £10 per hour, then on 1 September 2007 got promoted to Supervisor for 40 hours a week at £15 per hour, how do I get just his details for his current job of Supervisor?
If you have a field in the employee table that indicates something meaningful, such as the date the record was updated, you can use 'maximum' from the summary functions list in a summary filter in the report.
For instance, in the summary filter, something like this returns the most recent record:
DATE_UPDATED = maximum(DATE_UPDATED) for EMP_ID
(where DATE_UPDATED and EMP_ID are fields in the table that contain the indicated data) - your data tab should list the items you want to return from this table.