Different Aggregation Function for Single Measure

BOL alludes to being able to set a different aggregation function for a measure for different dimensions/hierarchies. In the June CTP, the link in BOL is as follows:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/c359b4c1-9c3f-41bc-a585-de7c934e2c11.htm

It states that an aggregation function can be set on the measure (as the default, using the AggregateFunction property) in the Properties pane of the Cube Designer. Which is fine.

But, it also states that an aggregation function can be specified for a particular measure when aggregated along a specific hierarchy. The problem is, it doesn't state where this might be done in the Cube Designer and I can't seem to find any property setting or other setting that might lend itself to doing this.

Being able to specify a different aggregation function for a measure based on the hierarchy involved would be very useful. For example, a dimension with multiple date dimensions or hierarchies using different aggregation functions to apply slightly different additive or semiadditive aggregations.

Anyone know how to do this?

Thanks...

Dave Fackler

A way to do this is in the calculations for the cube. Set a scope (for your measure), set a scope for your dimension, the change the value of the calculation. For example:

where the aggregation method for [myMeasure] is SUM:

SCOPE [Measures].[myMeasure];
SCOPE leaves([Region]);
this = [Measures].[myMeasure] * 2;
END SCOPE;
END SCOPE;

Note: the specifics depend strongly on the aggregation effect you're trying to achieve. I've often found that I needed to approach the problem in reverse, to get the results I wanted.

Good luck.