Getting Yesterday date properly formated in SSRS expressions ready for MDX

Suppose I want yesterdays value for use in MDX from SSRS. It will be something like:

[DPTime].[THEDATE].&[2009-08-22T00:00:00]

Here you can see that I have a Time heirarchy DPTime->THEDATE and in that the member for the time.

Heres how to do it in SSRS expressions. The following expression with take you 7 days back:

="[DPTime].[THEDATE].&["
&
Year(DateAdd("D",-7,DateTime.Now()))
&"-"&
IIf(Month(DateAdd("D",-7,DateTime.Now()))< 10,"0","")
&
Month(DateAdd("D",-7,DateTime.Now()))
&"-"&
IIf(Day( DateAdd("D",-7,DateTime.Now()) )< 10,"0","")
&
Day(DateAdd("D",-7,DateTime.Now()))
&"T00:00:00]"

The above expansion of the expression makes it pretty much self explanatory. But still note the following:

• -7 i.e the number of days to go back occurs 5 times.
• "[DPTime].[THEDATE].&[" is your heirarchy (bound to be different in your case).

Enjoy!