Rank MDX Function in Report Studio

[Anantula]

Hi,

We got a new requirement to find a rank of a site within state, any ideas?

Our report has 2 prompts, State and Site (Which has cascade state)
The report is trying to find rank of user selected site with in the entire state. (3 columns exists State, Site, Revenue)
for example, VIC state has 20 sites.

If user select state prompt as VIC, Site as Melbourne. We have to display the rank of only Melbourne.

We can achieve it very simply when there is no Site prompt.

I got up to here:

State           Site             Revenue        Rank
VIC              Geelong      2000              1
VIC              Melbourne   1000              10
VIC              Frankston    1500              6
VIC              St Kilda         500              20
---- ---- --- --- - - - - -- - --
----- - -- - - - -  - - - - - -  - -

Our report has to display only one row, when the user select State as VIC, Site as Melbourne:
State           Site             Revenue        Rank
VIC              Melbourne   1000              10

We are using SSAS Cube as Cognos source and I need MDX function for this report.
Is this possible, any ideas please?

Thank you so much.

Regards,
Anan

[Rahul Ganguli]

Hi Anan,

How are you calculating the ranks, if you are calculating them in MDX/Cube directly this is not going to resolve the issue. Calculate rank on cognos end create a new Dataitem with cognos rank function in it.

Regards,
Rahul

[MFGF]

Hi,

I would build this as follows:

1. State is a Query Calculation with the expression [Your state level here] -> ?pState?
2. Site is a Query Calculation with the expression [Your site level here] -> ?pSite?
3. Revenue is the measure from your cube
4. Rank is a Query Calculation with the expression rank([Revenue] within set children[Your State Query Calculation]))

I just tried this using the Great Ourdoors Sales En powercube, and it worked as expected.

Regards,

MF.

[Anantula]

Hi MF,

Thank you for your reply.

Unfortunately, I couldn't able to implement the same in my query and really would like to see your sample report with Great Outdoors powercube.
Could you please send me xml of the report if possible?

That would be a great help for me.
Thank you.

Regards,
Anan

[Anantula]

Hi MF,

Thank you for your input.

I did implement as per your suggestion and I can see the rank as always 1 as it is filtering the State, Site.
Any ideas, pls help me out?

Thanks,
Anan

[Anantula]

Hi Rahul,

I am calculating Ranks from report only by adding a new data item. But if you see my query, I want a rank site within the state when user selects the State, Site. Any ideas?
Thanks,
Anan

[CoolP]

Hi Anantula,

Here you go. I followed MF. Spec Attached.


Cheers
Prasan

[MFGF]

Thanks Prasan! I would have posted, but you beat me to it :)

Anan, is there any reason why you can't use this approach in your report?

MF.

[Anantula]

Thank you so much MF and Prasan. I was able to fix the report.

[CoolP]

Hi Anantula,

If you follow the spec that I attached earlier, you should be able to achieve it. Attached are the screen shots to illustrate how it is working for me. Let me know if you need any help.


Thanks
Prasan

[dheerajsega]

Hi,
     I have kind of similar situation. The scenario is to get the rank of an item based on some calculated % of qty for a state for a specific time period.  For example, display the rank of the item for a month, for a particular state based on the % of the qty (calculated ) shipped.  So for every month we need to know the relative ranks of the items for each state . In out filter we have to select one item and the report has a section to display the rank of that item, for that month in various states it might have shipped.

The following syntax  has errors, not sure how to fix it to get the required output.

rank( qty % tuple(month) within set [item],[state]).
The parent level of state is Country and parent level of month is rolling 6 months.

Please suggest.

Thanks

[dheerajsega]

Any ideas pros?