• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

Reporting Services date control issue using Analysis Services data source

April 17, 2009 by John Sansom 8 Comments

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.

Filed Under: Reporting Services

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy