[Mondrian] RE: Filtering measure values

[Julian Hyde]

Obviously you need to filter out the rows before you sum them, not after.
(After you have summed, say, all of the sales in a particular year and
region, the total will obviously be large, even though many of the
contributing cells might be <4.)
 
In cases such as this, you need to do the filtering in SQL, viz:
 
<Measure name="Quantity" datatype="Integer" aggregator="sum" visible="true">
  <MeasureExpression>
    <SQL dialect="generic">
     case when quantity > 4 then quantity else null end
    </SQL>
  </MeasureExpression>
</Measure>
 
Julian
 
PS Please send future emails to mondrian at pentaho.org. The
mondrian-devel at lists.sourceforge.net list is obsolete.


  _____  

From: Anderson C. Carniel [mailto:anderson_lp789 at hotmail.com] 
Sent: Wednesday, March 02, 2011 5:24 AM
To: mondrian-devel-owner at lists.sourceforge.net
Subject: Filtering measure values


Hi!

 
I would like to aggregate the sum of revenue and filter the measure
value, e.g., where quantity>4, following this description:
 
 <Measure name="Quantity" column="quantity" datatype="Integer"
aggregator="sum" visible="true">
</Measure>
<Measure name="Revenue" column="revenue" datatype="Numeric"
aggregator="sum" visible="true">
</Measure>
 
Using SQL, I would simply issue: select sum(revenue) from table where
quantity > 4;
 
However, how could I perform such filter using MDX?
 
Regards,
 
Anderson

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20110302/ff614f9b/attachment.html