Impromptu - Sub-Report for sub-select?

[Rattler27]

Product: Impromptu
Version: 7.1, delivered as part of vendor bundle

New to Cognos and Impromptu with this vendor. Learning to date is from time invested with support materials and this forum (very helpful!).

Trying to create an ad-hoc report for an HR function that will show "orphaned" Active employees (ex. if a supervisor vacates for any reason). Report to be run as needed or on schedule, to permit timely clean-up, audit and proper workflow.

Running against employee table, it seems like I first need to capture all employees (supervisors) in Terminated status and then run what amounts to a SQL sub-select against them to determine which Active employees are still pointing to those Terminated supervisors, since all employees (regardless of status) are in the same table.

-But I haven't figured out how to do this with Impromptu yet. This is a bundled package, so I cannot write my own SQL, I must use the user interface. This is probably a very easy filter for seasoned users. Thanks in advance for steps, tips and any guidance.

“Throw a man a fish and you feed him for a day; Teach a man to fish use proper filtering technique for this initiative and you feed him for life he won't bother you for a while.”

 

[griffindm]

Rattler,

Assuming all of the fact you need are in the employee table, what you need is a catalog self-join to the employee table. Add the employee table to a new catalog, then add it again using an alias name [i.e. Employee for Supervisor]. Join the two where the Empl.Supervisor = Alias.Employee. Then filter the output based on the supervisor row having a termination date or inactive status.

That should do it.

Regards,

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