Grouping MDX query results

问题:

I have a following query (based on sample data provided with Microsoft® SQL Server® 2008 MDX Step by Step book):

WITH
SET important_months AS
{
    ([Product].[Product Categories].[Subcategory].&[28].CHILDREN    , {[Date].[Month of Year].&[1], [Date].[Month of Year].&[2]}),
    ([Product].[Product Categories].[Product].&[477]                , {[Date].[Month of Year].&[3]})
}

SELECT [Measures].[Order Count] ON COLUMNS,
     important_months ON ROWS
FROM [Step-by-Step]

The query shows the number of orders placed on products in a particular subcategory in particular months. For all products in category 28, i need the count of orders placed in January or February (month 1 or 2). Exceptions are orders placed on product 447: in this case, I additionally need to include number of orders placed in March.

In the end however, I'm not really interested in details regarding months: all I want, is simple number of orders placed on a particular product (i.e. I want to loose/hide the information about what was the month the order was placed).

So instead of

• Mountain Bottle Cage, January, 176
• Mountain Bottle Cage, February, 183
• Road Bottle Cage, January, 141
• Road Bottle Cage, February, 152
• Water Bottle - 30 oz, January, 381
• Water Bottle - 30 oz, February, 403
• Water Bottle - 30 oz, March, 414

I need to have:

• Mountain Bottle Cage, 359 (176 + 183)
• Road Bottle Cage, 293 (141 + 152)
• Water Bottle - 30 oz., 1198 (381 + 403 + 414)

I tried with putting the important_months set into a where clause, but (besides circular reference error due to custom set) I wouldn't be able to project the categories on rows axis (would I?). Also, I thought of using a subquery, but it appears I cannot refer to the important_months set there either.

In other words: I need to get result that in SQL i would get by issuing

SELECT SUM([Order Count]) 
FROM <MDX RESULT HERE> 
GROUP BY Product

Can it be done?

回答1:

An educated guess is that MDX Subqueries is the solution. Did you try using tuples in the subselect :

  WITH
    SELECT [Measures].[Order Count] ON COLUMNS,
    {[Product].[Product Categories].[Subcategory].&[28].CHILDREN,[Product].[Product Categories].[Product].&[477]} ON ROWS
 FROM ( 
   SELECT 
     {([Product].[Product Categories].[Subcategory].&[28].CHILDREN,{[Date].[Month of Year].&[1], [Date].[Month of Year].&[2]}),
  ([Product].[Product Categories].[Product].&[477],{[Date].[Month of Year].&[3]})} ON 0
  FROM [Step-by-Step]
 )

回答2:

You're creating an asymmetric set (with March for Water Bottle only), so you can't really slice this directly in the WHERE clause without including it for all other products.

icCube's answer looks good to me, with one small addition: add a DISTINCT to the row selection to combine Water Bottle back into one row.

WITH
  SELECT [Measures].[Order Count] ON COLUMNS,
  DISTINCT {[Product].[Product Categories].[Subcategory].&[28].CHILDREN, [Product].[Product Categories].[Product].&[477]} ON ROWS
FROM ( 
  SELECT 
    {([Product].[Product Categories].[Subcategory].&[28].CHILDREN,{[Date].[Month of Year].&[1], [Date].[Month of Year].&[2]}),
    ([Product].[Product Categories].[Product].&[477],{[Date].[Month of Year].&[3]})} ON 0
  FROM [Step-by-Step]
)