Sum parent's value (distinct)

[netangel]

I'm developing a cube for analysing the orders of a company.
My database (for orders) is organized in the tables 'ORDER' and 'ORDER LINE'. I must create a cube that sums the number of products by client, and some other totals like this that join the 'ORDER' and 'ORDER LINE' tables.
The problem is that one of the totals I must calculate is a sort os distinct sum: I must sum a field from the 'ORDER' table, and show it only once for each order.
Meaning that if a order has 2 lines, the value of the field should appear once, not 2*value.

Is this possible?

NetAngel

 

[issahar]

May be you can think about next solution(not very nice but may be..):
(i don't know the names of your fields so I supposed their names to be
orderID and numProducts.

create 2 views:

first view 'view1':
Select orderId, numProducts
from ORDER
UNION
Select orderId, numProducts
from ORDER LINE


create view 'view2'
SELECT [orderId], SUM([numProducts]) / COUNT([numProducts]) AS numProducts
FROM VIEW1
GROUP BY [Time]

Base your cube one view2


Please, notify me if it helped you.
Good luck. Issahar
senior software engineer

http://www.softwatch.com