Search code examples
ssasmdx

Calculated member not showing totals

i have the following script in my cube:

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;   

CREATE MEMBER CURRENTCUBE.[Measures].[Scope TEST]
 AS 
STRTOVALUE(2), 
VISIBLE = 1;  

SCOPE([Locations].[LocationName].Members, [Measures].[Scope TEST]); 
this = SUM([Locations].CURRENTMEMBER, STRTOVALUE(1)); 
END SCOPE;

what i would like for the script to do, is the following;

i have 5 locations, so when i add the measure scope test it should display 1 after each row. which is fine.

it, however, does not display 5 in the grand total row.

this is a simplified version of a script im trying to use. in that script i have yet to use a scope, but with a scope statement or without it, it doesn't really matter since there are no differences.

if anyone could point me in the right direction, any help would be much appreciated.

Solution

  • Are you seeing 1 in the grand total? If so, it may be that SUM(ALL, 1) = 1, as the ALL member does in a way count as a single member.

    Try adding the All member's descendants into the count like this and let us know how it goes:

    SCOPE([Locations].[LocationName].Members, [Measures].[Scope TEST]); 
this = SUM(DESCENDANTS([Locations].CURRENTMEMBER, , AFTER), STRTOVALUE(1)); 
END SCOPE;

    The Descendants function will return the set of descendants, so the All member will have all it's children (The members) evaluated for the set. So you'll get five members, summing that at one each should give you the number 5.