Is it possible to pull the most recent record for an employee

[Hanzelmans]

If we are writing a Report Studio report and it is currently returning five rows of data for Employee A, would it be possible to only return the record with the most recent date and exclude the other four records?

Steve Hanzelman
Milton Hershey School

http://www.mhs-pa.org

 

[blom0344]

Yep,

build a dataitem : rank([date] for [employee])
use this in a detail filter , like:
rank([date] for [employee]) = 1
with the filter property set to 'after auto aggregation'

Ties Blom

 

[Hanzelmans]

Hi Ties,
Thanks for your response.

I am having problems with the Rank function. I cannot get it to rank the individual employee data. It will rank the date for the entire data set.

Thanks,
Steve

Steve Hanzelman
Milton Hershey School

http://www.mhs-pa.org

 

[blom0344]

That is strange. I tested my suggestion prior to posting and it worked out for me. In my case both date and employee where non-facts, identifiers as such. My test database was on a SQL 2005 server and Cognos 8.1 the reporting tool.

Ties Blom

 

[sln007]

Steve,
Create a calculated column, named MaxRec, as " MAXIMUM([date] for [employee]) ".
Then create a filter: MaxRec = [date]

This will ony show those row(s) where the [date] equals the hightest date for the [employee].

SLN