Microsoft KB Archive/322965

= BUG: Excel Mode Function Returns #ERR in MDX Calculated Member =

Article ID: 322965

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services
 * Microsoft SQL Server OLAP Services

-



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.



Steps to Reproduce Behavior
 Edit the Sales cube in the FoodMart sample database.  Add a calculated member named TestModeFunc, and give it this expression: Mode(3,3,4)  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.

Additional query words: MODE Excel MDX Calculated Member UDF OLAP

Keywords: kbbug kbnofix KB322965

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.