CODEWITHSUNDEEP
ssasmdxcube
haggz
haggz

Reputation: 92

no total value using calculation in ssas

im using a calculated member to subtract special values from measures of special attributes.

In pseudo code it shoud work like this:

CASE Attribute  
WHEN (Attribute = A) THEN Measure_of_A - 14
WHEN (Attribute = B) THEN Measure_of_B - 2
ELSE 5

I tried this in MDX within a calculated Member

CASE [D Software].[Dim Software].CURRENTMEMBER

 WHEN [D Software].[Dim Software].[Feature Desc].&[A]&[AAA]
  THEN ([D Software].[Dim Software].[Feature Desc].&[A]&[AAA],[Measures].[Status6]) - 14

 WHEN [D Software].[Dim Software].[Feature Desc].&[B]&[BBB] 
  THEN ( [D Software].[Dim Software].[Feature Desc].&[BBB]&[B],[Measures].[Status6]) - 2   

ELSE '5'
END

IT works fine, however the aggregated value of total is always wrong. There I get a result looking like this:

Attribute Value

AA -14

Total_AA 5

BB -2

Total_BB 5

Grand_Total 5

Has someone an advice for me ? Where is my failure ? Why is there no correct aggregation of the values ?

Upvotes: 1

Views: 481

Answers (1)

nhrobin
nhrobin

Reputation: 903

Calculation of calculated member is executed after cube do all the aggregation. You need to define aggregation explicitly in the calculation for total e.g.

CASE [D Software].[Dim Software].CURRENTMEMBER

 WHEN [D Software].[Dim Software].[Feature Desc].&[A]&[AAA]
  THEN ([D Software].[Dim Software].[Feature Desc].&[A]&[AAA],[Measures].[Status6]) - 14

 WHEN [D Software].[Dim Software].[Feature Desc].&[B]&[BBB] 
  THEN ( [D Software].[Dim Software].[Feature Desc].&[BBB]&[B],[Measures].[Status6]) - 2   

  WHEN [D Software].[Dim Software].[Feature Desc].&[A] 
  THEN sum( [D Software].[Dim Software].[Feature Desc].&[A].children,[Measures].[Status6])

  WHEN [D Software].[Dim Software].[Feature Desc].&[B] 
  THEN sum( [D Software].[Dim Software].[Feature Desc].&[B].children,[Measures].[Status6])

  WHEN [D Software].[Dim Software].[Feature Desc].[All] 
  THEN sum( [D Software].[Dim Software].[Feature Desc].[All].children,[Measures].[Status6])

ELSE '5'

END

Writing aggregation logic in MDX calculated member this way is not maintable. You should use scope to handle this kind of logic.

Upvotes: 1

Related Questions