Aggregation type - NONE; but setting aggregation through MDX - how to?

[eo]

Hi,

I have a measure I want to shown ONLY at a certain scope. But cant seem to get this to work. The measure aggregation type is set to None, as to show nulls when analysed normally.
But in MDX the measure is set to aggregation using a scope (which specifies the ONLY intersection where the user shoudl see something.

1) Aggregation for the measure [ed02_d_mh_flag_lhntarget] set to None

2) A new calculated measure created

CREATE MEMBER CURRENTCUBE.[Measures].[ed02_d_mh_flag_target]
AS 
NULL,
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Targets' ,  ASSOCIATED_MEASURE_GROUP = 'FactCombined';

3) The new calculated measure defined at the relevant scope with an aggregation function

SCOPE ([Gen_Hospital].[Hierarchy],[Gen_Date].[Hierarchy]); 
[Measures].[ed02_d_mh_flag_target]
  = sum({[Gen_Hospital].[Hierarchy].members,[Gen_Date].[Hierarchy].members},[Measures].[ed02_d_mh_flag_lhntarget]); 
END SCOPE;

But I get NULLS even when intersecting the new calculated measure with the hierarchies defined in the scope.

EO
Hertfordshire, England

 

[Shab]

Hi eo,

I have a couple of suggestions. If your base physical measure is not visible to end users, then you don't have to set its aggregation to NONE. Set it to SUM and let the engine do its work. Then you only use its value when you want for your calculated measure. Since this is all users see, you don't have to worry about the rest. I am guessing that you have more than the two dimensions you have used in the SCOPE statement. If that is the case, you are still referreing to an intersection of the cube that is not at the grain of the fact table. Since you have set the aggreagtion to NONE, there are no values there since you are not at the bottom, hence sum of nothing returns noting.
Try the above approach and let me know if it works:

1- Change the aggregation of the base physical measure to SUM.
2- Change the scope statement to this:


SCOPE ([Gen_Hospital].[Hierarchy],[Gen_Date].[Hierarchy]); [Measures].[ed02_d_mh_flag_target] = [Measures].[ed02_d_mh_flag_lhntarget]); END SCOPE;

Hope this helps.

S.Watson