Thomas.I Microsoft BI & Analytics

I usually write MDX blog posts with a business problem as the start point and seldom posts about how MDX functions might work.  Here is one exception, the Item function that is useful in many analytic scenarios,  will be discussed with examples but only in an attempt to explain how it works.  I thank Chris Webb for helping me on some questions that I had.

 

We will start with a query to get a base result set to use with the Item function in the later examples. Run this MDX in management studio:

 

 

Select {[Measures].[Internet Sales Amount]} on 0,

CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])) On 1

From [Adventure Works];

 

 

 

Now let us continue with a simple Item example on the same MDX select that we have started with.

 

Select {[Measures].[Internet Sales Amount]} on 0,

 CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])).Item(0) On 1

From [Adventure Works];

 

The result is the first tuple in the set that you have seen in the starting query. The index that you enter into the Item() function starts with zero so this is a clear result.

 If you increase the index one by one you will walk down the first result set tuple by tuple, so there is nothing unclear about how the Item() function works with one Index.

 

Now we will add a second argument to the Item() function like Item().Item() and se how this works. Remember to refer to my first example at the top of this blog post.

 

Select {[Measures].[Internet Sales Amount]} on 0,

 CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])).Item(0).Item(1) On 1

From [Adventure Works];

 

 

 What you get as a result might require an explanation.  The query asks for the second member from the first tuple. That means that from the tuple (2001, Accessories) we are asking for Accessories.  What we get as a result is the sum of all Accessories in all the years in the first  query.

 

We can repeat the same behaviour for the second tuple with this query.

 

Select {[Measures].[Internet Sales Amount]} on 0,

 CrossJoin (Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]),

Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])).Item(1).Item(1) On 1

From [Adventure Works];

 

 Like in the previous example we will get a sum of all bike sales for all years.