Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conditional Calculated Measures Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 1:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:08 PM
Points: 246, Visits: 568
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
Posted Wednesday, October 31, 2012 7:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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 »

Add to briefcase

Permissions Expand / Collapse