displaying top 5 of a column

[lahdeb]

Hello,

Is there a way to display top 5 of a column in cognos without using rank function (the rank function is not an optimal solution for cognos)

Thanks

[MFGF]

Hello,

Is there a way to display top 5 of a column in cognos without using rank function (the rank function is not an optimal solution for cognos)

Thanks

If you have a dimensional package, you can use the topCount() function, but this is a dimensional function so won't work with a relational package.

For Relational, you're looking at Rank() and an after-agg filter.

Having said that, I seem to recall Lynn managing to get a topCount() function to work in a crosstab over a relational package. I think this would need to be a CQM package, and I also remember it was not easy, but she might be along shortly to set me straight :)

Cheers!

MF.

Cheers!

MF.

[Lynn]

Crosstabs over a relational source spin up a little cube which lets you cheat and use some dimensional functions. I managed a period v period comparison at one point but don't recall trying topCount(). Maybe I did but it is a painful method and I wouldn't recommend going down that route.

What is problematic about rank()? It is the right tool for the job in my opinion. It is also possible to set a conditional style on list column body style to show only the desired rows, but this is seriously not optimal since it is all done in the rendering rather than the filtering.

[lahdeb]

Hello , thanks all for your reply ,

The problem is that I have a lot of data since there is no filter in my list :/ , so using rank() on one column forces cognos to do more calculation , Cognos have to attribute a rank to all my data and sort by desc , then display what I want :/.

I think the solution Given by Lynn is more sweetable for me , acting on the render of the list , but I don't know how can I hide all rows , and showing only the hight 5 values of my list  by using conditional style?

Thanks

[Lynn]

Hello , thanks all for your reply ,

The problem is that I have a lot of data since there is no filter in my list :/ , so using rank() on one column forces cognos to do more calculation , Cognos have to attribute a rank to all my data and sort by desc , then display what I want :/.

I think the solution Given by Lynn is more sweetable for me , acting on the render of the list , but I don't know how can I hide all rows , and showing only the hight 5 values of my list  by using conditional style?

Thanks

Create a query calculation using the running-count() function and associate this to your list. Then create a conditional style on list column body styles to set box type as none for running count greater than 5. I'm not sure this is going to perform well for you either based on what you describe.