Conditional Calculated Measures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Conditional Calculated Measures

Conditional Calculated Measures

Rate Topic

Display Mode

Topic Options

Author

Message

michael.french 172

Posted Tuesday, October 30, 2012 1:37 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:12 AM
Points: 245, Visits: 564

I am having an issue trying to add some conditional logic to a calculated measure. What I am trying to do is to account for a change in view formula prior to a certain date. The below MDX query produces the desired results.

with member [Measures].[Monthly Adjusted Cost] as 
CASE 
    WHEN StrToVal([Calendar].[YearMonthLabel].CurrentMember.Name) < 201101 
    THEN [Measures].[Cost AMT]*0.95
    ELSE [Measures].[Cost AMT]
END

select non empty [Calendar].[YearMonthLabel].[YearMonthLabel].Members on rows

, {[Measures].[Cost AMT], [Measures].[Monthly Adjusted Cost]} on columns 

from [CostCube]

The question then becomes how to put the case statement into the actual Cube definition from the Calculations tab of the Cube Design for CostCube in Visual Studio. The forms that I have been trying end up with #value error saying I have an empty MDX Expression. The cube builds and does not produce an error.
Please, let me know if anyone knows how best to do this.
Thank you

Post #1378996

Mackers

Posted Wednesday, October 31, 2012 7:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:16 AM
Points: 117, Visits: 459

Try something like:

with member [Measures].[Monthly Adjusted Cost] as
IIF(Exists([Calendar].[YearMonthLabel].CurrentMember, {null:[Calendar].[YearMonthLabel].&[201101]}).COUNT > 0
, [Measures].[Cost AMT]*0.95
, [Measures].[Cost AMT])

select non empty [Calendar].[YearMonthLabel].[YearMonthLabel].Members on rows

, {[Measures].[Cost AMT], [Measures].[Monthly Adjusted Cost]} on columns

from [CostCube]

It may be simpler to add a scope statement in your cube rather than doing a calc

scope {null:[Calendar].[YearMonthLabel].&[201101]};

[Measures].[Cost AMT] = [Measures].[Cost AMT]*0.95;

end scope;

Post #1379314

« Prev Topic | Next Topic »

Permissions