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.

About these ads

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

  1. 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]"

    Like

  2. 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?

    Like

  3. 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…

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s