Date Ranges in SSRS and SSAS

I have taken my first steps in Reporting Services (SSRS) land. The reports use data from a multidimensional cube in Analysis Services (SSAS). Of course, one requirement was to filter the reports on a date range. Should be simple, right? Well, at least not for me.

When designing the reports, I created a new dataset, used the MDX query designer and added my date dimension as a filter. I choose Range (Inclusive) and checked the two parameter checkboxes. Visual Studio automatically added these parameters both to the dataset and the report.

The problem was that the key of my dimension was a date column, but the tool added text parameters and choose available values from a query. That wasn’t what I wanted. I wanted a calendar drop down. So I changed the parameters to date and as available values I choose None. OK, now I had the calendar. But the report didn’t work. I got “The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated”.

It turned out I had to use a completely different expression for the dataset parameter expression. The expression should return a string that contains a dimension filter. I tried:

="[Datum].[Datum].&[" + Format((Parameters!StartDate.Value), "yyyy-MM-dd") + "]"

(My dimension is called “Datum” and so is my key attribute.)

It didn’t work. I got an error saying 2014-05-01 couldn’t be converted to the type date. Now I thought that SSAS was using US formatted dates internally, so I tried “MM-dd-yyyy”. Didn’t work either. After some Bing research I finally managed to find the solution: include time in the string as well:

="[Datum].[Datum].&[" + Format((Parameters!StartDate.Value), "yyyy-MM-ddTHH:mm:ss") + "]"

By the way, here is how to set the default for the report parameters to start and end of previous month:

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s