CODEWITHSUNDEEP
sql-server-2008-r2ssasmdxssas-2008
GordyII
GordyII

Reputation: 7257

SSAS MDX - How to change the way summing up works in a cube?

I want to change the way summing up works the cube to avoid rounding errors.

My calculation is simply Column A / Column B

At each level in the hierarchy, I want to have SUM(Column A) / Sum(Column B) instead of SUM(All the individual Column A / Column B)

How can I do this either in my calculation or changing the way the summing up works?

Upvotes: 0

Views: 1041

Answers (3)

ic3
ic3

Reputation: 7680

You can add a calculated member that is processed at the very end of the aggregation. Let's assume you defined A and B as measures :

WITH
  MEMBER [Measures].[A/B] AS  ([Measures].[A]) / ([Measures].[B])
SELECT
 ...

You can add the calculated member in the cube definition so it will be available in all mdx statements. To be sure, note Sum(A) / Sum(B) != Sum (A/B), you can add check for 0/null if needed.

Upvotes: 1

dani herrera
dani herrera

Reputation: 51655

That you want is SUM(Column A) / Sum(Column B) instead of SUM(All the individual Column A / Column B). Then I asume that you have both measures: sum_column_a and sum_column_b.

To create new measure sum_column_a_b you should create a calculated member:

  • On Visual Studio open your cube
  • Click on calculations tab
  • Click script view and paste this code:

Code to be pasted:

CREATE MEMBER CURRENTCUBE.[Measures].[sum_column_a_b]
 AS iif(  [Measures].[sum_column_b] > 0,
        [Measures].[sum_column_a] / [Measures].[sum_column_b], 
        0 ), 
FORMAT_STRING = "Percent", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'yourDesiredGroup';

When you process cube this new measure will be available.

Upvotes: 1

Max
Max

Reputation: 804

As I understood, you have a calculated field (Column A / Column B) in sql db or DSV (data source view). So, you can just create two SUM measures for each column (one sum measure for column A and second - for column B) and use them in the new calculated measure

iif([Measure].[ColumnB]=0 OR [Measure].[ColumnB] is NULL, 0, [Measure].[ColumnA]/[Measure].[ColumnB]).

Upvotes: 0

Related Questions