Hi

If I have a MDX query like

select {[Measures].[Amount] } on columns,
{ [Dim location].[name].members } on rows
from myCube

and each Measure Amount can be postive, negative or 0 the above returns
the net amount. How do I return the

net, positive (only the sum of postive measure values), negative (only
the sum of negative measure values) and absolute (sum of absolute
values)?

Thank you

Adrian

Hi Adrian,

If you need to roll up the positive, negative and absolute amounts from
the fact level up, you might want to add named calculations (AS 2005) to
the fact table, like:

Pos Amt: case when Amount > 0 then Amount else 0 end

Neg Amt: case when Amount < 0 then Amount else 0 end

Abs Amt: abs(Amount)

Then define "sum" measures on these fields.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***