Issue with calculated member in MSAS 2005 Vs 2000 - by AshokDugaputi

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 436036 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 4/29/2009 4:17:44 AM
Access Restriction Public


In my Finance cube (Similar to Adventure Works Finance cube), I had defined an Account dimension in MSAS 2000.

Key       Name                                                    Member Type              Formula
4008    ~ Gross Profit                                         Rollup member
4002    - Operating Expenses                               Rollup  member
4175       + Operating Expense                             Rollup  member
1945            + General & Admin                          Rollup  member
1939            + Selling Expenses                          Rollup  member
4009            ~ Operating Income                        Calculated member     [Account].&[4008]-[Account].&[4175]

In the above hierarchy Operating Income is calculated as Gross Profit - Operating Expense. My Account hierarchy is setup exactly as shown below including Rollup operators and formula. In MSAS 2000 the above formulas gives correct result.

But in MSAS 2005, it is not returning any result.

Does anyone know what is the issue?

I have created another rollup member called 'Other' at Level 1 and moved the Operating Income  member then same formula works fine.

Sign in to post a comment.
Posted by Microsoft on 8/16/2010 at 3:52 PM
Thanks for asking about this. We believe that this is the result of an intentional design change in AS 2005. A child cannot refer to its parent without invoking what would effectively be an infinite loop. While we understand that you used the ~ operator to try to break the loop, we're really not architected for that behavior.

Analysis Services Product Team
Posted by AshokDugaputi on 8/21/2009 at 11:55 PM

Thanks for reviewing this item and posting your comments.

After reading your reply, I could replicate the same issue in SSAS 2008 (in Adventure WorksDW 2008). Following are the steps.

Case #1 : •    Created a new record in [AdventureWorksDW2008].dbo.[DimAccount] table with name called “Check This”
                 •    Updated the Rollup Operator to ‘~’ (tilde) character
                 •    Updated the Custom Members field to refer to Notes payable member

                     Update DimAccount
                     Set CustomMembers='[Account].[Accounts].&[28]'
                     Where AccountKey='100'

                     Here Member formula '[Account].[Accounts].&[28]' refers to Notes Payable

                • Processed Account dimension (Full Process) & Adventure Works cube (Full Process)

Case #1 Result:

                •As expected it displayed the amount of Notes payable against my new member called ‘CheckThis’. This is correct result.

Case #2: Updated the Custom Members field to refer to its parent member called ‘Current Liabilities’
                Update DimAccount
                Set CustomMembers='[Account].[Accounts].&[27]'
                Where AccountKey='100'

Here Member formula '[Account].[Accounts].&[27]' refers to ‘Current Liabilities’

Case #2 Outcome: It returned Null value against my member ‘CheckThis’. I am expecting Amount    $3,588,650.00

So basically, whenever child member refers to parent it returns Null vallue in SSAS 2005 and SSAS 2008. But SSAS 2000 gives the correct result.

Could you please review the same and let us know your feedback on this functionality change?


Posted by Microsoft on 7/29/2009 at 1:03 PM
Thanks a lot Ashok for your feedback.
We ask you try and reproduce this problem with Analysis Services 2008 and if this is still not to your satisfaction - please open a case with Product Support.
The Analysis Services Team.