Here’s a solution to an issue that you may have come up against when designing a SQL Server Reporting Services (SSRS) report requiring Datetime parameters, that uses a SQL Server Analysis Services (SSAS) data source.
What’s the problem?
When you create a dataset from a SSAS data source, by default the date values returned from a given Dimension are treated as strings. Now in order to use the Calendar control that is built into SSRS, the report parameters need to be defined as Datetime data types.
So naturally you go ahead and change the parameter data type and execute the report however, doing so raises an error of the form below.
An error occured during local processing.
An error has occured during report processing.
Query execution failed for data set ‘TestCalendarDataSet’.
Query(47,14) the restrictions imposed by the CONSTRAINED flag in the
STRTOSET function were violated.
Why does this happen?
This occurs because the data returned by the Calendar control (a datetime value) is not appropriate, as the MDX query is expecting a Dimension Memeber value.
For example, the MDX code with the datetime parameter is of the form:
STRTOMEMBER(@Date, CONSTRAINED)
And substituting an actual datetime parameter results in:
STRTOMEMBER(“2009-01-01”, CONSTRAINED)
Clearly “2009-01-01” is not a valid MDX Member Value. A valid value needs to be of the form:
STRTOMEMBER([Date].[Date].&[“2009-01-01T00:00:00”], CONSTRAINED)
How can I fix it?
In order to make using a datetime parameter with a SSAS data source work, you need to manually convert the parameter value, which is a string, to an MDX Member value.
Here’s how:
- Go to “Data” tab in Report Designer.
- Choose the dataset that populates the report, and click the “…” button beside it.
- Now, the “Dataset” dialog window shows, and clicking the “Parameters” tab in the dialog shows the list of parameters you may choose.
- Assume the calendar control parameter is @Date, in the “Parameters” tab, you will have “Date” in the “Name” column, and “=Parameters!FromDateDate.Value” in the “Value” column.
- Now, change the value column to: =”[Date].[Date].&[” + Format(CDate(Parameters!Date.Value), “s”) + “]”
- Click “Ok” and close all windows.
- Click “Preview” to test the result.
Summary
In order to use the Calendar control for a SSRS report that encompasses a SSAS data source, the string value of the datetime parameter must be converted to an MDX Memeber Value before being passed to the data source.
I hope this tip helps you with your report designing endeavours. I really enjoy reading all your comments and hearing about your experiences with SQL Server technology, so keep your feedback coming.
