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.
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)
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.
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. :-[
Perhaps TopCount is the function you are after? You shouldn't use rank against a dimensional source.
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
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.
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.
Thanks techie!! Good to know....the all-powerful "within set" is the key I suppose.
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
That's so nice of you Jasmine. I am happy it worked for you.