Export (0) Print
Expand All

Infinite recursion detected during execution of calculated member <member name>

SQL Server 2000

Infinite recursion detected during execution of calculated member <member name>

Message Text

Infinite recursion detected during execution of calculated member <member name>.

Explanation

This error occurs when the definition of a calculated member, custom rollup formula, custom member, or calculated cell refers to itself recursively. Infinite recursion can sometimes be difficult to discover within a Multidimensional Expressions (MDX) expression, because the source of the recursion may not be readily apparent. Two examples are presented here to demonstrate obvious and subtle ways in which infinite recursion can occur within MDX expressions.

In the first example, a calculated measure that sums the value of the Sales measure for the year to date is defined using the following MDX expression:

Sum(YTD())

Because the Sales measure is not referenced within the expression, the current measure is used instead to evaluate the calculated measure. However, the calculated measure is the current measure, so the expression recursively refers to itself.

Another less obvious example is a calculated measure that counts the number of members in the Products dimension that have associated transactions. Suppose the following MDX expression  is used to define the calculated measure:

Count([Products].[Members])

While this expression does not cause recursion, it also counts both empty and non-empty members and does not provide the desired value for the calculated measure. The following MDX expression attempts to resolve this issue by excluding empty members

Count([Products].[Members], EXCLUDEEMPTY)

This expression satisfies the requirements of the example, excluding empty members from the Products dimension. However, the values of the members in the Products dimension must be calculated first in order to determine whether a member is empty. Because a measure was not specified in the MDX expression, the current measure is again used and infinite recursion occurs as a result.

Action

Examine the MDX expression that defines the calculated member, custom rollup formula, custom member, or calculated cell in question and ensure that the expression does not explicitly or implicitly reference itself. Typically, such actions involve explicitly specifying an implied member or measure within the MDX expression. The following MDX expressions demonstrate how to resolve the infinite recursion issues described earlier.

To resolve the problem in the first example, the following MDX expression directly references the Sales measure, removing the infinite recursion:

Sum(YTD(), [Measures].[Sales])

To resolve the problem in the second example, the following MDX expression uses the DefaultMember MDX function to specify that the default measure be used when the expression is resolved:

Count([Products].[Members]*{[Measures].DefaultMember}, EXCLUDEEMPTY)

If you are unable to resolve this problem, contact your primary support provider.

See Also

MDX

Count

DefaultMember

Sum

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft