In MDX we have the Item function which can be used in a number of ways. It is important to understand how it works and how it can be used to our advantage.
As a start, we can call Item over a set or over a tuple:
{set}.Item(0) or (tuple).Item(0)
It may be important to note that when we call Item over a set, we get a tuple out of it (sets are collections of tuples), while if we call it over a tuple we get a member.
If we use Item with a tuple, we must specify as an argument the integer position of the member within the tuple which we want. However, when we works with sets, we can either do the same, or specify a number of strings, which identify specific tuples. Some examples:
Item with a tuple:
(a,b).Item(0) = a
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
([Product].[Category].&[4], [Date].[Calendar].[Calendar Year].&[2008]).Item(0)
} ON 1
FROM [Adventure Works]
(a,b).Item(1) = b
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
([Product].[Category].&[4], [Date].[Calendar].[Calendar Year].&[2008]).Item(1)
} ON 1
FROM [Adventure Works]
Item with a set:
{a,b,c}.Item(0) = a
WITH
SET SET1 AS
{ ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
}
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
SET1.Item(0)
} ON 1
FROM [Adventure Works]
{a,b,c}.Item(“a”) = a
WITH
SET SET1 AS
{ ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
}
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
SET1.Item("([Product].[Category].&[4],
[Date].[Calendar].[Calendar Year].&[2008])")
} ON 1
FROM [Adventure Works]
{(a1,b1),(a2,b2),(a3,b3)}.Item(“a1″,”b1”) = (a1,b1)
WITH
SET SET1 AS
{ ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
}
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
SET1.Item("[Product].[Category].&[4]",
"[Date].[Calendar].[Calendar Year].&[2008]")
} ON 1
FROM [Adventure Works]
When we specify a number of strings as arguments, we get the tuple which is defined by these strings/coordinates.
Now, let’s see what happens when we have a set of tuples and we use Item on it with a single argument:
{(a1,b1),(a2,b2),(a3,b3)}.Item(0) = (a1,b1)
WITH
SET SET1 AS
{ ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
}
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
SET1.Item(0)
} ON 1
FROM [Adventure Works]
We get a tuple back. Therefore, if we use a second Item function over the first one, we will get the member on that position from the tuple:
{(a1,b1),(a2,b2),(a3,b3)}.Item(0).Item(0) = (a1,b1).Item(0) = a1
To illustrate the concept:
WITH
SET SET1 AS
{ ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
}
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
SET1.Item(0).Item(0)
} ON 1
FROM [Adventure Works]
This gives us the whole amount for Accessories, while:
WITH
SET SET1 AS
{ ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
}
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
SET1.Item(0).Item(1)
} ON 1
FROM [Adventure Works]
gives us the total amount for 2008.
Even if we do:
WITH
SET SET1 AS
{ ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
}
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
SET1.Item(0).Item(0).Item(0).Item(0).Item(0).Item(0).Item(0).Item(0)
} ON 1
FROM [Adventure Works]
we still get the amount for accessories.
What happens here is:
- With the first call of Item(0) over SET1 we get the first tuple from the set (in our case it is ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008])).
- Then with the second call, we get the first member of this tuple – [Product].[Category].&[4].
- Now, with the third call of Item(0) over this member, we get the first member from the implicitly converted to tuple member from the previous step. Therefore, we pull out the first member from it which is ([Product].[Category].&[4]).
- From here onwards we flip between a tuple and a member as a result every time we call Item(0).
But if we do:
WITH
SET SET1 AS
{ ([Product].[Category].&[4],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[1],[Date].[Calendar].[Calendar Year].&[2008]),
([Product].[Category].&[3],[Date].[Calendar].[Calendar Year].&[2008])
}
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
SET1.Item(0).Item(1).Item(1).Item(0).Item(0).Item(0).Item(0).Item(0)
} ON 1
FROM [Adventure Works]
we get nothing back. This is because there is no element on the second position/coordinate of the tuple ([Date].[Calendar].[Calendar Year].&[2008]).
Therefore calling Item(0) after another Item(0) is rarely necessary and should be done only if we need it, because we could either get wrong results or possibly hurt our query performance.
Note: Please read the comments below, where you can find an in-depth discussion about the concepts in this article.
Boyan Penev on Microsoft BI 
You must be logged in to post a comment.