MDX Query (New to MDX

Discussion in 'microsoft.public.sqlserver.olap' started by Subin, Nov 17, 2004.

  1. Subin

    Subin Guest

    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
     
    Subin, Nov 17, 2004
    #1
    1. Advertisements

  2. Subin

    Deepak Puri Guest

    You can use nested sum(), in lieu of crossjoin():
    member Measures.[testsum] as
    'Sum([Education], Sum([City},
    Sum([Drink], [Unit Sales])))'

    - Deepak
     
    Deepak Puri, Nov 17, 2004
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.