1

I'm using MSAS 2008 with the Adventure Works DW. Here's the structure for the slice I'd like to get using MDX query:

  • [Product].[Style], [Product].[Product Categories], [Measures].[Discount Amount], [Measures].[Reseller Order Quantity] on rows
  • [Product].[Color] on columns

I'm stuck on creating a query to get the top 2 members from the Product Categories hierarchy. The problem is I don't know how to create the new calculated Total member for the [Product].[Style] hierarchy. Using the query below I can only create Total member for the [Product].[Product Categories] hierarchy, but all my attempts to get the total for Style hierarchy have failed.

WITH 
    SET [Product Color (set)] as 'DRILLDOWNLEVEL(
        {[Product].[Color].[All Products]}
    )'
    SET [Top 2 Categories For Each Style (set)] as 'GENERATE(
        [Product].[Style].[Style],
        TOPCOUNT(
            ([Product].[Style].CURRENTMEMBER, [Product].[Product Categories].[Category]),
            2,
            [Measures].[Discount Amount]
        ) + ([Product].[Style].CURRENTMEMBER, [Product].[Product Categories].[Total])
    )'
    SET [Product Style (set)] as 'DRILLDOWNLEVEL(
        {[Product].[Style].[All Products]}
    )'
    SET [rowSet] AS '[Top 2 Categories For Each Style (set)] *
                     {
                        [Measures].[Discount Amount],
                        [Measures].[Reseller Order Quantity]
                     }'
    SET [columnSet] as '[Product Color (set)]'
    member [Product].[Product Categories].[Total] as 'AGGREGATE(
        TOPCOUNT(
            ([Product].[Style].CURRENTMEMBER, [Product].[Product Categories].[Category]),
            2,
            [Measures].[Discount Amount]
        )
    )'
SELECT 
    [rowSet] ON ROWS, 
    [columnSet] ON COLUMNS 
    FROM [Adventure Works] CELL PROPERTIES VALUE

Any advice would be appreciated.

iamjager
  • 11
  • 1

0 Answers0