MDX - SQL Server Analysis Services 2012 Cube Development - page 207

Limiting the query output
MDX supports multiple ways of limiting query results. You could use the WHERE
clause, often referred to as slicer , since it limits the result set by specifying a data
slice. You could also use the FILTER function to specify the criteria for members in-
cluded on each axis and thereby derive a more focused result set.
While reviewing the results of queries, as shown in the previous section, you probably
noticed that the result sets included some empty cells. As you might imagine, large
cubes could include many empty cells, and such data may or may not be desirable in
the query's output. You have a couple of options for limiting the output to only non-null
(non-empty) values.
How to do it...
Let's get started with limiting the query output.
1. Execute the following queries to limit the output to only the components
product category. As mentioned earlier, we can refer to a hierarchy member
by its name or by its key, so either of the following statements will return the
same output:
--Reference "components" category by
member name:
SELECT [Measures].[Reseller Sales Amount]
ON 0,
[Date].[Calendar].[Calendar Year].members
ON 1
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].[components]
--Reference "components" category by
member key:
SELECT [Measures].[Reseller Sales Amount]
ON 0,
[Date].[Calendar].[Calendar Year].members