A WHERE clause in MDX works in a similar way as the other query languages. It acts as a filter and restricts the data returned in the result set.
Not surprisingly, however, the WHERE clause in MDX does more than just restricting the result set. It also establishes the "query context".
The MDX WHERE clause points to a specific intersection of cube space. We use tuple expressions to represent cells in cube space. Each tuple is made of one member, and only one member, from each hierarchy.
The following tuple points to one year, 2008 and one measure, the [Internet Sales Amount]:
( [Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2008] )
Using a tuple in an MDX WHERE clause is called "slicing" the cube. This feature gives the WHERE clause another name, slicer. If we put the previous tuple in the WHERE clause, in MDX terms, we are saying, "show me some data from the cube sliced by sales and the year 2008".
That is what we are going to do next.
Open the Query Editor in SSMS, and then follow these steps to write a query with a slicer and test it:
Copy this initial query into the Query Editor and run the query. You will see the following result:
SELECT { [Customer].[Customer Geography].[Country] } ON 0, { [Product].[Product Categories].[Category] } ON 1 FROM [Adventure Works]At this point, we should ask the question, "What are the cell values?" The cell values are actually the
[Measures].[Reseller Sales Amount], which is the default member on theMeasuresdimension.Add the previous tuple to the query as a slicer. Here is the final query:
SELECT { [Customer].[Customer Geography].[Country] } ON 0, { [Product].[Product Categories].[Category] } ON 1 FROM [Adventure Works] WHERE ( [Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2008] )The result should be as shown in the following screenshot:
Ask the question again, "What are the cell values?" The cell values are now the
[Measures].[Internet Sales Amount], and no longer the default measure.
We can slice the data by pointing to a specific intersection of cube space. We can achieve this by putting a tuple in the WHERE clause.
In the preceding example, the cube space is sliced by sales and year 2008. The cell values are the Internet Sales Amount for each country and each product category, sliced by year 2008.
Notice that the data returned on the query axes can be completely different from the tuple in the WHERE clause. The tuples in the slicer will only affect the cell values in the intersection of rows and columns, not what are on the columns or rows axes.
If you need to display sales and year 2008 on the query axes, you would need to move them to the query axes, and not in the WHERE clause.
This query has moved the sales to the columns axis, and the year 2008 to the rows axis. They both are "crossjoined" to the original hierarchies on the two query axes:
SELECT
{ [Measures].[Internet Sales Amount] *
[Customer].[Customer Geography].[Country]
} ON 0,
{ [Date].[Calendar Year].&[2008] *
[Product].[Product Categories].[Category]
} ON 1
FROM
[Adventure Works]Run the query and you will get the following result. The call values are the same as before, but now we have the year 2008 on the rows axis, and the Internet Sales Amount on the columns axis.
