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.