SSRS, SSAS - Auto Pick Yesterday
Jan.22, 2007 in
Geeky/Programming
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.
Simlar Posts
January 23rd, 2007 at 4:13 pm
[...] 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. [...]
February 3rd, 2007 at 5:00 am
=”[Time].[Calendar Date].&[” & Today().AddDays(-1).ToString("yyyy-MM-ddT00:00:00]“)
February 7th, 2007 at 5:28 pm
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]“
August 10th, 2007 at 5:36 am
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?
August 10th, 2007 at 11:13 am
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…
November 21st, 2007 at 11:44 am
thanks for the GREAT post! Very useful…