Sunday, August 23, 2009

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:


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:

IIf(Month(DateAdd("D",-7,DateTime.Now()))< 10,"0","")
IIf(Day( DateAdd("D",-7,DateTime.Now()) )< 10,"0","")

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).