Share via

Numeric Functions (OLE DB)

  • Article

This topic demonstrates the syntax of numeric functions.

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.

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 following expression will return the sum of sales for USA and France:

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

This expressions 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 following statement, 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.

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])