BAQ / SQL Question

Epicor ERP 10
,
1

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. ???

2
1 Like
4

I wish I would’ve thought of this. I still am not great with CTE expressions, but this is nice.

@jdewitt6029 there are a few reports I can think of where this would come in handy and could replace the unions.

5

The other way of doing it is to create calculated fields as there is a limit to the number of Sales Reps that can be assigned to a Quote or an Order using the following expressions:

([Ice].entry(1,OrderHed.SalesRepList ,’~’ )) as [Calculated_SalesRep1],
([Ice].entry(2,OrderHed.SalesRepList ,’~’ )) as [Calculated_SalesRep2],
([Ice].entry(3,OrderHed.SalesRepList ,’~’ )) as [Calculated_SalesRep3],
([Ice].entry(4,OrderHed.SalesRepList ,’~’ )) as [Calculated_SalesRep4],
([Ice].entry(5,OrderHed.SalesRepList ,’~’ )) as [Calculated_SalesRep5],

1 Like