[Mondrian] FW: Mondrian issues with date and fact table columns
julianhyde at speakeasy.net
Tue Jul 10 12:28:47 EDT 2007
For the 1st issue, I have a more specific problem. An order has parts. The
fact table has a record for each part in an order with the part_qty and unit
I have a problem in creating a calculated mesaure for the sale amount i.e
part_qty * unit price. I want to get the sale amount for each order or for
each part in order.
The basic schema design is like this
Qty, unit price
nQty : sum of Qty
nUnitPrice : sum of Unit Price
Total Sale : Measures.nQty * Measures.nUnitPrice
Total Sale on Part : ([Part].CurrentMember , [Measures].[nQuantity])
* ([Part].Currentmember, [Measures].[nUnitPrice])
I created two calculated measures as above. None of them gives the correct
order sale amount. They add all qty of all parts in an order and add all
unit price for all parts in order and multiply the two which is incorrect.
But if I use part dimension on one axis, order dimesion on another axis and
calculated measure on slicer axis I get correct part sale amount but the
order amount is still as calculated above.
Order No. Part Num Qty Unit Price
1 905 1 75
1 50 1 150
This order sale amount should be 225 but the calculated measure gives 450.
1. Is it somehow possible to show that within each Order there are
parts in the Calculated measure definition so that group by is done on order
no, part num.
2. How is it possible to show that part is alevel within order where
order and part are two different dimensions?
Please clarify these issues and help to create a calculated measure for the
sale amount of an order.
You need to introduce a new SQL expression price = quantity * unit_price,
and create a measure on it, and make that measure additive.
You can do this using the MeasureExpression element or by creating a view.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Mondrian