If you are unable to create a new account, please email support@bspsoftware.com

 

How do I display a dynamic top 5 or top 10 records in an active report?

Started by pacificbeavs, 23 Apr 2019 10:55:29 AM

Previous topic - Next topic

pacificbeavs

I know how to use the rank function and display the top records for a standalone list or visualization (or even with data decks to generate multiple top 10 lists).  However, in this scenario my report has quite a few filters, and I'd like my visual to dynamically update the top 10 records being displayed depending on the filters selected.  I believe there are too many filter combinations (100+ combos) to do data decks, so I'm unsure of how to accomplish this.  Thanks!

ThatKingslayer

Do you have an example of what kind of data you are showing a Top 5 or 10 for and what kind of filters you are referring to?

Whenever I've done Top 5 lists in the past, I've had a main extract query which particular optional and required filters that pulls my main extract of data. Using the results of that, I have another query that calculates the Top 5 for a particular subject using the rank function. Because of the optional filters on the main extract query which supplies the top 5 query, if I were to apply a filter there because I selected a different product or product line, it would in turn impact the top 5.

Am I thinking of something completely different or is that kind of what you are talking about?

pacificbeavs

Quote from: ThatKingslayer on 25 Apr 2019 07:36:56 AM
Do you have an example of what kind of data you are showing a Top 5 or 10 for and what kind of filters you are referring to?

Whenever I've done Top 5 lists in the past, I've had a main extract query which particular optional and required filters that pulls my main extract of data. Using the results of that, I have another query that calculates the Top 5 for a particular subject using the rank function. Because of the optional filters on the main extract query which supplies the top 5 query, if I were to apply a filter there because I selected a different product or product line, it would in turn impact the top 5.

Am I thinking of something completely different or is that kind of what you are talking about?

I think that would make sense if the report wasn't active, but since it's active it's already coming up with the top 5/10 results and I can't re-filter it.  I'll stick with cars for my example...  My report currently displays the top 10 models (e.g. civic, accord, camry) based on # of vehicles sold.  However, off to the side I have filters for Make (e.g. Honda, Toyota) and even Salesperson.  I would like to be able to select a make and see the top 10, or a make and a salesperson, or any combination of those. I am unable to do that though since my report was filtered up front for the top 10 models.  The only way I can think of is data decks, which then Cognos would generate many different top 10 data decks for those scenarios.  I don't think that is an ideal option though.

MFGF

Quote from: pacificbeavs on 25 Apr 2019 03:51:37 PM
I think that would make sense if the report wasn't active, but since it's active it's already coming up with the top 5/10 results and I can't re-filter it.  I'll stick with cars for my example...  My report currently displays the top 10 models (e.g. civic, accord, camry) based on # of vehicles sold.  However, off to the side I have filters for Make (e.g. Honda, Toyota) and even Salesperson.  I would like to be able to select a make and see the top 10, or a make and a salesperson, or any combination of those. I am unable to do that though since my report was filtered up front for the top 10 models.  The only way I can think of is data decks, which then Cognos would generate many different top 10 data decks for those scenarios.  I don't think that is an ideal option though.

I can't see any option other than data decks for this. The filtering that can be done within the rendered output of an active report is very simple, and to be able to use things like rank() or topCount() would mean running a query - which you can't do as an active report is designed to be used offline.

If your report is of a reasonably small size, data decks could work for you - it might be worth trying?

Cheers!

MF.
Meep!