Hello,

I need help with rank mdx query:

This works ok:

WITH
MEMBER [Time].[R1] AS
'RANK(
Axis(1).Item(0).Item(0).Dimension.CurrentMember,
ORDER (Axis(1), ([Measures].[Profit],(Axis(0).item(0).item(0))), ASC)
)', SOLVE_ORDER=1

select
{([Time].[1997].Children), [Time].[R1]} on Axis(0),
{([Gender].[All Gender].Children)} on Axis(1)
from Sales


but I don't know how to do such query with two dimensions on Axis(1)

f.e.

WITH
MEMBER [Time].[R1] AS
'RANK(
Axis(1).Item(0).Item(0).Dimension.CurrentMember,
ORDER (Axis(1), ([Measures].[Profit],(Axis(0).item(0).item(0))), ASC)
)', SOLVE_ORDER=1

select
{([Time].[1997].Children), [Time].[R1]} on Axis(0),
{
{([Marital Status].[All Marital Status].Children)} * {([Gender].[All
Gender].Children)}
}
on Axis(1)
from Sales


gives mi formula error in R1 column

Any help?

Your second statement has a problem:
WITH
MEMBER [Time].[R1] AS
'RANK(
Axis(1).Item(0).Item(0).Dimension.CurrentMember,
ORDER (Axis(1), ([Measures].[Profit],(Axis(0).item(0).item(0))), ASC)
)', SOLVE_ORDER=1

select
{([Time].[1997].Children), [Time].[R1]} on Axis(0),
{
{([Marital Status].[All Marital Status].Children)} * {([Gender].[All
Gender].Children)}
}
on Axis(1)
from Sales


....the problem with "([Gender].[All Gender].Children)}" is that you're
trying to get children of a level, not of a specific member.



I'm guessing you wanted the genders listed, so I rewrote that query as:

WITH
MEMBER [Time].[R1] AS
'RANK(
Axis(1).Item(0).Item(0).Dimension.CurrentMember,
ORDER (Axis(1), ([Measures].[Profit],(Axis(0).item(0).item(0))), ASC)
)', SOLVE_ORDER=1

select
{([Time].[1997].Children), [Time].[R1]} on Axis(0),
{
{([Marital Status].[All Marital Status].Children)} *
{[Gender].[Gender].members}
}
on Axis(1)
from Sales


....now, however, you'll get #ERR in the R1 calc because you're set doesn't
make sense in the ORDER statement.

The question is, what exactly are you trying to order and rank in that
second query?

Maybe I can help more if I know.

- Phil

"PN" wrote:

Sorry, for the mistake above, actually ALL Gender is a valid member, I got an
error for a different reason, [All Gender].Children is the same as
[gender].[gender].members, forgive me. Darn, I'm slipping! : >)

I'm still uncertain as what it is you want to rank, though.

- Phil

"PN" wrote:

Hello!

Thank's for your response. I think I mixed myself a little bit with this query
I wanted to set rank over first dimension on axis(1). That is why I used this:
Rank(Axis(1).Item(0).Item(0).Dimension.CurrentMemb er, ...)

and

I wanted to make order on one of the columns on axis(0) and all dimensions on axis(1)

Maybe my description is not clear but it works when I do this, like in query below:



WITH
MEMBER [Time].[R1] AS
'RANK(
[Gender].CurrentMember,
ORDER (
{[Gender].[All Gender].Children},
([Measures].[Profit],(Axis(0).item(0).item(0))), ASC))', SOLVE_ORDER=1

select
{([Time].[1997].Children), [Time].[R1]} on Axis(0),
{
{([Marital Status].[All Marital Status].Children)} * {([Gender].[All Gender].Children)}
}
on Axis(1)
from Sales


Here I make an order only by one dimension: {[Gender].[All Gender].Children}
It seems that it is simply impossible to do what I previously wanted to do,
so thank's for your time


Peter





"=?Utf-8?B?U1FMIE1jT0xBUA==?=" <SQLMcOLAP (AT) discussions (DOT) microsoft.com>
wrote in news:5F1D8EA1-6102-48DC-B47D-4290B93A6C76 (AT) microsoft (DOT) com:

This version works when the number of dimensions on Axis(1) is known, so
as to construct the current Axis(1) tuple:

MEMBER [Time].[R1] AS
'RANK(
(Axis(1).Item(0).Item(0).Dimension.CurrentMember,
Axis(1).Item(0).Item(1).Dimension.CurrentMember),
Order(Axis(1), ([Measures].[Profit], Axis(0).item(0).item(0)), BASC)
)', SOLVE_ORDER=1

select
{([Time].[1997].Children), [Time].[R1]} on Axis(0),
{[Marital Status].[All Marital Status].Children
* [Gender].[All Gender].Children} on Axis(1)
from Sales
where ([Measures].[Profit])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***