分组MDX查询结果 [英] Grouping MDX query results

问题描述

我有以下查询(基于Microsoft®SQLServer®2008 MDX分步指南提供的示例数据):

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]

该查询显示特定月份在特定子类别中的产品下达的订单数.对于类别28中的所有产品，我需要计算1月或2月(第1或2个月)下的订单数.在产品447上下的订单例外:在此 情况下，我还需要包括3月份下的订单数量.

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).

所以不是

• 山瓶笼，176年1月
• 山瓶笼，183年2月
• 路瓶笼，141年1月
• 路瓶笼，152年2月
• 水壶-盎司30盎司，381年1月
• 矿泉水瓶-30盎司，403年2月，
• 矿泉水瓶-30盎司，414年3月

我需要拥有:

• 山地水壶架，359(176 + 183)
• 路瓶笼，293(141 + 152)
• 水壶-30盎司，1198(381 + 403 + 414)

我尝试将Important_months集合放到where子句中，但是(由于自定义集合而导致的循环引用错误除外)，我将无法投影 行轴上的类别(我可以吗?).另外，我想到了使用子查询，但是看来我也无法引用那里设置的重要时间.

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.

换句话说:我需要获得在SQL中我可以通过发出

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

能做到吗?

推荐答案

一个有根据的猜测是

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]
 )

这篇关于分组MDX查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！