Ranking in report studio from cube

[jasmine0619]

Hello Cognos gurus,

I need some help with ranking function from a cube datasource. I need to calculate a customer's rank within a region but without bringing in all the customers in the context.

What I mean is lets say a customer Jsmith is ranked 3rd in that region but I want to calculate his rank but without bringing in all the customers.

is there a MDX or tuple like function that I can use to calculate the above requirement.? Rank(REVENUE WITHIN CUSTOMERSET)?

If I do Rank ({REVENUE} for Report) and have the customer in the query, then it takes a lot of time and I want to filter on the customer and the minute I put the filter the ranking gets screwed up because it only brings back data for that customer which is normal behaviour.

Any help would be greatly appreciated.

[TheCognosDave]

Hello jasmine0619,

I do all my rowset logic in the database using SQL.  Ranking is a great example of why !  to assign the rank value you need access to all rows in the ranking partition, but only want to report on the ones you care about right ? :-)

I would use a SQL something like this, which takes advantage of the derived table concept in the database.

          SELECT L.*
          FROM (
                    SELECT CUSTOMER_ID, CUSTOMER_NAME, RANK () OVER (ORDER BY REVENUE DESC) as Customer_Rank
                    FROM <CUSTOMERS_TABLES>
                    ) L
          WHERE L.CUSTOMER_ID='Jsmith'

Good Luck jasmine0619 !

(Now here come the comments from people who don't like SQL ...... :-P)

[jasmine0619]

Thanks so Much Cognosdave but unfortunately I am going against a cube data source.

I have used Oracle Dense_Rank functions before to achive similar results.

Is there any way I can write a similar MDX query against a cube. I have done a lot of searches on this subject but came up empty.

Again any help would be greatly appreciated.

[TheCognosDave]

DENSE_RANK() in oracle would work as well as regular RANK(), it's just a question of whether or not you want to skip any numbers.

I'm sorry I don't have any experience doing this with an MDX Query :-( ... just plain ol' SQL.  :-[

[Lynn]

Perhaps TopCount is the function you are after? You shouldn't use rank against a dimensional source.

[jasmine0619]

Hi Lynn,

Thanks for replying. I dont think TopCount would help me because TopCount gives me the list of the Members which meets the criterria. Here I am interested in the Actual Rank of the Customer within the community.

I am assumming that similar to Tuple which gives (X,Y) on a slice. I should be able to derive the rank of the customer within a level in a Hierarchy. I dont have a choice but to go against the cube. SQL would have been so easy.

regards
Jasmine

[cognostechie]

Jasmine -

I just created a report with Products in Rows, Canada as a country in the column and Quantity as the measure. I did the ranking using the MDX Rank  to rank the Products for Canada and this is how I did it. Too bad I can't post screenshots or attachments. The moderators should do something about this.

Create a query calculation and insert this:

rank(currentMeasure DESC tuple [Canada] WITHIN SET [ProductID])

It ranked the Products for a certain country (Canada in this case) and shows the rank for every Product. This is similar to what you need. Replace the Product with Customer and Canada with your region.

[cognostechie]

and by the way, if you use Rank the way I used then it is perfectly fine to use it against a Cube source. 'Rank' when used with the SQL syntax should be used only against the relational source but if it is used the way I used then it acts as an MDX alternative.

[Lynn]

Thanks techie!! Good to know....the all-powerful "within set" is the key I suppose.

[jasmine0619]

Hi CognosTechie,

You are true to your name. Your Suggestion worked. Thanks so much.

I am able to derive the rank of the customer without having the customer in the MDX query ( Just in the filter expression).

I knew there had to be away. I wish I could give you Gold as I do on Reddit. Maybe a suggestion to the Moderators -:)

Regards
Jasmine

[cognostechie]

That's so nice of you Jasmine. I am happy it worked for you.