07.21.08
SQL Server Reporting Services Calendar Control for Date Parameter (SSAS OLAP)
When building a report in SQL Server Reporting Services, we often use date as parameters. When you create dataset from SQL Server Analysis Services source, by default the date will be treated as string and you will have a drop down list of date. In SQL Server 2005, Reporting Services introduced “calendar control” to allow user select date from calendar (similar to calendar control in ASP.NET). To activate this feature, you simply set the parameter data type to “datetime” instead of “string”, and a nice calendar control will show when you run the report.
So is it the end of story? Definitely NO!
By doing that, a lot of report designers find the report would not be rendered according to the date you select, or most time, it throws an error. For example:
An error occured during local processing.
An error has occured during report processiong.
Query execution failed for data set 'TestCalendarDataSet'.
Query(47,14) the restrictions imposed by the CONSTRAINED flag in the
STRTOSET function were violated.
So what went wrong? In short, the data returned by the calendar control is not ready to be used in SQL or MDX query. For example, the MDX code with your date parameter looks like this:
-
STRTOMEMBER(@Date, CONSTRAINED)
and the @Date value returned by the control (Parameters!Date.Value) is something like “2008-07-01″. So the piece of MDX will be
-
STRTOMEMBER("2008-07-01", CONSTRAINED)
As you can see, “2008-07-01″ is not a valid member for MDX. What we really expect should be something like this:
-
STRTOMEMBER([Date].[Date].&["2008-07-01T00:00:00"], CONSTRAINED)
Therefore, in order to make the date parameter work in the MDX code of your dataset, we need to manually convert the parameter value. Here are the steps how to convert the parameter value:
1. Go to “Data” tab in Report Designer
2. Choose the dataset that populates the report, and click “…” button beside it
3. Now, the “Dataset” dialog window shows, and you click “Parameters” tab in the dialog and you should see a list of parameters you choose.
4. Assume the calendar control parameter is @Date, in “Parameters” tab, you will have “Date” in the “Name” column, and “=Parameters!FromDateDate.Value” in “Value” column
5. Now, change the value column to: =”[Date].[Date].&[” + Format(CDate(Parameters!FromDateDate.Value), “s”) + “]”
6. Click “Ok” and close all windows
7. Click “Preview” to test the result
The main job is done in Step 5. What I did is to convert the parameter from a simple date string to a MDX member. CDate(Parameters!FromDateDate.Value), “s”) will convert the date string to the format like “2008-07-01T00:00:00″ (this format is standard “sortable” date format). If you named date dimension other than “Date”, you need to change “[Date].[Date]” part accordingly.
