Filtering for duplicates in Report Studio?

[bucketfoot]

Hello,

I am new to the world of Cognos and am trying to figure out how to identify duplicate records within a Report Studio query, so that I can remove them in my final output.

My data set has multiple lines for a given item (Serial_Num) and I only want to include the one for each item that I am identifying as the highest priority (using a calculation that looks at multiple fields and ranking them 1 to 5).

My thought process had been to pull the base data in one query and sort it first by the Serial_Num, then by my calculated priority. I had hoped to use a second query that would use the prevMember function to create a duplicate flag for me that I could then use to filter out the unwanted records (did this in the past using the Previous function in Crystal Reports). However it does not look like the prevMember function works in this fashion

I guess my ultimate question is, is there a function that will allow you to compare fields from one record to another? If not, does anyone have any suggestions on how to handle this?

Thanks

 

[blom0344]

prevMember is used for dimensional models (DMR or cube packages) A relational model (based on SQL) will not think in terms of previous (or next) The previous function from Crystal reports was probably based on a client function (i.e. executed against the data on the client)

Cognos Report Studio has 2 types of filters: before and after aggregation. The latter will be executed by the Cognos server AFTER fetching all data from the database. That's were you need to filter for the applied ranking..

Ties Blom