Calculated Member using Member Properties

Question

Post reply

Calculated Member using Member Properties

emputecido

SSC Rookie

Points: 42
More actions
September 8, 2009 at 3:51 pm

#215014

Hi guys!
I have a SSAS Cube with a "Events" dimension, and I need to do some calculations in it. Actualy it looks like this:
Aggregate({[Event].[Events].[Category].[PayRoll].[Social Enc. PayRoll - Enterprise]}) / ([Event].[Events].[Category].[PayRoll].[Social Enc. PayRoll - Co-Workers])
The problem is:
The name of the member after the PayRoll category will change from times to times, like this:
[Social Enc. PayRoll - Enterprise] to [Social Enc. PayRoll - Enterprise - SP]
When it happens, the calculated field gives a error. Is there a way to tie the Member Properties (cvCodEve) to the calculation? So then I don´t have to worry 'bout it anymore.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jmessersmith-1145599 Say Hey Kid Points: 681 More actions · Answer 1

can you just make the SP column appear with a 0 when it doesnt have a value?

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 2

You can reference the member properties by doing a CurrentMember.Properties(" [Property Name] ") = " [Your Value] " reference, but why not simply reference the key values instead of the name values in your calculation. Hopefully you are reference a numeric key for the member and you can simply reference that so that a name change does not break the calculation.

So the reference would be something like [Event].[Events].[Category].[PayRoll].&[1] instead of [Event].[Events].[Category].[PayRoll].[Social Enc. PayRoll - Enterprise]

The key values should be much more reliable and should not change assuming that you have an actual key column reference instead of the same name column reference.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

emputecido SSC Rookie Points: 42 More actions · Answer 3

Thanks for the replies, and thanks Dan for the advices.

My problem persists, though.

I tried the following:

[Event].[Events].[Category].&[Payroll].&[95]

And

[Event].[Events].Properties( "Cv Cod Eve" ) = [859]

And it gives me #VALUE# on the field.

Hope someone helps.

Att.

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 4

Looks like you have an extra period (after Payroll) in the first item you listed and in regards to the other item you are going to need to evaluate your members that you are trying to filter.

[Event].[Events].[Category].&[Payroll]&[95]

And

[Event].[Events].CurrentMember.Properties( "Cv Cod Eve" ) = "859"

Maybe if you provided the entire MDX (any calculations and the SELECT statement) that you are trying to use that would help to determine how you should set this up exactly.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

emputecido SSC Rookie Points: 42 More actions · Answer 5

Dan, this is the query:

WITH MEMBER

[Event].[Events].[Category].&[PayRoll].[Test]

AS Aggregate({[Event].[Events].[Category].&[PayRoll].&[Social Enc. PayRoll - Enterprise]}) / ([Event].[Events].[Category].&[PayRoll].&[Social Enc. PayRoll - Co-Workers])

SELECT [Event].[Events].[Category].&[PayRoll].[Test] ON 0

FROM [DW Fator]

Thanks.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Hey Dan, he's using text strings for keys as well as descriptions 🙁

@emputecido - any chance you can use an internal identifier (like an integer) for the Key for this attribute and leave the name to be the current field value?

Steve.

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 7

Yeah, as I stated, if you are using text for the key column also then you are going to have issues referencing the key or name column for your calculation because the value will change. You will need to find a static reference that does not change and then this should solve your issue and you won't have to worry about applying an additional filter based on a member property either.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

emputecido SSC Rookie Points: 42 More actions · Answer 8

Thanks Steve and Dan!

I did what you guys told in the last two posts and it worked like a charm.

I owe the beer this time.

Thank you guys.