|
Home > Archive > MS SQL Server OLAP > December 2005 > How to create a non-additive measure
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to create a non-additive measure
|
|
| KarenM 2005-12-25, 8:23 pm |
| I have a measure called OrderQty for a master detail reporting
scenario.
The Purchase Order as a OrderQty of say 90 units and it could be
delivered in 5 lots I have a view with joins the purchase Order header
and the line items like the following:
POOrder# OrderQty DelvQty Date
====================
===========
10 90 10 2-Dec-05
10 90 50 5-Dec-05
10 90 30 10-Dec-05
====================
===========
So in the above scenario even though there are 3 deliveries the order
qty for the PO is still 90.
I want to set the OrderQty to be non-additive when I load the above
into the cube otherwise
when I report by PO Order the OrderQty would be 270 and the delvqty
would be 90 which would not be correct.
I am not able to figure out how to set the OrderQty to be non-additive.
Thanks
Karen
| |
|
| you can create a cube based on the orderqty only and a second based on the
delvqty.
but you have to use another date column for the orderqty cube, like date of
order instead-of data of delivery.
this order cube will have 1 row by order, while the delivery cube have 1 row
by delivery date.
a virtual cube in front of these 2 cubes allow you to cross analyse the
values and provide some ratios.
"KarenM" <karenmiddleol@yahoo.com> wrote in message
news:1135557176.688796.171660@g47g2000cwa.googlegroups.com...
>I have a measure called OrderQty for a master detail reporting
> scenario.
>
> The Purchase Order as a OrderQty of say 90 units and it could be
> delivered in 5 lots I have a view with joins the purchase Order header
> and the line items like the following:
>
>
>
> POOrder# OrderQty DelvQty Date
> ====================
===========
> 10 90 10 2-Dec-05
> 10 90 50 5-Dec-05
> 10 90 30 10-Dec-05
> ====================
===========
>
>
> So in the above scenario even though there are 3 deliveries the order
> qty for the PO is still 90.
>
> I want to set the OrderQty to be non-additive when I load the above
> into the cube otherwise
> when I report by PO Order the OrderQty would be 270 and the delvqty
> would be 90 which would not be correct.
>
> I am not able to figure out how to set the OrderQty to be non-additive.
>
> Thanks
> Karen
>
|
|
|
|
|