如何在有和没有聚合组的情况下查询MDX中的日期字段 [英] How to query Date field in MDX with and without aggregate group

问题描述

我对创建价格数据查询似乎很简单，该查询将返回不同日期的四组价格数据:

I have a seemingly simple requirement to create a query of price data that will return four sets of price data for different dates:

1. 该范围的最低日期
2. 该范围的最高日期
3. 整个范围的总和

我最近的是以下查询:

WITH set [Range] as {[Effective Date].[Date].&[2015-12-01T00:00:00] : [Effective Date].[Date].&[2015-12-31T00:00:00]}
select
NON EMPTY{
    filter([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price]),
    filter([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price]),
    nonempty([Measures].[Money In], [Range]),
    nonempty([Measures].[Money Out], [Range])
} on 0,
NON EMPTY{
[Region Manager].[Full Name].[Full Name]
} on 1
from [Cube]

但是，这样的日期范围会返回错误:

However, the date ranges as such return the error:

函数中指定的两个集合的维数不同.

Two sets specified in the function have different dimensionality.

过滤器"或非空"语句将单独工作，但我无法使它们在单个查询中工作.我怎样才能做到这一点?使用子查询会有所帮助吗?

The "filter" or "nonempty" statements will work individually but I cannot get them to work in a single query. How can I do this? Will it be helpful to use a sub query?

推荐答案

尝试一下:

WITH 
  SET [Range] AS 
    {
      [Effective Date].[Date].&[2015-12-01T00:00:00] 
    : [Effective Date].[Date].&[2015-12-31T00:00:00]
    }
SELECT
NON EMPTY
    {
      ([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price])
     ,([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price])
     ,{nonempty([Range]*[Measures].[Money In])}
     ,{nonempty([Range]*[Measures].[Money Out])}
    }
ON 0,
NON EMPTY [Region Manager].[Full Name].[Full Name] ON 1
FROM [Cube];

但是NonEmpty并不是真正必需的，因此更简洁:

But NonEmpty is not really required so this is more concise:

WITH 
  SET [Range] AS 
    {
      [Effective Date].[Date].&[2015-12-01T00:00:00] 
    : [Effective Date].[Date].&[2015-12-31T00:00:00]
    }
SELECT
NON EMPTY
    {
      ([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price])
     ,([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price])
     ,{[Range]*[Measures].[Money In]}
     ,{[Range]*[Measures].[Money Out]}
    }
ON 0,
NON EMPTY [Region Manager].[Full Name].[Full Name] ON 1
FROM [Cube];

要汇总，您需要一种新的自定义指标:

To aggregate you need a new custom measure:

WITH 
  SET [Range] AS 
    {
      [Effective Date].[Date].&[2015-12-01T00:00:00] 
    : [Effective Date].[Date].&[2015-12-31T00:00:00]
    }
  MEMBER [Effective Date].[Date].[All].[AggRange] AS 
    Aggregate([Range])
SELECT
NON EMPTY
    {
      ([Effective Date].[Date].&[2015-12-01T00:00:00], [Measures].[Plan Price])
     ,([Effective Date].[Date].&[2015-12-31T00:00:00], [Measures].[Plan Price])
     ,([Effective Date].[Date].[All].[AggRange],[Measures].[Money In])
     ,([Effective Date].[Date].[All].[AggRange],[Measures].[Money Out])
    }
ON 0,
NON EMPTY [Region Manager].[Full Name].[Full Name] ON 1
FROM [Cube];

这篇关于如何在有和没有聚合组的情况下查询MDX中的日期字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！