Create a calculated member on one measure.

I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.

'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.

Can anyone help me with that?

First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.

I am not sure why is it "obviously" not working well, the following should work well for you:

CREATE Version.Variance = Version.Actual - Version.Budget;

HTH,

Mosha (http://www.mosha.com/msolap)

|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||

I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:

CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];

MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.

Any ideas?

Thanks!

|||

OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

Thanks!

|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||

First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.

Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?

|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||

At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:

Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0

If I have AttributeHierarchyEnabled=False, I get two new errors:

Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0

Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'

Thanks

|||

I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:

A set has been encountered that cannot contain calculated members.

The other two members of Version work fine.

Here is the setup:

Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True

VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription

Here is how the member is created:

CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]

AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Net Amount] },

VISIBLE = 1 ;

The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.

Thanks

|||

Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.

Thanks for your help in walking me through it.

|||

Mike,

I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.

Thanks,
-Nimitt