在一组元组上使用MDX DateAdd函数

Question

我试图使用DateAdd()函数创建一组日期，但我在试图传递一组元组作为参数时出现错误。下面的代码返回一个成员，但我正在寻找一组新的日期。

,[au:] AS成员DATEADD("M"，12，[受伤日期].CurrentMember)

SELECT {[EFF INJ DT]} ON COLUMNS, [INJURY DATE].[DATE].Members ON ROWS
FROM [WVWC DATA CUBE FROI SROI]

我有以下尝试:

 WITH 
Set [EFF INJ DT] AS 
DATEADD("M",12, [INJURY DATE].CurrentMember)
SELECT {[EFF INJ DT]} ON COLUMNS, [INJURY DATE].[DATE].Members ON ROWS
FROM [WVWC DATA CUBE FROI SROI]

Answer 1

使用Adv Works，如果你想移动12个成员，那么你可以通过几种方式导航。

尝试以下操作。它应该比currentmember提前12天返回日期:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
        [Date].[Calendar].CurrentMember.LEAD(12).MEMBERVALUE
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[Date].[Calendar].[Month].&[2006]&[4].CHILDREN} ON 1
FROM [Adventure Works];

看起来您想要在未来返回12 个月的日期。

因此，首先我们需要通过ANCESTOR函数来对应currentmember的年份>，然后使用COUSIN函数向前推进到等效日期，如下所示:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
    COUSIN(
        [Date].[Calendar].CurrentMember, 
        ANCESTOR(
            [Date].[Calendar].CurrentMember, 
            [Date].[Calendar].[Calendar Year]).LEAD(1)
            ).membervalue   
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[Date].[Calendar].[Month].&[2006]&[4].CHILDREN} ON 1
FROM [Adventure Works];

适应您的场景并假设INJURY DATE是多级user hierarchy' that contains another level called日历年，您可以执行以下操作:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
        COUSIN(
            [INJURY DATE].CurrentMember, 
            ANCESTOR(
                [INJURY DATE].CurrentMember, 
                [INJURY DATE].[Calendar Year]).LEAD(1)
                ).MEMBERVALUE   
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[INJURY DATE].[DATE].Members} ON 1
FROM [Adventure Works];

---

编辑

通过VBA工具，我们在广告工作中有以下内容:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
      DATEADD(
      "M",
      12,
      [Date].[Calendar].CurrentMember.membervalue 
      )  
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[Date].[Calendar].[Month].&[2006]&[4].CHILDREN} ON 1
FROM [Adventure Works];

适合您的场景:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
    DATEADD(
    "M",
    12,
    [INJURY DATE].CurrentMember.membervalue 
    )  
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[INJURY DATE].[DATE].Members} ON 1
FROM [Adventure Works];