Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

The ITEM() function with MDX code examples

Posted by thomasivarssonmalmo on May 20, 2009

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];

 
 

 

 

Internet Sales Amount

CY 2001

Accessories

 

CY 2001

Bikes

3 266 373,66

CY 2001

Clothing

 

CY 2001

Components

 

CY 2002

Accessories

 

CY 2002

Bikes

6 530 343,53

CY 2002

Clothing

 

CY 2002

Components

 

CY 2003

Accessories

293 709,71

CY 2003

Bikes

9 359 102,62

CY 2003

Clothing

138 247,97

CY 2003

Components

 

CY 2004

Accessories

407 050,25

CY 2004

Bikes

9 162 324,85

CY 2004

Clothing

201 524,64

CY 2004

Components

 

CY 2006

Accessories

 

CY 2006

Bikes

 

CY 2006

Clothing

 

CY 2006

Components

 

 

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.

 

 

Internet Sales Amount

CY 2001

Accessories

(null)

 
If you change the index in the Item() function in the pervious query to 1 you will get the second tuple in the starting query.
 
 

 

 

Internet Sales Amount

CY 2001

Bikes

3 266 373,66

 

 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];

 

 

Internet Sales Amount

Accessories

700 759,96

 

 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];

 

 

Internet Sales Amount

Bikes

28 318 144,65

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

 
Finally if we execute this statement we will see the total for the components product category that is null.
 
 

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

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

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

From [Adventure Works];

 

 

Internet Sales Amount

Components

(null)

 
I hope that these code examples can help with understanding how the Item functions works. This function is easy to understand as long as you use one argument or index but gets a little harder with two arguments or indexes.

Advertisements

2 Responses to “The ITEM() function with MDX code examples”

  1. Miky Schreiber said

    It’s good that someone writing about the Item function. When I started with MDX, I had to learn this by using running examples until I understood what’s happening.Note that you can use the Item function to reference any cell in the grid, in case you want to fetch a specific data from the query.

  2. Thomas said

    Miky, if you have an example of how I can reference single cells that would be interesting. The Item function is tricky and I left out a lot of examples that gave less clear results than the ones here. It is possible to fill several blog post about this function.

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