日期小于现在的MDX

本文介绍了日期小于现在的MDX的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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:

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

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.