Please help me to find a better solution for calculating total unit sales of
specific products to customers those who lives in Burnaby,Yakima, Seattle
cities and education level has 'Bachelors Degree', Graduate Degree or High
School
Degree

i have created a Mdx Query using Named sets and Cross Join, that is working
fine

is any simple way to do the same?.


This is my Query

WITH SET [Drink] AS
'{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Good].[Good Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Pearl].[Pearl Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Portsmouth].[Portsmouth Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Top Measure].[Top Measure Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Walrus].[Walrus Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Good].[Good Chablis Wine],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Pearl].[Pearl Chablis Wine],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Portsmouth].[Portsmouth Chablis Wine],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Top Measure].[Top Measure Chablis Wine],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine].[Wine].[Walrus].[Walrus Chablis Wine]}'

Set [City] As
'{[Customers].[All Customers].[Canada].[BC].[Burnaby],
[Customers].[All Customers].[USA].[WA].[Yakima],
[Customers].[All Customers].[USA].[WA].[Seattle]}'

Set [Education] As
'{[Education Level].[All Education Level].[Bachelors Degree],
[Education Level].[All Education Level].[Graduate Degree],
[Education Level].[All Education Level].[High School Degree]}'

member Measures.[testsum] as
'SUM( {CrossJoin ({[Education]}, CrossJoin ({City}, [Drink]))}, [Unit Sales])'

SELECT
{ [Measures].[testsum]} on columns
FROM Sales

Please help me find a solution for this problem


Regards

Subin

You can use nested sum(), in lieu of crossjoin():

'Sum([Education], Sum([City},
Sum([Drink], [Unit Sales])))'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!