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))