Page Comparison

...

select {[Measures].[Sales]}
ON COLUMNS,
{[Time].[All Years].[2004].Children}
ON ROWS
from \[SteelWheelsSales

Image Added

All of these functions return sets of members, but what about creating sets of tuples? This is where another very common function, Crossjoin(), comes in: it takes two sets of members from different dimensions, performs a cartesian product and returns a set of tuples containing every single possible combination of members from each set. So, logically,

...

select
{[Measures].[Sales]} ON COLUMNS,
crossjoin(
[Time].[Years].Members,
[Markets].[All Markets].Children)
ON ROWS
from [SteelWheelsSales]

Image Added

Now we've got our sets, let's do something useful with them. For instance we might want to sort the items in the set in a particular order, and to do that we need to use the Order() function. Order() takes three parameters: the set you wish to sort, the MDX expression you wish to use to sort by, and whether you want to sort in ascending or descending order. Here's an example that sorts all of our Years by Sales:

select {[Measures].[Sales]}
ON COLUMNS,order(
[Time].[Years].Members,[Measures].[Sales],bdesc)
ON ROWS
from [SteelWheelsSales]

Image Added

The second parameter, the MDX expression you wish to order by, can either return a numeric value or a string value. In the example above we used the Sales measure but we could equally have used a tuple, and it's important to remember that this expression is independent of anything we are displaying on columns. So, for instance the following query shows Years ordered in the same way as in the previous query even though the ordering does not reflect the values actually displayed in the grid:

select
{([Measures].[Sales], [Markets].[All Markets].[Japan])}
ON COLUMNS,
Order([Time].[Years].Members, [Measures].[Sales], BDESC)
ON ROWS
from [SteelWheelsSales]

Image Added

This can be pretty confusing for people new to MDX ' we are sorting by Sales at the [All Markets] level but displaying Sales for Japan. If we wanted to sort by the values we're seeing in this query, we'd need to explicitly state that we wanted to sort by Sales for Japan by using a tuple in the second parameter:

select {([Measures].[Sales], [Markets].[All Markets].[Japan])}
ON COLUMNS,
Order([Time].[Years].Members, ([Measures].[Sales],
[Markets].[All Markets].[Japan]), BDESC)
ON ROWS
from [SteelWheelsSales]

Image Added

There's one last thing to notice about the Order() function, and that's the 'b' in front of the 'desc' in the last parameter. This 'b' stands for 'break the hierarchy': without it Order will first sort the member in the set you pass in into hierarchical order and then sort by the criteria you specify. You can see what this does by running the two queries which show all Months sorted by name ' the first breaks the hierarchy, so that you see all the Septembers, then all the Octobers, then all the Novembers and so on, while the second retains the hierarchy so you see all the Months for 2003 sorted by name, then all the Months for 2004 sorted by name and all the Months for 2005 sorted by name.

select
{([Measures].[Sales], [Markets].[All Markets].[Japan])}
ON COLUMNS,
Order([Time].[Months].Members, [Time].CurrentMember.Name, BDESC)
ON ROWS
from [SteelWheelsSales]select {([Measures].[Sales], [Markets].[All Markets].[Japan])}
ON COLUMNS,
Order([Time].[Months].Members, [Time].CurrentMember.Name, DESC)
ON ROWS
from [SteelWheelsSales]

Image Added

Another thing we might want to do with our set is filter it somehow. Looking the query we've just run we can see that there are several rows with no values and we might want to remove those rows from the resultset. The easiest way to do is to add NON EMPTY before the beginning of our row axis definition, as follows:

select
{([Measures].[Sales], [Markets].[All Markets].[ Japan ])}
ON COLUMNS,
NON EMPTY Order([Time].[Months].Members, [Time].CurrentMember.Name, DESC)
ON ROWS
from [SteelWheelsSales]

Image Added

NON EMPTY has removed all rows where every value on that row is a null, and if we added it to the columns axis it would do the same there. To implement more demanding filter criteria, though, we need to use the Filter() function. It takes two parameters: the set you wish to filter and an MDX expression which returns a Boolean value which will be evaluated for every item in the set. Here's an example which only returns Months where Sales is greater than 500000:

select
{[Measures].[Sales]}
ON COLUMNS,
Filter( Order([Time].[Months].Members, [Time].CurrentMember.Name, DESC) ,
([Measures].[Sales] > 500000.0))
ON ROWS
from [SteelWheelsSales]

Image Added

This also illustrates the fact that you can nest functions like Filter() and Order() which take a set as one of their parameters and return a set.

...

select {[Measures].[Sales]}
ON COLUMNS,
TopCount([Product].[Product].Members, 10.0, [Measures].[Sales])
ON ROWS
from [SteelWheelsSales]

Image Added

For people coming to MDX who have more than a passing knowledge of SQL, it's easy to get confused by the fact that you need to filter using functions in this way and not using the WHERE clause. MDX also has a WHERE clause but it never directly filters what you can see on the rows and columns of your query ' it acts more as a third axis, one member thick, which slices the resultset. Consider the following query:

select
{[Measures].[Sales]}
ON COLUMNS,
[Product].[Product].Members
ON ROWS
from [SteelWheelsSales]

Image Added

It has no WHERE clause and it doesn't mention the Time dimension at all, but that doesn't mean we're not slicing by something on Time ' we are, we're seeing values for the All member on Time. When we introduce a WHERE clause we can explicitly state which member on the Time dimension we want to see values for, for example the Year 2004:

select {[Measures].[Sales]}
ON COLUMNS,
[Product].[Product].Members
ON ROWS
from [SteelWheelsSales]
where([Time].[All Years].[2004])

Image Added

Notice that while the Products that appear on the rows haven't changed, the values for Sales have ' we're now seeing the Sales for the Year 2004 alone. So a WHERE clause can't directly affect which members appear on an axis, but it can do so indirectly if you're applying some kind of filter to that axis. If we go back to our Topcount() example and add a WHERE clause specifying that we see values for 2004 alone, we'll only see the top 10 products for 2004:

select
{[Measures].[Sales]}
ON COLUMNS,
TopCount([Product].[Product].Members, 10.0, [Measures].[Sales])
ON ROWS
from [SteelWheelsSales]
where([Time].[All Years].[2004])

Image Added

We can now see that the second-highest ranking product is the 2001 Ferrari Enzo and not the 1952 Alpine Renault ' the Topcount function has taken into account the WHERE clause in its filter. In fact all the filter functions we've looked at in this article so far will do this ' but why do they take the WHERE clause into account when they don't take what's on the opposing axis into account, as we saw earlier with the Order function sorting by something other than what was on columns? Simply because they can: the WHERE clause is an axis one member thick so logically there's no problem about which value to use for the filtering ' this is only one value to use, the value that's displayed. On the other axes there is potentially more than value to take into account (for example on the rows axis there could be more than one column to choose from) so MDX instead ignores these axes and makes you explicitly state what you want to filter by.

...

select {[Measures].[Sales]}
ON COLUMNS,
union( filter( [Time].[Months].Members, ([Measures].[Sales],
[Time].Currentmember)<200000 ) ,
filter( [Time].[Months].Members, ([Measures].[Sales],
[Time].Currentmember)>500000 ) )
ON ROWS
from [SteelWheelsSales]

Image Added

The Except() function takes the removes all members found in one set from another. So, for example, the following query shows all Months with Sales greater than 200000 except those which have Sales greater than 500000:

select
{[Measures].[Sales]}
ON COLUMNS,
except(
filter( [Time].[Months].Members, ([Measures].[Sales],
[Time].Currentmember)>200000 ) ,
filter( [Time].[Months].Members, ([Measures].[Sales],
[Time].Currentmember)>500000 ) )
ON ROWS
from [SteelWheelsSales]

Image Added

The Intersect() function returns only those members present in both sets passed into it, so for example the following query returns the Months which have Sales greater than 200000 and Sales less than 500000:

select {[Measures].[Sales]}
ON COLUMNS,
intersect(
filter( [Time].[Months].Members, ([Measures].[Sales],
[Time].Currentmember)>200000 ) ,
filter( [Time].[Months].Members, ([Measures].[Sales],
[Time].Currentmember)<500000 ) )
ON ROWS
from [SteelWheelsSales]

Image Added

Lastly, it's possible to iterate over every member of a set and perform a set operation at each iteration. Why would you need to do this? Let's say that you've found our top 3 selling Products and you want to find the top 5 Months for each of these Products. You might think that the following query would do the trick:

select
{[Measures].[Sales]}
ON COLUMNS,
crossjoin( TopCount([Product].[Product].Members, 3, [Measures].[Sales]) ,
TopCount([Time].[Months].Members, 5, [Measures].[Sales]) )
ON ROWS
from [SteelWheelsSales]

Image Added

However, the fact that the top 5 Months for each Product are the same might make you suspicious and in fact further investigation will show that this query does not return what we wanted. It actually returns the Crossjoin of the top 3 Products for all Months and the top 5 Months for all Products, although the Sales values for each Product/Month tuple are correct. What we need to do is use the Generate() function to iterate through the set of Products and for each Product return the top 5 Months for that Product as follows:

select {[Measures].[Sales]}
ON COLUMNS,
Generate( TopCount([Product].[Product].Members, 3, [Measures].[Sales]) ,
Crossjoin(
{[Product].Currentmember}
,
TopCount([Time].[Months].Members, 5, [Measures].[Sales]) ) )
ON ROWS
from [SteelWheelsSales]

Image Added

In this case Generate is being passed the set of the top 3 Products as its first parameter, and then for each of these three Products it Crossjoins the Currentmember on Product (the current Product in the iteration) with the top 5 Months which produces the result we're looking for.

...