Microsoft KB Archive/810440

= FIX: MDX Queries Return &quot;#Err&quot; as the Result Set When You Enable &quot;Visual Totals&quot; on Private Parent-Child Dimension =

Article ID: 810440

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



BUG #: 13661 (Plato7.x)



SYMPTOMS
When you run a Multidimensional Expressions (MDX) query against a private parent-child dimension that has Visual Totals enabled in the custom dimension security, the query returns &quot;#Err&quot; in the result set.

You also experience this problem when you click Test Role in the Cube Role Manager dialog box.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.



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

This problem was first corrected in Microsoft SQL Server 2000 Analysis Services Service Pack 3.



Steps to Reproduce the Problem
 In Analysis Manager, in the FoodMart 2000 database, expand the Cubes folder. Right-click the Budget cube, and then click Edit to start Cube Editor. Right-click the Account shared dimension, and then click Remove to delete the dimension. Right-click Dimensions, and then click New Dimension. Click Next in the Welcome to the Dimension Wizard. In the Dimension Wizard, follow these steps: <ol style="list-style-type: lower-alpha;"> In the Choose how you want to create the dimension page, click Parent-Child: Two related columns in a single dimension table, and then click Next.</li> In the Select the Dimension table page, click the Account table in Default Schema, and then click Next.</li> In the Select the columns that define the parent-child data hierarchy page, set the following options, and then click Next: <ul> Member key: Account_id</li> Parent key: Account_parent</li> Member name: Account_id</li></ul> </li> In the Select advanced options page, click Deselect All, and then click Next.</li> For the Dimension name value, type PrivateAccount, clear the Share this Dimension with other cubes check box, and then click Finish.</li></ol> </li> Save and process the Budget cube.</li> Quit Cube Editor.</li> Expand the Budget cube folder, right-click Cube Roles, and then click Manage Roles.</li> In the Cube Role Manager dialog box, click the All Users role, and then click Edit.</li> Click the Dimensions tab, and then set the Rule column value to Custom for the PrivateAccount dimension.</li> Click the … (ellipsis) button in the Custom Settings column of the PrivateAccount dimension.</li> In the Custom Dimension Settings: Budget^PrivateAccount dialog box, set the following options: <ul> <li>Basic tab: <ul> <li>Top Level: Level 02</li> <li>Bottom Level: Level 04</li> <li>Select members: Deselect all members</li> <li>Members pane: check member [5000]</li></ul> </li> <li>Common tab: <ul> <li>Click Enable-Show visual totals in Visual Totals, and then click OK.</li></ul> </li></ul> </li> <li>Click OK in the Edit a Cube Role dialog box.</li> <li>Click Test Role. &quot;#Err&quot; appears in the MeasuresLevel column of the result set.</li> <li> Run the following MDX query in the MDX Sample application, and &quot;#Err&quot; is displayed in the result set (do not run the query as a member of the OLAP Administrators group): Select {[Measures].[Amount]} on Rows, {[5000]} on Columns from budget </li></ol>

Keywords: kbsqlserv2000presp3fix kbfix kbbug KB810440

-

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

© Microsoft Corporation. All rights reserved.