Extracting Top n number of rows 1

[Swathi37]

Hi All,

I have a question on extracting the top n number of rows say top 100 rows from a report using Cognos Impromptu. Ex:

The report has

Customer# #ofUsers
---------------------
123 100
298 89
471 70
288 53
500 8

If I have to pull top 3 rows, I should have only the following output

Customer# #ofUsers
---------------------
123 100
298 89
471 70

I guess I have to use rank function. How do I use this function. Please advice.
Thanks so much
Swathi

 

[DoubleD]

You'll need to create a Rank column, then set your Summary filter to Rank <= 3.

Pain is stress leaving the body.

DoubleD

 

[Swathi37]

Thanks so much for your response. DoubleD, Could you please explain this process in detail?

 

[DoubleD]

1. Create a calculated column using the Rank function under the Summaries folder to rank the chosen column.
2. On the Filter tab, switch to Filter Type = Summary.
3. Add the filter: [Rank Column] <= 3
4. Run Report

Pain is stress leaving the body.

DoubleD

 

[Swathi37]

Thank you so much for your help.

 

[GilMerc]

Hi,

Easier of that, you can specified a number of row you like to extract.

You have an acces tab in menu REPORT-QUERY (I think so it's a right traduction, I have a french version. Otherwise, first sub-report of report menu). In this tab, you can specify a number of row. It's more easy and more efficient than other solution because when you use a calculation column Impromptu must evaluate all rows and if you result have many rows it will be long to complete it.

Gilles.

 

[DoubleD]

GilMerc,
If you only have to grab unique records and sort them, your solution is good.
My solution assumes that there is aggregation required.

Pain is stress leaving the body.

DoubleD

 

[GilMerc]

if selection of top n rows is a complex condition it's probably more efficient to use a calculation field but most of times we can do this by grouping and sorting the rows.

I remember your solution.

Gilles.

 

[monsky]

Can I create a ranking by displaying, say, the top 5 selling products, then the rest are grouped as 'Others'?

 

[DoubleD]

I'm not sure how you could do that with Impromptu. My initial thought would be one report that identified the top 5, then a second report which used the first to calculate a column identifying Top 5 vs Others.

I am what I am based on the decisions I have made.

DoubleD

 

[GilMerc]

Monsky,

I think you can do this by using view.
Define a view on DB for table where you extract top 5 rows and others. I think you'll need to define a new column where you set a rank (1,2,3,4,5,99 for others).
In the report you can group rows by this new rank.

Gilles.

 

[monsky]

I was hoping to perform this dynamically through Impromptu but since it may not possible I just decided to do this in PowerPlay.

 

[drlex]

Could you not just drop the filter on rank and put in a summary column for rank over 5 and use conditional formatting to hide the rank over 6 and change the displayed columns to show "Others" and the totals for the hidden rows on row number 6?

soi la, soi carré