SSRS Commonly Required Date Expressions

, 2010-02-05

Reporting Services has many expressions that can manipulate date fields.  These expressions can be used just about anywhere in SSRS including object properties, displayed data, and parameters. 

Recently I’ve worked on some projects that required date parameters to do things that you would typically see in an Analysis Services cube.  My client needed to see data brought back Week to Date, Month to date,  Year to date, and on with a rolling week.  My first suggestion of course was to create a cube to not only to do these calculations but get many other benefits.  Unfortunately, they did not take my advice so I had to use the SSRS expression language to return the results they were looking for.

I thought this made for a great blog topic so I could refer back to these whenever I need them.  All the reports had at least two parameters for start date and end date.  I needed to change the default values so they would default to show the above date range (YTD, MTD…etc). 

End date would always have the current date or Today() in the expression language.  If you want time included then use Now().  The default value of the start date parameter will use the DateAdd function to return back a date that is a set number of units back.  The DateAdd function is constructed like this:

DateAdd(interval, number, date)

Argument

Description

Interval

This is the interval you want to add (Ex. Days, Months, Years…)

Number

Numeric value that represents the number of intervals you want to add

Date

The date to perform the addition to.

Expression I use to change my parameter default values.  These could be written many different ways and actually use VB in the customer code are but I wrote them this way if you’re not a VB expert.

Month over month

=DateAdd("m",-1,Today)

MTD

=DateAdd("d",-(Day(today)-1), Today)

Week over week

=DateAdd("ww",-1, Today)

or

=DateAdd("d",-7, Today)

WTD

=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

Year over Year

=DateAdd("yyyy",-1, Today)

YTD

=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)

Notice in the YTD and WTD expression I’m calling a DateInterval function that is actually a Visual Basic function but does not require you do add anything to the custom code section of your report.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads