SSRS, SSAS – Auto Pick Yesterday

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.

By Steve Novoselac

Director of Digital Technology @TrekBikes, Father, Musician, Cyclist, Homebrewer

6 replies on “SSRS, SSAS – Auto Pick Yesterday”


=”[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]"



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?


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…


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.