THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Fun and games with Reporting Services expressions

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:

20091210params

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

Published Thursday, December 10, 2009 5:23 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

David (summitcloud) said:

The real benefit of DateSerial over hacky strings is localization of your app won't matter now.

More people would string manipulate then convert (mm/dd/yyyy) which doesn't help your report users in Italy..or some other country with fine food and wine.

PS - If you hadn't already figured, use the current values for month/year, swap the 1 to 0 and that will be the last day of the previous month.

December 10, 2009 7:14 PM
 

Wes W. said:

Am I the only one with a "DimDate" table and just a couple of views that show ALL necessary date cuts?  Views show fields such as calendar and fiscal start and end dates, fiscal periods, current periods, days in a every period, etc. making this type of task a trivial lookup for SSRS?

My code, based on our fiscal month, would be:

SELECT [DateEnd]

 FROM [FiscalPeriods]

 WHERE PeriodId IN (SELECT (PeriodId - 1) FROM [FiscalPeriods] WHERE [IsCurrentFiscalPeriod?] = 1)

No locale issues and similar MDX code functionality is also pretty easy.  

December 11, 2009 9:17 AM
 

dan said:

One issue with this is that you can end up with repeated screen refreshes when using non-deterministic date functions as defaults.

http://sqlreportingservices.spaces.live.com/blog/cns!17F39A64B3FF4C5C!238.entry

It's actually better to return the defaults from a recordset instead.

December 11, 2009 12:03 PM
 

David said:

@Wes: Having a Date dimension is not a panacea. You still need to maintenance that table, no? Setting fiscal start and end dates isn't automatic... Large scale enterprise overhead vs one-off report...

There is always more than 1 way to slice the cat...er pie. Choose the appropriate solution for the problem at hand.

@Dan: Your blog entry is only half-right.

1) The issue isn't date functions as defaults...the challenge is with using any expressions as defaults. There is an old post here about that:

http://jetlounge.net/blogs/teched/archive/2007/12/13/strange-refresh-of-parameters-in-sql-reporting-services.aspx

2) The potential for parameter refresh is only when it is used downstream on the parameter list, and only noticable when there are several parameters.

December 12, 2009 1:17 AM
 

Wes W. said:

@David:  Agreed, though at my employer we have fiscal start and end periods input through 2015 and only have ONE master date table to maintain in the enterprise; so there isn't much maintenance at all.  Being fiscally based, there is no better solution I'm aware of since the Accounting department establishes those dates to meet their needs (as it should be).

Even if your fiscal year is a calendar year, I'd still go this route since having a master date table is very easy to create and extremely useful for many other purposes.

We can both agree that there is no single "correct" solution - just different approaches to achieve the same goal.

December 14, 2009 9:10 AM
 

SSIS Junkie said:

I recently overheard a remark by Greg Low in which he said something akin to "the most interesting parts

May 10, 2012 5:08 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement