Numeric Functions | Microsoft OLE DB 2.0 Programmers Reference and Data Access SDK (Microsoft Professional Editions)

OLE DB Programmer's Reference

AGGREGATE(<set>[, <numeric_value_expression>])

This function uses the proper aggregate function based on the context. For example, consider the following expression:

WITH MEMBER Geography.Total AS AGGREGATE({USA, France}) SELECT {Measures.SumSales, Measures.MaxSales} ON COLUMNS,        {USA, France, Total} ON ROWS FROM SalesCube WHERE ([1991], [Products].[All], [SalesRep].[All],        [Quarters].[All])

In this expression, the calculated member Total is displayed against the measure SumSales and the measure MaxSales. In the former case, the provider computes Total by adding (with SUM), and in the latter case, the provider computes Total by taking the maximum. What happens when the <set> argument contains members from the MEASURES dimension is provider-specific.

AVG(<set>[, <numeric_value_expression>])

Computes the average of the tuples in <set> based on <numeric_value_expression>. Similar numeric functions are as follows:

MEDIAN(<set>[, <numeric_value_expression>])
MIN(<set>[, <numeric_value_expression>])
MAX(<set>[, <numeric_value_expression>])
VAR(<set>[, <numeric_value_expression>])
STDDEV(<set>[, <numeric_value_expression>])
RANK(<tuple>, <set>)

Note Each of these functions requires an implicit count of the number of cells, which does not include empty cells. To force the inclusion of empty cells, the application must use the COALESCEEMPTY function. For more information, see "Empty Cells" in this chapter.

COUNT(<set>[, INCLUDEEMPTY])

Counts the number of tuples in <set>. The optional INCLUDEEMPTY flag includes empty cells in the count. For more information, see "Empty Cells."

SUM(<set>[, <numeric_value_expression>])

Sums <set> based on an optional numeric value expression.

For example, the expression

SUM({USA, FRANCE}, Sales.VALUE)

will return the sum of sales for USA and France. This can also be written in a more intuitive way as follows:

SUM({USA, FRANCE}, Sales)

The production <numeric_value_expression> ::= <tuple>[.VALUE] is used, and the optional VALUE keyword is left out.

If a numeric value expression is not specified, it is implied by the other coordinates that appear in the MDX statement. For example, in the statement

WITH MEMBER Geography.NewMember AS SUM({USA, France}) SELECT NewMember ON COLUMNS,           Quarters.MEMBERS ON ROWS       FROM SalesCube WHERE ([1991], [SalesRep].[All], Sales, [Products].[All])

the ROWS expression provides a coordinate from the Quarters dimension. The WHERE clause provides the other four coordinates from the Years, Products, SalesRep, and Measures dimensions. These coordinates together identify a value in the cube for USA. They also identify a value for France. The SUM function adds these two values.