Share via

WHERE Clause Overrides

  • Article

Each individual set, member, tuple, or numeric function in an MDX statement always executes in the larger context of the entire statement. For example, consider the FILTER function in the following expression:

SELECT FILTER(SalesRep.MEMBERS, [1996].VALUE > 500) ON COLUMNS,
   Quarters.MEMBERS ON ROWS
FROM SalesCube
WHERE ([Geography].[All], [Products].[All], [1996], Sales)

The second argument of FILTER, "[1996].VALUE," does not contain enough information by itself. Six coordinates are needed ? one from each of the six dimensions ? to determine VALUE. The argument contains only one coordinate, from the Years dimension. In such a case, the other coordinates are obtained by looking at the following, in order:

  1. The rest of the axis specification; this yields (in the example above) the coordinate of the SalesRep dimension because the FILTER function iterates through each member of the SalesRep dimension.

  2. The slicer condition (WHERE clause) and picking up the coordinates for the slicer dimensions. This yields the coordinates for the Geography, Products, and Measures dimensions as (respectively) Geography.[All], Products.[All], and Measures.Sales.

  3. The default member for dimensions that appear neither on the axis nor on the slicer. Thus the default members are picked for the Quarters dimension.

A special case arises when a coordinate is specified both in the WHERE clause and within the expression. For example, suppose an application calls for a dataset that, on the COLUMNS axis, contains 1996 budgeted sales for all the states in the United States that had more than 500 units of ActualSales in 1995 and that, on the ROWS axis, contains the Quarters. This dataset can be created by the following statement:

SELECT FILTER({USA.CHILDREN}, ([1995], ActualSales) > 500) ON COLUMNS,
   Quarters.MEMBERS ON ROWS
FROM SalesCube
WHERE ([1996], BudgetedSales, [Products].[All], [SalesRep].[All])

As the FILTER function is evaluated for each state in the United States, it already has the coordinates ([1996], BudgetedSales) from the WHERE clause. However, it receives the coordinates ([1995], ActualSales) from the FILTER function. To avoid potential conflict, the argument of the FILTER function takes precedence. In general, any coordinates obtained from the WHERE clause are overridden by coordinates that are specified within an axis specification.