Fun and games with Reporting Services expressions

jamiet — Thu, 10 Dec 2009 16:23:00 GMT

I have today been messing about with Reporting Services' expression language and as such have learnt a few things that I figured might be worth sharing.

The report that I have been attempting to build has two parameters that define the effective reporting period for the report, effectively they are arguments in a WHERE clause:

The business rule that I had to implement for these parameters was:

The effective reporting period is the most recently completed month

In other words, I had to determine the first day and last day of the previous month which for me (as I was trying to do this on 8th December 2009) was 1st November 2009 and 30th November 2009.

How would you have gone about this? Here was my first attempt at determining "From Effective Date" (i.e. 1st November 2009) based on today's date:

=CDate(
    CStr(
        Year(DateAdd("mm", -1, Now())) * 10000 +
        Month(DateAdd("mm", -1, Now())) * 100 +
        1
    )
)

You can probably decipher my logic here. Subtract one month from today's date and use the first day of the month of the result. Sound logic but unfortunately it failed with a totally unhelpful error message :

"An error occurred during local report processing. <parameter name>"

After investigation it seems as though the CDate() function does not like strings in the format YYYYMMDD so here was my next attempt:

=CDate(
    CStr(Year(DateAdd(DateInterval.Month,-1,Now()))) + "-" +
    RIGHT("0" + CStr(Month(DateAdd(DateInterval.Month,-1,Now()))), 2) +
    "-01"
)

This one worked fine and I was going to stick with it until Summitcloud suggested using the DateSerial(...) function instead like so:

=DateSerial(Year(DateAdd(DateInterval.Month,-1, Now())), Month(DateAdd(DateInterval.Month,-1, Now())), 1)

That's a much nicer method in my opinion, no hacky string manipulation going on here, only three numeric arguments (which I have highlighted in different colours) to define year/month/day thus I changed to use DateSerial(…). Happy with that I turned to working out my "To Effective Date" using the same DateSerial() function. This time my logic was to get the first day of the current month and then subtract one day from it:

=DateAdd(DateInterval.Day, -1, DateSerial(Year(Now()), Month(Now()), 1))

Worked a treat!

So I guess the lesson here is to make sure you know all the tools in your toolbox and what they all do. Don't use pliers (string manipulation) to tighten a nut when you already have a spanner(DateSerial)! (Does that analogy work? Maybe not!! :)

@Jamiet

SSIS Junkie : expressions, SQL Server Reporting Services

Fun and games with Reporting Services expressions