If you ever have written any Reporting Services reports off a cube (Analysis Services), and want to have a drop down that auto picks a day, this is for you. There is probably a more elegant way to do this, but it works.
=”[Time].[Calendar Date].&[“& Year(DateAdd(“D”,-1,DateTime.Now())) &”-“& IIf(Len(Month(DateAdd(“D”,-1,DateTime.Now()))) < 2,Month(DateAdd("D",-1,DateTime.Now())),"0"&Month(DateAdd("D",-1,DateTime.Now()))) &"-"& IIf(Len(Day(DateAdd("D",-1,DateTime.Now()))) < 2,"0"&Day(DateAdd("D",-1,DateTime.Now())),Day(DateAdd("D",-1,DateTime.Now()))) &"T00:00:00]"
Remember, months and days need to have a zero in the front of them.
Steve Novoselac's Blog 
6 replies on “SSRS, SSAS – Auto Pick Yesterday”
[…] MDX for using Yesterdays date in a SSRS Parameter January 23rd, 2007 — Charlie Maitland Snippet from Stevienova that will return yesterdays date for use in a parameter. […]
LikeLike
=â€?[Time].[Calendar Date].&[â€? & Today().AddDays(-1).ToString(“yyyy-MM-ddT00:00:00]”)
LikeLike
update:
=”[Time].[Calendar Date].&[“& Year(DateAdd(“D”,-1,DateTime.Now())) &”-“& IIf(Len(Month(DateAdd(“D”,-1,DateTime.Now()))) < 2,Month(DateAdd("D",-1,DateTime.Now())),"0"&Month(DateAdd("D",-1,DateTime.Now()))) &"-"& IIF(Len(Day(DateAdd("D",-1,DateTime.Now())).ToString()) < 2,"0" & Day(DateAdd("D",-1,DateTime.Now())).ToString(),Day(DateAdd("D",-1,DateTime.Now())).ToString()) &"T00:00:00]"
LikeLike
Hey!
Can I use a similar approach to get RS to pass values from the datepicker to query an AS database? You ever got that working?
LikeLike
well, you should be able to drag your time DIM over in the filters in the query designer on the Data tab, and then check the boxes next to it for “parameter” and SSRS should automagically add it to your parameters on your report, the date picker is there and works just fine, it is just setting the default dynamically which is tough, which this post should handle…
LikeLike
thanks for the GREAT post! Very useful…
LikeLike