I want to have an MDX that creates different kinds of crossjoins on the
rows, depending on the level of the current member in the first dimension
plus I want to have a sorting within the inner dimension. My idea was to use
an iif()-function for the second set in the generate()-function.

I' ve already tried different ways to implement but all without succes....

Thanks for any help,
Matthias


with
set [myCustomers] as
'{[Customers].[All Customers], [Customers].[City].members}'
set [myProducts_TOP] as
'{ [Product].[All Products]}'
set [myProducts_Cat] as
'{[Product].[Product Category].members}'

set [theRows] as
'generate(
{myCustomers},

{iif(myCustomers.currentmember.level.name = "City",
{nonemptycrossjoin( {[mycustomers].currentmember}, {ORDER([myProducts_Cat],
[Measures].[Unit Sales], BDESC)})},
{nonemptycrossjoin({[mycustomers].currentmember}, {ORDER([myProducts_TOP],
[Measures].[Unit Sales], BDESC)})}
)
}
)'

set [theColumns] as
'{[Measures].[Unit Sales]}'
select
[theRows] on rows,
[theColumns] on columns
from sales

Hi Matthias

The problem here is that the IIF function can only return either a numeric value or a string value - not a set value as you're trying to do here. The workaround is to get IIF to return a string, and then cast that string to a set using the STRTOSET function. Here's the result for your query

wit
set [myCustomers] a
'{[Customers].[All Customers], [Customers].[City].members}
set [myProducts_TOP] a
'{ [Product].[All Products]}
set [myProducts_Cat] a
'{[Product].[Product Category].members}
set [theRows] a
'generate
{myCustomers}
nonemptycrossjoin( {[customers].currentmember}
strtoset
iif(Customers.currentmember.level is [Customers].[City],
"ORDER([myProducts_Cat],[Measures].[Unit Sales], BDESC)"
"ORDER([myProducts_TOP],[Measures].[Unit Sales], BDESC)"

))
set [theColumns] a
'{[Measures].[Unit Sales]}

selec
[theColumns] on columns
[theRows] on row
from sale

HTH

Chri

----- Matthias Englert wrote: ----

I want to have an MDX that creates different kinds of crossjoins on th
rows, depending on the level of the current member in the first dimensio
plus I want to have a sorting within the inner dimension. My idea was to us
an iif()-function for the second set in the generate()-function

I' ve already tried different ways to implement but all without succes...

Thanks for any help
Matthia


wit
set [myCustomers] a
'{[Customers].[All Customers], [Customers].[City].members}
set [myProducts_TOP] a
'{ [Product].[All Products]}
set [myProducts_Cat] a
'{[Product].[Product Category].members}

set [theRows] a
'generate
{myCustomers}

{iif(myCustomers.currentmember.level.name = "City"
{nonemptycrossjoin( {[mycustomers].currentmember}, {ORDER([myProducts_Cat]
[Measures].[Unit Sales], BDESC)})}
{nonemptycrossjoin({[mycustomers].currentmember}, {ORDER([myProducts_TOP]
[Measures].[Unit Sales], BDESC)})


)

set [theColumns] a
'{[Measures].[Unit Sales]}
selec
[theRows] on rows
[theColumns] on column
from sale

Hi Chris,

that's it!

Thanks a lot,
Matthias

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:102ACF2E-9F4A-4B82-AFFD-A1AA35480C9C (AT) microsoft (DOT) com...
workaround is to get IIF to return a string, and then cast that string to a
set using the STRTOSET function. Here's the result for your query: