MDX: combining SUM and Existing

Hi,
I have problem summing values in leaf level and total level.
Lets say, I would like to to get orders in dealer price for each selected
product as well as sum for selected products only. As example, this MDX
could demonstrate my case in Adventure Works database:

with member [Dealer Price Orders] as

SUM(
Existing ([Product].[Product Categories].[Product]),
iif([Product].[Dealer Price].MemberValue = 0 , null,
[Order Quantity]*[Product].[Dealer Price].MemberValue)
)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product
Categories].[All Products]})})
ON COLUMNS
FROM (SELECT ({[Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure
Works])
WHERE ([Measures].[Dealer Price Orders])

Unfortunately, this MDX returns Dealer Price Sales for all Products, but not
for Selected [Accessories] and [Bikes]. VisualTotals function does not help
in this case. Does anyone has a solution how to rework my calculated member?

Ramunas Balukonis

on 2005 sp2, I get a blank resultset from your test query - so a lot of people probably can't use it to give you an answer.

are you on something like sp1 or ssas 2000?

|||

Unfortunately the Existing statement currently doesn't take into account subselect restrictions.

One possibility is if you can define your calculation on leaf cells, then subselect visualtotals can be achieved. Here is one example, note that I changed [Product] to [Product Name] which matches my version of Adventure Works.

with cell calculation X for '([Product].[Product Categories].[Product Name].members, [Order Quantity])' as

iif([Product].[Dealer Price].MemberValue = 0 , null,

[Order Quantity]*[Product].[Dealer Price].MemberValue)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})

ON COLUMNS

FROM (SELECT ({[Product].[Product Categories].[Category].&[4],

[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works])

WHERE ([Measures].[Order Quantity])

|||

Here is another way which uses calculated member instead of cell calculation. It uses the fact that query scope named sets are resolved using subselect restrictions.

with set S as [Product].[Product Categories].[Product Name].members

member [Dealer Price Orders] as

SUM(

existing(S),

iif([Product].[Dealer Price].MemberValue = 0 , null,

[Order Quantity]*[Product].[Dealer Price].MemberValue)

)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})

ON COLUMNS

FROM (SELECT ({[Product].[Product Categories].[Category].&[4],

[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works])

WHERE ([Measures].[Dealer Price Orders])