Setting a condition on an aggregate column

building semantic layer
1

I have an object in the Universe, Shipped Qty, that is defined as 'Sum(Sales_Order.Shipped_Qty). It also projects SUM when aggregated.

I’m attempting to write a report that counts by Item all of the records of the table where Shipped_Qty <> 0.

What I am getting as SQL is something like

select Item_Id, count(Sales Order Id)
from Sales Order
group by Item_Id
having sum(Shipped_Qty) <> 0

What I would rather have is

select Item_Id, count(Sales Order Id)
from Sales Order
group by Item_Id
where Shipped_Qty <> 0

In other reports, its important for me to have Shipped Qty defined as ‘Sum(Sales_Order.Shipped_Qty)’ (ie I’d rather have the database doing the aggregation than BusinessObjects). Does this mean that I need to have two objects in the Universe for Shipped Qty , one defined with SUM, one without ?

Thanks,
Matthew

Listserv Archives (BOB member since 2002-06-25)

2

Matthew Ferguson schrieb:

I have an object in the Universe, Shipped Qty, that is defined as 'Sum(Sales_Order.Shipped_Qty). It also projects SUM when aggregated.

…
In other reports, its important for me to have Shipped Qty defined as ‘Sum(Sales_Order.Shipped_Qty)’ (ie I’d rather have the database doing the aggregation than BusinessObjects). Does this mean that I need to have two objects in the Universe for Shipped Qty , one defined with SUM, one without ?

Yes.

–
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Listserv Archives (BOB member since 2002-06-25)