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.



  • Pingback: Charlie Maitland’s Blog MDX for using Yesterdays date in a SSRS Parameter «

  • http://ssasfreak.spaces.live.com Steffo

    =â€?[Time].[Calendar Date].&[â€? & Today().AddDays(-1).ToString("yyyy-MM-ddT00:00:00]“)

  • http://www.stevienova.com Steve Novoselac

    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]“

  • Eivind G.

    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?

  • http://www.stevienova.com Steve Novoselac

    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…

  • http://www.whateverishere.com Whatever-ishere

    thanks for the GREAT post! Very useful…