...
| Code Block |
|---|
select
{[Measures].[Sales]}
ON COLUMNS,
Filter( Order([Time].[Months].Members, [Time].CurrentMember.Name, DESC) ,
([Measures].[Sales] > 500000.0))
ON ROWS
from [SteelWheelsSales]
|
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.
...
| Code Block |
|---|
select {[Measures].[Sales]}
ON COLUMNS,
TopCount([Product].[Product].Members, 10.0, [Measures].[Sales])
ON ROWS
from [SteelWheelsSales]
|
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:
| Code Block |
|---|
select
{[Measures].[Sales]}
ON COLUMNS,
[Product].[Product].Members
ON ROWS
from [SteelWheelsSales]
|
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:
| Code Block |
|---|
select {[Measures].[Sales]}
ON COLUMNS,
[Product].[Product].Members
ON ROWS
from [SteelWheelsSales]
where([Time].[All Years].[2004])
|
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:
| Code Block |
|---|
select
{[Measures].[Sales]}
ON COLUMNS,
TopCount([Product].[Product].Members, 10.0, [Measures].[Sales])
ON ROWS
from [SteelWheelsSales]
where([Time].[All Years].[2004])
|
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.
...
| Code Block |
|---|
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]
|
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:
| Code Block |
|---|
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]
|
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:
| Code Block |
|---|
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]
|
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:
| Code Block |
|---|
select
{[Measures].[Sales]}
ON COLUMNS,
crossjoin( TopCount([Product].[Product].Members, 3, [Measures].[Sales]) ,
TopCount([Time].[Months].Members, 5, [Measures].[Sales]) )
ON ROWS
from [SteelWheelsSales]
|
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:
| Code Block |
|---|
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]
|
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.
...