SSRS Gotcha!–Using SSAS Time Intelligence in SSRS Reports

Today I had the opportunity to revisit a problem I encountered previously; but, today was different. Today I found the solution.

Adding Time Intelligence to your SSAS cube using the Business Intelligence Wizard is an easy way to add a whole bunch of useful information to your solution. It is one of my favorite topics when teaching boot camps or quick starts because it adds so much functionality to your Analysis Services solution without a lot of work. One day, I decided to create an SSRS report based on one of my SSAS databases that utilized this feature and encountered a strange error when I tried to filter the result set in the SSRS query designer by adding the time intelligence attribute to the filter screen.

“A set has been encountered that cannot contain calculated members”

Allow me to demonstrate…

  1. Start a solution in BIDS by selecting File, New Project, Import Analysis Services Database, and selecting your existing “Adventure Works DW 2008R2” solution.
  2. Once complete, double click the Adventure Works cube in the solution exploder and select Cube, Add Business Intelligence from the File menu up top.
  3. Define time intelligence is selected by default, so click next. Change the hierarchy “to analyze time calculations” from date/fiscal to date/calendar and check all of the available time calculations.
  4. Click the Select All button for the available measures and click Finish.
  5. Process your solution
  6. Close BIDS
  7. Open BIDS
  8. Click File, New Project, and select Report Server Project
  9. Create a data source to your SSAS database
  10. Start a new report and use the query designer
  11. Open up your Date Dimension, Calendar hierarchy and drag Date.Calendar Date Calculations to the bottom right pane in the query designer.
  12. Drag date.Calendar hierarchy to the bottom right pane, Open up measures, Internet Sales and drag Internet Sales amount to the bottom right pane in query designer. Scroll down the analyze the values.
  13. Now, for the fun part… drag Date.Calendar Date Calculations to the top right section to filter on a specific time calculation. I used month over month growth %.

image_thumb2

“A set has been encountered that cannot contain calculated members.”

Well, I don’t want to bore you anymore than necessary… Here is the fix:

Edit your data source connection string by appending “;Subqueries = 1” at the end like so:

image_thumb6

That’s all there is to it! Go back to the query designer if you don’t believe me.

image_thumb10

The blog post that gave me the clue was from Chris Webb: Subselects and Calculated Members in R2

Thank you Mr. Webb!

One more thing… the built in time intelligence wizard in SSAS is limited because you have to apply the time intelligence to each of the date dimensions in your cube which can lead to quite a lot of query processing overhead. There is a whitepaper with an alternate method which can apply these MDX formulas to all of your (role playing) date dimensions in one sweep. Also, in this zip file is a word document that describes how to apply Cognos style relative date time intelligence.

You can download it here.

I hope you found this post useful. If you have found a better way, please let us know!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s