WHERE Clause Overrides

OLE DB Programmer's Reference

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.

1998-2001 Microsoft Corporation. All rights reserved.



Microsoft Ole Db 2.0 Programmer's Reference and Data Access SDK
Microsoft OLE DB 2.0 Programmers Reference and Data Access SDK (Microsoft Professional Editions)
ISBN: 0735605904
EAN: 2147483647
Year: 1998
Pages: 1083

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net