Impromptu - Return Last record in a subgroup 1

[techsmith]

I need to list training posts at different organisations. I can group on organisation and post. How do I restrict the report to only show the last trainee in each post? I have the start dates for each trainee.

Thanks

 

[griffindm]

SimsDba,

It depends. You can do it two ways, a hard method and an easy one. I'll assume that the last trainee is determined by the most recent start date. For the easy way: sort on start date(ascending) and put the simple column trainee in the group footer for post, that trainee will be the last one in the group based on sorting. You can hide the detail info if you want. The harder way is to try to filter on the maximum start date within your groupings. It's actually not much harder. Just add a calculated column defined as Max(Start_dt) associated to the posting, and then add a summary filter like this:

start_date = Max(Start_dt) ...

The report will then only return the last trainee in each post.

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

 

[techsmith]

Thanks Dave. We managed to crack this a different way. The report is based on a SQL select rather than using the builder. We added a where clause based on a SQL substatement - effectively doing the filter thing on Max(started).

Is there any way of accessing the builder tools (filtering etc) when you are working from a SQL select? I guess this is a different posting, but it would tie this up wuite nicely.

Thanks

Andy