日期小于现在的MDX [英] MDX where Date is less than now

问题描述

这是我的下面的代码...

This is my code below......

    SELECT 
 {
    [Measures].[ACPPurchaseValue]

  } ON COLUMNS
,(

   [Date].[YYYYMMDD].[YYYYMMDD]

  ) ON ROWS
FROM [Kahuna]
WHERE  
  (
    [Reporting Currency].[reportingCurrency].&[1]
   ,strToSet(@MdxBOSP)
   ,strToSet(@MdxVIPType)
   ,strToSet(@MdxHost)
   ,strToSet(@MdxOperatorName)
  );

我该怎么说[日期].[YYYYMMDD].[YYYYMMDD]< getdate()

How can I say where [Date].[YYYYMMDD].[YYYYMMDD] < getdate()

推荐答案

您需要首先创建一个与今天相对应的成员或单个成员集.

You need to initially create a member, or a single member set, that corresponds to today.

以下内容看起来有些复杂，但实际上是一种相当标准的方法-由Tomislav Piasevoli提出-违反了AdvWrks维度Date:

The following looks a little complex but is actually a fairly standard approach - put forward by Tomislav Piasevoli - it is against the AdvWrks dimension Date:

WITH
  MEMBER [Measures].[Key for Today] AS 
    Format
    (
      Now()
     ,'yyyyMMdd'
    ) 
  MEMBER [Measures].[Today string] AS 
    '[Date].[Calendar].[Date].&[' + [Measures].[Key for Today] + ']' 
  SET [Today] AS 
    StrToMember
    (
      [Measures].[Today string]
     ,constrained
    ) 
...

适用于您的情况:

WITH
  MEMBER [Measures].[Key for Today] AS 
    Format
    (
      Now()
     ,'yyyyMMdd'
    ) 
  MEMBER [Measures].[Today string] AS 
    '[Date].[YYYYMMDD].[YYYYMMDD].&[' + [Measures].[Key for Today] + ']' 
  SET [Today] AS 
    StrToMember
    (
      [Measures].[Today string]
     ,constrained
    ) 
SELECT 
   [Measures].[ACPPurchaseValue] ON 0
  ,{null:[Today].item(0).item(0)}  ON 1
FROM [Kahuna]
WHERE  
  (
    [Reporting Currency].[reportingCurrency].&[1]
   ,strToSet(@MdxBOSP)
   ,strToSet(@MdxVIPType)
   ,strToSet(@MdxHost)
   ,strToSet(@MdxOperatorName)
  );

另外两个可以简化所有人生活的更好的解决方案:

Two other better solutions which could simplify life for everyone:

1. 不要在多维数据集中实现将来的日期

1. Do not materialize future dates in your cube

保留将来的日期，但将一个名为[Today]的自定义集和一个作为今天的计算成员作为子代添加到Date的All成员中.

Leave future dates in, but add a custom set called [Today] and a calculated member called today as a child to Date's All member.

这篇关于日期小于现在的MDX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！