Extra rank column of olap grid

[bestgk]

Dear all:

I have set the grid as follows

Age Male Female Total
11-20 10 15 25
1-10 5 10 15
21-30 7 7 14
Total 22 32 54

There are Gender and Age dimension here.

If I want to add an extra column rank so that

Rank Age Male Female Total
1 11-20 10 15 25
2 1-10 5 10 15
3 21-30 7 7 14
4 Total 22 32 54

How i can achieve this view as rank seems not in the grid structure(interaction result from dimensions)..

Thanks very much expert!

bestgk

 

[MDXer]

Can you post your MDX so I can see what the query is doing?

Thanks


Paul

 

[bestgk]

i just connect the olap through crystal report...havent written any MDX code yet...

 

[MDXer]

I'm not sure how crystal reports does it, but what you want is farily easy to accomplish in MDX. Create a Calculated Measure that is the Rank (I assume you have done this already. Then by adding this Calculated measure to columns and using an Oder Function you can display the Rank value. Catution about using RANK If 2 Members are tied they will have the same rank value but the next item's rank value will be offset by the number of elements tied.

Example
RANK UNITS
---- -----
Item 1 1 100
Item 2 2 90
Item 3 2 90
Item 4 4 80

HTH

Paul

 

[bestgk]

For more complex grid:

column dimension include gender and marry status while column contain age group and the fact table containing data...

will it works?
Sorry i am not in office now so i cannot test it...really thx for your comment

 

[bestgk]

amendment

For more complex grid:

column dimension include gender and marry status while ROW contain age group and the fact table containing INCOME data...

will it works?
Sorry i am not in office now so i cannot test it...really thx for your comment

 

[MDXer]

The dimensionality should not inhibit the RANK function from working it may take some tweaking and playing with to get it right. Basing it of a giving Measure is typically easier depending on how your cube is set up. Sorry for my unfamiliarity with Crystal reports, as I typically do everything in Raw MDX.


Paul

 

[bestgk]

Male Female All Gender
M NM ALL M NM ALL M NM ALL
11-20 1 1 2 2 2 4 3 3 6
1-20 1 1 2 1 1 2 2 2 4
Total 2 2 4 3 3 6 5 5 10


M:Married
NM: Not Married
ALL: ALL marry status

If i want to add a new rank column at the leftest side according to the sorting order by the ALL Gender:ALL field, how can it be done?

 

[bestgk]

Dear PCorley thanks for your suggestion, this is my 1st day MDXing, hope we have chance to discuss on other areas in the future

 

[MDXer]

Here is a sample query written against food mart which unfortunately does not have an age dimension but you can swap our the dimensions and members as needed. This should be a good starting place for you.

WITH
MEMBER
[Measures].[Rank] AS
'
RANK([Education Level].CurrentMember,[Education Level].[Education Level].Members, Measures.[Sales Count])
'
Select
{
CROSSJOIN(
CrossJoin(
[Gender].[Gender].Members,
[Marital Status].[Marital Status].Members
),
{[Measures].[Rank], [Measures].[Sales Count]})
} On Columns,
{[Education Level].[Education Level].Members} On Rows
From Sales
Where ([Time].[1997])

Paul

 

[bestgk]

Dear PCorly

Thanks! I can run your sample, however, if I want the ranking follows the order of sales count instead of the education level order. is MDX support such queries AFTER the sales count calculation?

 

[MDXer]

The rank is based upon the sales count as they relate to the Dimension the Rank function takes is as follows
RANK(<<SET>>, <<Numeric Value>>) I'm not quite sure I understand what you mean by after the sales count order. You can rank any dimension against any measure.

Paul

 

[bestgk]

Hi Paul

Is your explanation meaning that if I specify
&quot;, Measures.[Sales Count]&quot;
of
&quot;RANK([Education Level].CurrentMember,[Education Level].[Education Level].Members, Measures.[Sales Count])&quot;

the ranking first sort the &quot;[Education Level].[Education Level].Members&quot; according to the &quot;Measures.[Sales Count]&quot;?

I asked because I got a wrong ranking to the sales count.

Thanks for your explanation

 

[MDXer]

yes that is what it does. if you got a wrong result set post it or you can send it to me. If you feel you need more detail and can't be more specific here you can email me your questions.

Paul

 

[bestgk]

Hi Paul

I have made a screen dump for the MDX Sample Application. How I can send the screen to u? Any convenience email address?

Thanks in advance!

 

[MDXer]

Nobley@hotmail.com

I'll look at it first thing in the AM.

Paul

 

[bestgk]

Hi Paul

I have sent u the dump. You are really hepful! I think mine should be stupid question, hope u don mind.