Reporting Services date control issue using Analysis Services data source

Apr 17th, 2009
by John Sansom.

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:

  1. Go to “Data” tab in Report Designer.
  2. Choose the dataset that populates the report, and click the “…” button beside it.
  3. Now, the “Dataset” dialog window shows, and clicking the  “Parameters” tab in the dialog shows the list of parameters you may choose.
  4. 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.
  5. Now, change the value column to: =”[Date].[Date].&[” + Format(CDate(Parameters!Date.Value), “s”) + “]”
  6. Click “Ok” and close all windows.
  7. 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.

←
→
  • magomerlano

    Thanks for the tip, i knew about this issue but i wasn’t aware of the “s” format, much more handy than concatenating Year() Month() and so on! :-)
    Just one note: that should be no need to perform a CDate() since the parameters is of datetime type…

    • http://www.johnsansom.com John Sansom

      Hey, thanks for your comment. Glad you found this post useful!

  • Tracey

    Hi

    I have used your solution in my first OLAP based RS report and have overcome the issue for the large part. However, the dates available in the dimension only start in October 11. When I select a start date prior to this first valid date (or after the last date in the dim) I get the same error message “the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated”. How do I avoid this error showing if the date selected is outside of the available range?

  • Tracey

    Solution found for my problem: removed the “CONSTRAINED” keyword from the parameters in the MDX query. I then struck an issue where I had no data displayed if I selected a start date prior to the dates in the dimension.

    I then had to get the cube updated to allow all dates in the dimension (back to a certain year) rather than just those from the details of the cube data.

    Thanks for the help this blog post has given :)

    • http://www.johnsansom.com John Sansom

      Hey Tracey!

      Glad I could help, although in this case it looks like you solved your issue all by yourself :-) The best way to learn after all.

      Thanks for your comments.