September 8, 2009 at 3:51 pm
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.
September 9, 2009 at 1:09 pm
can you just make the SP column appear with a 0 when it doesnt have a value?
September 10, 2009 at 11:32 am
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
September 14, 2009 at 10:36 am
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.
September 14, 2009 at 10:45 am
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
September 14, 2009 at 12:07 pm
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.
September 14, 2009 at 1:38 pm
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.
September 15, 2009 at 4:50 am
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
September 15, 2009 at 10:51 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply