Home > MDX Challenges > MDX #16 – Attribute [Month Name] Is Invisible

MDX #16 – Attribute [Month Name] Is Invisible

In SSRS user-defined hierarchy Calendar returns the entire hierarchy

Continuing from my previous post, MDX #15 – Why do I get Year, Semester, Quarter when I only need Month?.

clip_image001

The above query gave me the months and each month’s sales amount in SSMS, but gave me the entire hierarchy in Calendar in SSRS, which includes Year, Semester, Quarter and Month.

Use Attribute Hierarchy Instead

I am not particularly fond of this unexpected feature in SSRS. In most of the SSRS reports, I need to have control of

  • which columns to return from the datasets, and
  • what the column names are for each dataset

Fortunately, SSAS provides so much flexibility in the dimension design that allows us the same level of flexibility (and more) in how we write our MDX queries.

Month is the fourth level down in the Calendar hierarchy. In the Properties window, we can see that the Month Name is the source attribute.

clip_image002

This is a good news. Instead of using the Calendar hierarchy, I can use the Month Name attribute in my query. Hopefully, SSRS will only give me back the Month.

On the Y axis, use [Date].[Month Name].[Month Name], we get exactly one column back, [Month Name] from the Query Designer in SSRS.

clip_image003

Use both user-defined hierarchies and attribute hierarchies in MDX queries

Don’t forget that we have the flexibilities to use both user-defined hierarchies and attribute hierarchies in MDX queries.

Even if attribute hierarchies are invisible

Attribute [Month Name] is not a visible attribute. In BIDS you can see that the AttributeHierarchyVisible property is set to False. When you browse the Date dimension in SSMS,  [Month Name] is no where to be found. This doesn’t mean that we cannot use it in our MDX queries.

clip_image004

Categories: MDX Challenges Tags: ,
  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: