In this example, we are defining the factorial of a number. You may notice the usage of a recursive notation.
Query
WITH
FUNCTION factorial(n) AS Iif( n = 0, 1, n * factorial(n-1) )
MEMBER f0 AS factorial(0)
MEMBER f3 AS factorial(3)
SELECT { f0, f3 } ON 0 FROM [Sales]
Result
Assertion : Cell Equals
WITH
MEMBER f0 AS 1
MEMBER f3 AS 6
SELECT { f0, f3 } ON 0 FROM [Sales]
In this example, we are returning a member to demonstrate that functions are not limited to scalar value.
Query
WITH
FUNCTION f() AS [Measures].defaultMember
SELECT
f() ON 0,
[Geography].[Geo].[America].children ON 1
FROM
[Sales]
Result
|
Amount |
| Canada |
|
| Mexico |
|
| United States |
768 |
| Venezuela |
|
Assertion : Cell Equals
SELECT
[Measures].defaultMember ON 0,
[Geography].[Geo].[America].children ON 1
FROM
[Sales]
A function argument may be of any OLAP type; in this example we are passing a level as parameter and returning the set of members of this level.
Query
WITH
FUNCTION f(lvl) AS lvl.members
SELECT
[Measures].defaultMember ON 0,
f( [Geography].[Geo].[Country] ) ON 1
FROM
[Sales]
Result
|
Amount |
| Canada |
|
| Mexico |
|
| United States |
768 |
| Venezuela |
|
| France |
4 |
| Spain |
3 |
| Switzerland |
248 |
Assertion : Cell Equals
SELECT
[Measures].defaultMember ON 0,
[Geography].[Geo].[Country].members ON 1
FROM
[Sales]
A function is always evaluated taking into account the current context. The function defined in this example is returning the value of the default measures for the tuple in the current context of evaluation as defined by the axis and the slicer.
Query
WITH
FUNCTION f() AS ( [Geography].[Economy].currentMember, [Measures].[Amount] )
MEMBER x AS f()
SELECT
x ON 0,
[Time].[Calendar].[Year].members ON 1
FROM
[Sales]
WHERE
[Geography].[Economy].[United States]
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[x] AS ( [Geography].[Economy].[United States], [Measures].[Amount] )
SELECT
[Measures].[x] ON 0,
[Time].[Calendar].[Year].members ON 1
FROM
[Sales]
In this example, we are defining a function whose evaluation is declared constant. As it cannot see the current context, the constFn() is evaluating the default country (as opposed to the current member) which is the all member.
Query
WITH
CONST FUNCTION constFn() AS ( [Measures].defaultMember, [Geography].[Geo].currentMember )
FUNCTION nonConstFn() AS ( [Measures].defaultMember, [Geography].[Geo].currentMember )
MEMBER [Measures].[all-countries] AS constFn()
MEMBER [Measures].[current-country] AS nonConstFn()
SELECT
{ [Measures].[all-countries], [Measures].[current-country] } ON 0,
[Geography].[Geo].[Country].members ON 1
FROM [Sales]
Result
|
all-countries |
current-country |
| Canada |
1023 |
|
| Mexico |
1023 |
|
| United States |
1023 |
768 |
| Venezuela |
1023 |
|
| France |
1023 |
4 |
| Spain |
1023 |
3 |
| Switzerland |
1023 |
248 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[all-countries] AS ( [Measures].defaultMember, [Geography].[Geo].[All] )
MEMBER [Measures].[current-country] AS ( [Measures].defaultMember, [Geography].[Geo].currentMember )
SELECT
{ [Measures].[all-countries], [Measures].[current-country] } ON 0,
[Geography].[Geo].[Country].members ON 1
FROM [Sales]
In this example, we are defining a function whose evaluation is declared constant. As it cannot see the current context (in that example the slicer), the usage of .currentMember() is therefore equivalent to .defaultMember().
Query
WITH
CONST FUNCTION constFn() AS [Geography].[Geo].currentMember.NAME
FUNCTION nonConstFn() AS [Geography].[Geo].currentMember.NAME
MEMBER [Measures].[const] AS constFn()
MEMBER [Measures].[non-const] AS nonConstFn()
SELECT { [Measures].[const], [Measures].[non-const] } ON 0 FROM [Sales]
WHERE [Geography].[Geo].[United States]
Result
| const |
non-const |
| All Regions |
United States |
Assertion : Cell Equals
WITH
MEMBER [Measures].[const] AS [Geography].[Geo].defaultMember.NAME
MEMBER [Measures].[non-const] AS [Geography].[Geo].[United States].NAME
SELECT { [Measures].[const], [Measures].[non-const] } ON 0 FROM [Sales]
In this example, we are defining a function whose evaluation is declared constant. As it cannot see the current context (in that example the slicer), the default measure for 'All' the regions is not equals to the [United States] as specified in the slicer.
Query
WITH
CONST FUNCTION constFn() AS ( [Measures].defaultMember, [Geography].[Geo].[All] ).Value
FUNCTION nonConstFn() AS ( [Measures].defaultMember, [Geography].[Geo].[All] ).Value
MEMBER [Measures].[const] AS constFn()
MEMBER [Measures].[non-const] AS nonConstFn()
SELECT { [Measures].[const], [Measures].[non-const] } ON 0 FROM [Sales]
WHERE [Geography].[Economy].[United States]
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[const] AS ( [Measures].defaultMember, [Geography].[Geo].[All] ).Value
MEMBER [Measures].[non-const] AS ( [Measures].defaultMember, [Geography].[Geo].[United States] ).Value
SELECT { [Measures].[const], [Measures].[non-const] } ON 0 FROM [Sales]
In this example, we are defining a function whose evaluation is declared constant. As it cannot see the current context (in that example the sub-select), the [Geography] dimension is fully visible and therefore the [France] is not filtered out.
Query
WITH
CONST FUNCTION constFn() AS IIF( [Geography].[Geo].[France] IS NULL, [Geography].[Geo].[unknown], [Geography].[Geo].[All] )
FUNCTION nonConstFn() AS IIF( [Geography].[Geo].[France] IS NULL, [Geography].[Geo].[United States], [Geography].[Geo].[unknown] )
SELECT { constFn(), nonConstFn() } ON 0
FROM (SELECT [Geography].[Geo].[United States] ON 0 FROM [Sales] )
Result
| All Regions |
United States |
| 768 |
768 |
Assertion : Cell Equals
SELECT { [Geography].[Geo].[All], [Geography].[Geo].[United States] } ON 0
FROM (SELECT [Geography].[Geo].[United States] ON 0 FROM [Sales] )
In this example, we are defining a function whose evaluation is declared constant. As it cannot see the current context (in that example the sub-select), the value of all the regions is not filtered by the sub-select specifying [United States] only.
Query
WITH
CONST FUNCTION constFn() AS [Geography].[Geo].[All].Value
FUNCTION nonConstFn() AS [Geography].[Geo].[All].Value
MEMBER [XX] AS 1
MEMBER [YY] AS 0
SELECT
{ IIF( constFn() > [Geography].[Geo].[United States], [XX], [YY] ), IIF( nonConstFn() = [Geography].[Geo].[United States], [XX], [YY] ) } ON 0
FROM (SELECT [Geography].[Geo].[United States] ON 0 FROM [Sales] )
Result
Assertion : Cell Equals
WITH
MEMBER [XX] AS 1
MEMBER [YY] AS 0
SELECT
{ [XX], [XX] } ON 0
FROM (SELECT [Geography].[Geo].[United States] ON 0 FROM [Sales] )
A const function cannot access named set as they are evaluated according to the current context.
Query
WITH
SET myCountry AS { [Geography].[Geo].currentMember }
CONST FUNCTION f() AS myCountry
SELECT f() ON 0 FROM [Sales]
WHERE [Geography].[Geo].[United States]
Result
A const function cannot access calculated member as they are evaluated according to the current context.
Query
WITH
MEMBER myCountryValue AS ( [Measures].defaultMember, [Geography].[Geo].currentMember )
CONST FUNCTION f() AS myCountryValue
MEMBER constant AS f()
SELECT { constant } ON 0 FROM [Sales]
WHERE [Geography].[Geo].[United States]
Result
| constant |
| #ERROR : (OLAP_CONST_FUNCTION_CALC_MEMBER_USAGE) |
Error Code : OLAP_CONST_FUNCTION_CALC_MEMBER_USAGE
Error Message : f() : a const function cannot use any calculated member : [Measures].[myCountryValue]