Creating Impromptu filter for most recent record (using date) 1

[anicca]

Hi all,

I am new to Impromptu, but have taken several Impromptu reporting classes, and I have a question that is stumping me, but that I’m sure must be able to be done.

Here is the business requirement:

- Display the most recent employee attendance for a particular training class.

Which sounds like it should be very simple to do…I have my nice prompts that retrieve all of the available classes and that let the user choose to view attendees for only one class or for all classes and everything works great….

…except that I am retrieving multiple records for employees who have attended the same class more than once, which is a common occurrence.

Here is an example:

John Smith has taken the Fire Watch training class on 2/17/2002 and also on 6/14/2004. Both of these records are appearing in my report, when only the most recent record on (6/14/2004) should be showing up.

The problem I am having is trying to figure out how to filter my data set to remove all but the most recent record for each particular training class/employee combination.

I have grouped my report by Course Code and Badge No and I’ve done a count…so now when I have more than record for an employee, I can see how many there are. But I’m not sure what the next step should be to remove the older records.

Here are the columns in my report that I’m working with:

Dept
Employee No
Employee Name
Course Code
Course Completed Date
Count (calculated field that I described above)

Any suggestions on how I should proceed would be very much appreciated – thanks!

 

[griffindm]

anicca,

You must group your report, with either Employee No or Employee Name (if Employee Name is Unique) at the lowest level of grouping. Then create a summary value for Max(Course Completed Date) Associated to the above grouping. Last step is a summary filter on Course Completed Date = Max(Course Completed Date). Then you should have the result you are looking for.

Hope this helps.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[anicca]

Hi Dave,

Brilliant! That worked like a frosted Lucky Charm - my users will now be able to have magically delicious most-recent-training-class-attended records....for which I am sure they will undoubtably give thanks next Thanksgiving. (Okay, that's a bit of a stretch, but still, they will be Happy Campers.

Thanks very much for sharing your pearls of wisdom!
Leonie (aka annica)