After a long break from Epicor I’m back lol…and I’m curious about how I can properly group the data the way I want. So, right now we take all orders out of Epicor and group them together in access using some queries/maketables/etc. We want to streamline that process, so I was wondering if there was a way I could get a BAQ that shows bookings for the 12 months of the current year.
So in table form…
Rep | Jan | Feb | etc…
I just want the totals, but I’m not sure how to group it column wise by months
You can do a pivot in the BAQ. It just has to be specific criteria, but since you are doing months, that would work. Pivots are kind of a pain to work with, but work fine once you get them. Check out the tools user guide and give it a shot, if you get stuck, let us know what issues you are having.
Thanks for your reply! I think it is getting me somewhere. Now, I’m trying to figure out how to handle the actual totals, and match them up to our Rep. We have the OrderHed table, which includes my salespersons for the order.
According to my table, each of those have their own field (ex SalesRepCode1, SalesRepCode2, etc.). When we come over to order totals, what we want is the ‘OrderHed.DocOrderAmt’ or ‘Order Total:’
This is simple for a single rep, but I’m not quite sure how to handle a split order. If it is an 80% 20% split for commission…we should calculate that against the order total, and give each rep that amount for the month of the order.
Then trying to match these salespersons up with a rep code. The Name of the salesperson in the order matches up to a Workforce entry name, and the workforce entry ID matches the Rep code of the territory, which is what we are after. So…I believe I should be good as long as I can match up the Salespersons on the order with their workforce ID.
Old school way of doing it if you didn’t want to mess with the pivot table. Create formula fields for each month:
JanSales: if month(orderhed.orderdate) = 1 then OrderHed.OrderAmt else 0
FebSales: if month(orderhed.orderdate) = 2 then OrderHed.OrderAmt else 0
@SimsTrak Thanks for your input! Overall I don’t think its a bad idea…but with commission splits between reps I think that can get very out of hand very fast. I’m hoping @Banderson can offer some insights about maybe some additional subqueries to calculate order totals with commission? Or even better if there is already a field I’m not aware about that stores these?
@Banderson And it’s sort of confusing too…because when I look in my order tracker…I see fields aligning with OrderHed.SalesRepCode1, but when I look in my BAQ I only see SalesRepList. However, if i put SalesRepList in, its not matching what shows in my list here.
For example,
That is in my order, and their workforce/rep ID is 720. But I look in my BAQ calling out SalesRepList and I see 370~320…so I’m not sure where I’m going wrong.
I don’t know what you’re looking at here, but the orders that you are looking at are not the same. If that record had 2 you should see two in the drop downs. Something is funky with what you are doing.
Remember, you have header, and line salesmen records
I’m going to look more into every field in the orderhed table, but Entry Person is our internal person that entered the order, not the rep…but apparently you were right anyways…somehow I was looking at the wrong order.
Ok so @dcamlin after I have them linked…it separates out to two different records, one for each rep. How do I take it a step further to know which rep split goes with which rep. Theoretically in the end I’d like one row that says “Total” and it doesn’t matter which RepSplit number it was…
For reference those last five columns are RepSplit1 thru RepSplit5
So could I do something like this for a case statement?
CASE
WHEN SalesRep.SalesRepCode = [Ice].entry(1, [OrderHed.SalesRepList], '~') THEN OrderHed.RepSplit1
WHEN SalesRep.SalesRepCode = [Ice].entry(2, [OrderHed.SalesRepList], '~') THEN OrderHed.RepSplit2
WHEN SalesRep.SalesRepCode = [Ice].entry(3, [OrderHed.SalesRepList], '~') THEN OrderHed.RepSplit3
WHEN SalesRep.SalesRepCode = [Ice].entry(4, [OrderHed.SalesRepList], '~') THEN OrderHed.RepSplit4
WHEN SalesRep.SalesRepCode = [Ice].entry(5, [OrderHed.SalesRepList], '~') THEN OrderHed.RepSplit5
ELSE 0
END
Yeah, I didn’t set mine up for that… but I was thinking along the same lines.
If you already created the SalesRep1 (2, 3, 4, 5) calculated fields, you should be able to use those in the expression… like below:
case
when SalesRep.SalesRepCode = SalesRep1 then OrderHed.RepSplit1
when SalesRep.SalesRepCode = SalesRep2 then OrderHed.RepSplit2
when SalesRep.SalesRepCode = SalesRep3 then OrderHed.RepSplit3
when SalesRep.SalesRepCode = SalesRep4 then OrderHed.RepSplit4
when SalesRep.SalesRepCode = SalesRep5 then OrderHed.RepSplit5
else 0
end
Okay… I just set up a test order with (5) different Reps splitting the order… each had a different Commission % setup on their Workforce records so I could verify they were coming through correctly… and I can calculate commissions in the BAQ.
Below are my Query Results (edited because I use peoples’ names as their RepIDs)
Like you mentioned the BAQ is going to create a row for each Rep.
My Calculated Fields:
SalesRep1 thru 5 (detailed in above posts)
CalcRepSplit (see previous post)
CommPerc (pretty much the same as CalcRepSplit, but this time I wanted to grab that reps assigned Commission Percentage from SalesRep table):
case
when SalesRep.SalesRepCode = SalesRep1 then SalesRep.CommissionPercent
when SalesRep.SalesRepCode = SalesRep2 then SalesRep.CommissionPercent
when SalesRep.SalesRepCode = SalesRep3 then SalesRep.CommissionPercent
when SalesRep.SalesRepCode = SalesRep4 then SalesRep.CommissionPercent
when SalesRep.SalesRepCode = SalesRep5 then SalesRep.CommissionPercent
else 0
end
@dcamlin Thanks for your continued help with this…I’m super close to what I need now. We don’t care about the actually commission. We just want to know based off the split how much of the order total should be added to their booking totals for the month.
OrderHed.DocOrderAmt * (RepCommSplit / 100)
I have this formula now in a calculated field, which is supposed to give me my final order total…and it works for 100%, but it is showing 0 when there is a split… Kinda confused. Does it have something to do with my formats?
