If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Manipulation of queries

Started by phoenixfire, 07 Oct 2009 06:46:36 PM

Previous topic - Next topic

phoenixfire

Hello,
I have a tricky issue to fix.
I have say, two queries, where each employee has multiple records with a different hire date for each record. I need to display only the earliest hire date which by the way is controlled by a sequence number with the max sequence no being the value for the earliest hire date.
How can I do this in cognos report studio using queries? ???

Eg.
Emp. No.      Seq.No.      Hire Dates
E01              5                 01-Jan-06
E01              3                 01-Jan-08
E01              1                 01-Jan-09
E02              4                 01-Feb-08
E02              1                 01-Mar-09
E03              1                 01-Apr-09

I need to display
E01              5                01-Jan-06
E02              4                01-Feb-08
E03              1                01-Apr-09

Thank you very much for your help.

dumbell

Hello,
Did you try max function.

dataitem:: max([Seq.No.] for [Emp. No.])

Then drag [Emp. No.],[ dataitem] & [Hire Dates] into object like list

Good Luck

phoenixfire

Thank you very much.
I used the data item like you suggested and put in a filter
seq no. = data item.
Worked like a charm!! ;D