BUG: Excel Mode Function Returns #ERR in MDX Calculated Member (322965)


The information in this article applies to:

  • Microsoft SQL Server 2000 Analysis Services
  • Microsoft SQL Server OLAP Services 7.0
This article was previously published under Q322965
BUG #: 12577 (plato7x)

SYMPTOMS

When you use the Microsoft Excel Mode function in a calculated member for a cube, the calculated member returns #ERR instead of the Mode of the input values. If you double-click the #ERR value, you will receive this error message:
Unable to display cell properties. The operation has failed because of an error in the COM component An error occurred during attempt to get a cell value.

CAUSE

The Excel Mode function documentation indicates that it accepts integer arguments; however, it actually expects real arguments.

WORKAROUND

To work around this problem, express all the arguments to the Mode function by using decimal values.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Edit the Sales cube in the FoodMart sample database.
  2. Add a calculated member named TestModeFunc, and give it this expression:
    Mode(3,3,4)
  3. Save the calculated member, and then view the data.
The expected result for the TestModeFunc calculated member is a value of 3; however, TestModeFunc displays #ERR. If you double-click the #ERR, you receive the following error message:
Unable to display cell properties. The operation has failed because of an error in the COM component An error occurred during attempt to get a cell value.


To fix the problem, redefine the TestModeFunc calculated member as: 
Mode(3.0,3.0,4.0)
Now, the TestModeFunc member has the value you expected.
Modification Type:MinorLast Reviewed:7/14/2004
Keywords:kbbug kbnofix KB322965
©2004 Microsoft Corporation. All rights reserved.