Omitting the rank of data item in the report

[inu]

Hi
Country         sales      rank
-----------       -------     ---------
India              400         1
USA                300         2
UK                  300         2
Canada          200         4
Belgium          100         5


I think, u might have gone through this situation , i want from cognos to display the rank appropriately, but it is wrong result which i m getting, i googgled it , but not found the solution,
The rank for canada should dispay as 3, but cognos dont do this, it is inernally counting each row as different rank, that's why it is giving the output in this manner,.

Please you guys , solve this problem. I will be thankful to you.

Regards
Inam

[BigChris]

I think that's just the way it is. Canada isn't really 3rd...it is 4th. It's just that USA and UK are joint 2nd. If UK was 290 instead of 300 (in your example) it wouldn't alter Canada's ranking...she'd still be 4th.

[inu]

Hi
As you are saying, if UK was 190 instead of 300, cognos will display Canada in 4th position, but actually it is not so, right,:
You are giving the assumption, that is right. but, is it correct ranking in real time?. I have to display the correct ranking,.
Is it possible.
Please I need the solution, help me out,

Thanks
Inam

[BigChris]

Ermmm...no. In your example data that you gave, if the UK was 190 it would be ranked 4th and Canada would be 3rd. My point is that in your example data Canada is correctly ranked as 4th. UK and USA are correctly ranked as joint 2nd.

[inu]

Hi
By mistake I typed 190 instead of 290, I just repeated your assumed sales_amount for UK=290 ,whatever you said in the previous post. you should understand the point, what I wanted to say. Don't confuse youself and me as well, don't go outside of the topic itself.
By the way, from my first post point of view, if cognos not showing the 3rd position ranked, it doesnot mean that we would ignore the situation. Our common sense wants all the ranks continuously.
I m not saying that cognos is doing wrong anything.I know cognos internally treating UK as 3rd rank, that's why giving the output as 4th position for Canada.
How can I consider 4th position for Canada. our common sense donot accept it at all. Though it should come in the 3rd position.

Please buddy, don't mind. I don't want to taunt you. Tell me the solution, please

Regards
Inam

[bdbits]

BigChris was not going "outside of the topic itself". He gave you a perfectly valid response. Despite your repeated assertions the Cognos result can be considered correct and "common sense". Cognos is using "Olympic ranking" also called "competitive ranking" as demonstrated here, for which 1224 is a perfectly valid result. Some prefer 1223 or so-called "dense" ranking, which seems to be what you want. But Cognos has not implemented it that way. The result is not wrong, just different than what you want.

So, you will need to come up with your own expression or method for ranking. Depending on where your data is coming from, there may be a way of calculating rank the way you prefer it using functions at the data source, but they are vendor-specific. Perhaps if you reply with your data source, and the type of model (relational, dimension, etc.), an answer can be found. At the moment, a pure Cognos solution does not immediately come to my mind.

[inu]

Hi
I apologise to bigchris for my misconception towards him.
How to use dense ranking to get my desired result.

please u guys tell the steps , so that i can reach to required result.

Regards
Inam

[navissar]

Hi,
First of all, I don't think Inam meant any disrespect towards Chris. It's a terrible thing, really, that international communication is done in English. For most people, English isn't their first language, so sometimes intents aren't clear. Anyways, all's well that ends well.

Inam, Cognos does Olympic Ranking. Have you ever watched the Olympic games? If two swimmers, for example, make the exact same time and are the first two to arrive, then they're both 1st place and get a nice gold medal, and there's no one in second (So all in all only 3 medals are given - two golds, one bronze). That's reasonable, but it is understood this isn't what you are after.
Now, as bdbits suggested, some databases can do what is called Dense Rank (Which means 1223 rather than 1224, you can read about dense rank here http://technet.microsoft.com/en-us/library/ms173825.aspx
If your database supports dense rank, you can use the DB function to achieve what you are after. A simple Google search will tell you whether your database version supports dense rank (Simply search "<database vendor> dense rank", e.g. "Oracle dense rank").
Alternatively, you could let us know which database you're using, and I promise to check for you.

[BigChris]

And incidentally, no disrespect taken or even assumed...my skin's thicker than that! I've just been a bit busy today so I ahven't had a chance to reply. Dense ranking is a new one on me - I've always used "olympic" ranking, whether it's in Cognos, Access, Excel or just about any other tool you care to mention.

[navissar]

I always say that dense rank is correctly named; it is a bit dense...In most cases, Olympic rank is the way to go. However, in some cases, dense rank is necessary. An example is when the rank is used for an external calculation mechanism. Here's a real-life example: A certain company decided to hand out sales bonuses (On top of commissions) to its top 5 employees in each branch monthly, for a period of a few months, as a pilot. So, if a salesperson was 1st in her branch in sales, on top of her x% commission she would get an extra 1000 NIS (Roughly $280, 200 GBP/Euro). Now, of the top 5 in each branch, sales bonuses were handed out like this: the 1st employee (The best selling in branch) would get 1000NIS, the second would get a bonus of 800 NIS, the third would get 600, the fourth would get 400 and the 5th would get 200.
Here, ranking mechanisms matter. Consider:
salesperson|monthly sales|rank|Dense Rank|Bonus according to rank|Bonus according to DR
Clara          |600              |1    |1                |1000                            |1000
Marcus        |550              |2    |2                |800                             |800
Jessica        |550              |2    |2                |800                             |800
Nadia          |500              |4    |3                |400                             |600
Michael       |450               |5   |4                 |200                            |400
Johnny        |400              |6    |5                |0                                |200


Now, this is a question of management. Management decided that in a scenario like this, they would like to bonus the top 5 unique results rather than the 5 people on top, and that they would like the third-best unique result to get the bonus appropriate. So dense rank it was...The basic question is whether you're ranking entities or unique results, really.

[inu]

Hi Nimrod Avissar
I m using oracle 9i. I searched in google, i got the dense rank syntax , but i didnot this function in cognos if i use dense_rank() in cognos, it shows error message.
Can you please confirm that i can use dense_rank() in cognos or not, if possible then please hard code in the reply.



Thanks
Inam

[navissar]

you could perhaps use the {} syntax, though I doubt it because the OVER(PARTITION BY ORDER BY) syntax will be difficult to use with data items. This is a case where I would use customized SQL - Just drag in an SQL query and write the query for the report.