CODEWITHSUNDEEP
datefiltermdx
user2741956
user2741956

Reputation: 145

Filter Today's Date in MDX

Hi I am trying to put a dynamic date filter (like today's date) in a MDX query, but I cannot get any result from the query, can some one help to check what is it wrong with my query?

I first tested the MDX query with a specified dated value, the query is

WITH MEMBER [Yesterday] AS VBAMDX.Format(VBAMDX.Now()-1,"MM/dd/yy")

select {[Measures].[Order Root Total], [Measures].[Yesterday]} on columns,
filter ([D Date].[DAY ID].&[09/19/13],
[Measures].[Order Root Total]>0) on rows
from [Root Product Profit and Sales]

It return the result

            Order Root Total    Yesterday
09/19/13    1481.2225483463         09/19/13

Then I tried:

WITH MEMBER [Yesterday] AS VBAMDX.Format(VBAMDX.Now()-1,"MM/dd/yy")

select {[Measures].[Order Root Total], [Measures].[Yesterday]} on columns,
filter ([D Date].[DAY ID].&[Yesterday],
[Measures].[Order Root Total]>0) on rows
from [Root Product Profit and Sales]

Then I return nothing..

Can Someone Please help me! Much Appreciate!!!

Upvotes: 3

Views: 5844

Answers (1)

FrankPl
FrankPl

Reputation: 13315

You would have to use StrToMember, as Yesterday is a measure, and cannot directly be used in a member name. I would suggest defining Yesterday on the [D Date].[DAY ID] hierarchy instead of the Measures one:

WITH MEMBER [D Date].[DAY ID].[Yesterday] AS 
     StrToMember('[D Date].[DAY ID].&' + VBAMDX.Format(VBAMDX.Now()-1,"MM/dd/yy") + ']')

select {[Measures].[Order Root Total], [Measures].[Yesterday]} on columns,
filter ([D Date].[DAY ID].[Yesterday],
[Measures].[Order Root Total]>0) on rows
from [Root Product Profit and Sales]

Upvotes: 2

Related Questions