Home > MDX Challenges > MDX #15 – Why do I get Year, Semester, Quarter when I only need Month?

MDX #15 – Why do I get Year, Semester, Quarter when I only need Month?

Descendants() function gives me the exact level in the Date hierarchy in SSMS

In MDX #14, I used this query to get the internet sales amount for each month.

clip_image001

The results above are from SSMS, and they are what I was expecting.

In SSRS, the same MDX query will give more than what the Descendants() function is asking

Run this same MDX query in the Query Designer in SSRS, this is what you will get.

clip_image002

We’ve noticed two differences.

  1. Unlike the results from the SSMS where the returns on the Y axis are not given any column names, all attributes from the Date dimension has a column name, and each column name is the same as the level name in the Calendar hierarchy.
  2. Unlike the results from the SSMS where only the Month is returned, the SSRS returned 3 more levels in the calendar hierarchy  that are above the level Month.

Bad feature or good feature?

Having a column name is certainly a good thing in Reporting Services. But getting columns more than what I asked for can be annoying, although I can, on the other hand, also view it as a good feature, in case I need Year, Semester or Quarter for other purposes such as sorting. 

  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: