Calculations Question

Hello,

Is there a way to make a Calculated Member aggregatable? I want to create a Calculated Member that performs a certain formula at the leaf level only. On the upper levels of the hierarchy I want the value aggregated, not the formula re-applied. Is there a way to achieve this?

Another question, is there a way to apply a special formula for a measure? and what is the "MeasureExpression" Property of a measure to be used for?

Thanks

For the first part:

You could create real measure that has all values as NULL. This will not take any space in database. Then you assign calculations in your calculated area and this measure will behave like real measure. This is based on Mosha Pasumansky suggestion here:

http://sqljunkies.com/WebLog/mosha/archive/2006/11/16/sqlpass_udm_presentation.aspx

Quote:

6. It isn't a problem that calculated measures don't aggregate up. If the designer need to add calculated measure which has aggregation semantics of a real measure, than there is very simple way to do it. Add a real measure to the measure group, and specify NULL as its column binding. It will cost nothing during processing, the partition size is going to be exactly the same, because AS will easily detect that measure value is constant and will compress it to 0 bits. But then in MDX Script, when expressions are assigned to that measure - they will aggregate up using measure's aggregation function - and it should work very efficiently too.

Some info about "MeasureExpression" property can be found in this thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=768757&SiteID=17

Vidas Matelis