Rank function returning duplicate values

[cognosuserindc]

Hello,

I am a relatively new Cognos 8 user.

I am trying to create a report that returns the top 5 foreign languages used during court cases.  The filter I am using is: rank ([Count Of Case])<=5

When I run the report 5 values are returned, however, the top language/value is returned twice (i.e., Spanish 10,000; Spanish 10,000; French 17,000; Dutch 15,000; Arabic 13,000.

Any suggestions as to what I might be doing wrong?

Thanks in advance!

[rajasekharbathala]

try this..
rank(distinct [Count Of Case])<=5
make filter as After auto aggregation
thanks..

[cognosuserindc]

I was hoping that would work.  Unfortunately, it now returns 6 languages, with the 1st and the 6th being the same language but with different case counts.

Any thoughts?

Thanks.

[AussiePete2011]

hi there,

Try capturing the SQL and running this against the database.  I find it easier to work out whats occurring if I have the SQL in front of me.
Add the SQL to the forum and we'll see what it's doing as there may be a valid reason for this behaviour.

Cheers
Peter

[Stejawh]

Hi,

Try to Rank the measure you want, then in a seperate Data Item Perform a Running Count against the Rank Data Item.

Ie:

Rank(Measure)

Running-count([Rank])

Then Filter by:

[Running Count]<=5 and set this to After Auto Aggregation