Top and bottom 10 records

[Sarayucog]

Hi all Gurus,

I am using Cognos 8.4 version.

I have model that is modeled with relational query subjects and dimensional query subjects.

I have a list report with 8 columns. In that first column is Global. This only one column is from the dimensional query subject and remaining are relational query subjects.

Here I want to find out the top and bottom 10 records. I have used Rank function, its giving 10 records.

Here my problem is if I drill down on global it should give 10 records for each Area and then if I drill down on Area it should give 10 records for each and every country, but in my report its not giving like this. Please give me the straight answer how to do this. Its very urgent....

Your help will more appriciated....


Thanks in advance

[MFGF]

Hi,

It's never a good idea to mix data from dimensions and query subjects in the same reporting object - my (albeit limited) experience is that it can lead to unpredictable results.  I think my approach would be to rebuild the entire list using objects from the dimensional model, then use the topcount() function to grab the top 10.  You should then be able to manipulate your drill behaviour to preserve this as you drill from one leve to the next.

Regards,

MF.

[Sarayucog]

Thanks for your quick reply.

I have used this logic, but I have a problem with this.

Actually in my report I have a list and chart report objects. These 2 should show the same results. By using topcount() function I am getting the correct results in list but the chat is not reflecting with the same values. Thats why now I am trying with Rank or running-count functions.

Please suggest me if you have any idea by using rank or running-count functions.

Thanks

[MFGF]

I think you should be focussing on why the topcount() function doesn't work in your chart. What do you mean when you say it doesn't work - do you get more than 10, or an error, or a different 10 from the list, or... ?

MF.

[Sarayucog]

Thanks for your reply....

I am getting only 10 records collecting from all the countries in the chart, but it list I am getting 10 records for each country.

Ex: let us assume I have 5 countries in my DB. I am getting 10 records for each county ( Total 50 records ), but in my Chart report I am getting only 10 records collecting from all these countries.

Thats why I am not using topcount() function.

As you said in you first reply I have used Preserve and replace expression settings also in my report.

Thanks

[MFGF]

Ok - I think I see.  You are not looking for the top 10 countries, but instead the top 10 somethings within each country based on a measure?  What are you grabbing the top 10 of, by the way - staff members?

Whatever, you will need to nest these below the countries when you drag the data items into the chart.  If it helps, think of your chart as functioning in the same way as the data in a crosstab - if you want the top 10 values within the context of something else (ie top 10 staff members within country), you would use Country as the outer nested columns and a Top 10 Staff calculation as the nested row heading within the countries.  The same applies in a chart.

Regards,

MF.

[Sarayucog]

As I told I am not using the cross tab. My objects are List and Chart.

First of all I am not getting the proper result in my list report object itselt as I told in the immediate below reply, but you are concentrating on the chart.

I am getting 10 records in list, but its giving only first country's 10 records. Its not displaying the remaining country's records.

Please let me know first how to get the top 10 records country wise in list and we will try with chart.