I just can’t seem to get my mind around this in a BAQ. Hoping for some help here.
We have multiple sales reps on an order with different splits. Those splits are used to determine how much of the sale go toward that rep groups quota. In a BAQ I can get the first rep and the split but how do i get all the other splits to sum them up for a particular rep?
Here is what the BAQ generates.
select (Sum(((OrderDtl.unitprice * OrderDtl.SellingQuantity) - OrderDtl.Discount) * (OrderHed.RepSplit1 / 100))) as [Calculated_OrderLineSales], (Convert(nvarchar, Month(OrderHed.OrderDate)) + '/' + LEFT(Convert(nvarchar, Year(OrderHed.OrderDate)),2)) as [Calculated_MonthYearOrderDate], (Year(OrderHed.OrderDate)) as [Calculated_OrderDateYear], [UDCodes].[CodeID] as [UDCodes_CodeID], [UDCodes].[CodeDesc] as [UDCodes_CodeDesc], (Month(OrderHed.OrderDate)) as [Calculated_MonthOrderDate] from Erp.OrderHed as OrderHed left outer join Erp.SalesRep as SalesRep on CASE WHEN OrderHed.SalesRepList LIKE '%~%' THEN [Ice].entry(1,OrderHed.SalesRepList ,'~' ) ELSE OrderHed.SalesRepList END = SalesRep.SalesRepCode left outer join Erp.Region as Region on SalesRep.ShortChar02 = Region.RegionCode left outer join Erp.SalesRep as Manager on Region.SalesManagerID = Manager.SalesRepCode left outer join Ice.UDCodes as UDCodes on SalesRep.ShortChar01 = UDCodes.CodeID and ( UDCodes.CodeTypeID = 'REPGRP' ) inner join Erp.Customer as Customer on OrderHed.Company = Customer.Company and OrderHed.CustNum = Customer.CustNum inner join Erp.OrderDtl as OrderDtl on OrderHed.Company = OrderDtl.Company and OrderHed.OrderNum = OrderDtl.OrderNum and ( OrderDtl.VoidLine = 0 ) where (OrderHed.VoidOrder = 0) and UDCodes.CodeID = @RepGroupParam and (Year(OrderHed.OrderDate)) = @OrderEntryYear group by (Convert(nvarchar, Month(OrderHed.OrderDate)) + '/' + LEFT(Convert(nvarchar, Year(OrderHed.OrderDate)),2)), (Year(OrderHed.OrderDate)), [UDCodes].[CodeID], [UDCodes].[CodeDesc], (Month(OrderHed.OrderDate)) order by MonthOrderDate
In native SQL I do a union all and change the entry and the RepSplit in the sum (ex:
OrderHed.RepSplit2), for each of the 5 rep entries.
THEN [Ice].entry(2,OrderHed.SalesRepList ,'~' )
I guess i am struggling how to do that same thing in a BAQ. Maybe there is a better way of doing it instead of doing the unions. ???