I am new to B1 Query and I am trying to create a report to show a summary of order discounts by customer for a date range. There are discounts at the line level and at the order level. When I run the following query, it adds the order level discount and order level total to the summary for each order line. I can't figure out how to summarize the info from INV1 and pull in the order total info just once per order. Any ideas would be appreciated. Here is my code:
SELECT T1.CardName, sum(T0.QuantityT0.PriceBefDi)[Total Before Discounts], sum((T0.QuantityT0.PriceBefDi)-T0.LineTotal)[Line Discount], Sum(T1.DiscSum)[Order Discount],
Sum(((T0.Quantity*T0.PriceBefDi)-T0.LineTotal)+T1.DiscSum)[Total Discounts],
Sum(T1.TotalExpns)[Freight],Sum(T1.DocTotal)[Invoice Total]
FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.DocDate between [%1] and [%2]
Group by T1.CardName
Order By T1.CardName