How to make a monthly booking by rep BAQ

Anthony_Mattice · August 21, 2023, 4:07pm

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

Banderson · August 21, 2023, 4:47pm

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.

Anthony_Mattice · August 21, 2023, 6:13pm

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.

SimsTrak · August 21, 2023, 7:23pm

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

this is not baq syntax, but you get the idea

Anthony_Mattice · August 21, 2023, 8:27pm

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

Anthony_Mattice · August 22, 2023, 2:04pm

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

dcamlin · August 22, 2023, 2:30pm

My approach (may be other solutions)… break the SalesRepList into individual calculated fields… below is an example:

CalcField: SalesRep1 (nvarchar)
CalcField Expression: [Ice].entry(1, OrderHed.SalesRepList ,‘~’ )

CalcField: SalesRep2 (nvarchar)
CalcField Expression: [Ice].entry(2, OrderHed.SalesRepList ,‘~’ )

So this is splitting your OrderHed.SalesRepList… anywhere it sees a tilde (~). Entry 1 is your SalesRep1, Entry 2 is your SalesRep2, etc.

Then you can do other calculations based on your commission structure. Below is an example, but tweak as required:

Calc Field: Rep1Comm (Int)
Expression:
(case when SalesRep1 = SalesRep.SalesRepCode then OrderHed.DocOrderAmt / OrderHed.RepSplit1 * SalesRep.CommissionPercent else 0 end)

Calc Field: Rep2Comm (Int)
Expression:
(case when SalesRep2 = SalesRep.SalesRepCode then OrderHed.DocOrderAmt / OrderHed.RepSplit2 * SalesRep.CommissionPercent else 0 end)

Below is an example result (in this case, both sales reps had the same commission %, and split the commission 50/50)

Anthony_Mattice · August 22, 2023, 3:22pm

I’m a little confused about how to get it working. I don’t really see any connection between OrderHed and SalesRep that I can use in a BAQ.

Banderson · August 22, 2023, 3:29pm

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

Anthony_Mattice · August 22, 2023, 3:34pm

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.

dcamlin · August 22, 2023, 3:55pm

Oops… forgot that piece. You can join SalesRep table to OrderHed with the below Relationship:

So my query join looks like this:

Anthony_Mattice · August 22, 2023, 4:04pm

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

I’m assuming I can do this with a case statement

Anthony_Mattice · August 22, 2023, 4:29pm

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

dcamlin · August 22, 2023, 4:38pm

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

dcamlin · August 22, 2023, 5:24pm

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

Commission:
(InvcDtl.ExtPrice * (CalcRepSplit) * (CommPerc / 100))/100

We pay commissions based on Invoiced amounts, so that’s why I used InvcDtl.ExtPrice

For some reason… I originally had this as
(InvcDtl.ExtPrice * (CalcRepSplit / 100) * (CommPerc / 100))

… but this resulted in all zeros. If I moved the center " /100 " to the end, it worked. Not sure why.

But I ran the numbers through Excel to verify I was getting the correct results, and it seemed to line up.

Anthony_Mattice · August 22, 2023, 5:39pm

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

dcamlin · August 22, 2023, 5:43pm

Try:

(OrderHed.DocOrderAmt * RepCommSplit) / 100

Like I said in my previous post, I tried my “(CalcRepSplit / 100)” and got all zeros, too.

Try moving the " / 100" outside the parenthesis. Can’t explain why, but maybe it’ll get you the correct result.

Banderson · August 22, 2023, 5:46pm

You might have to convert integers to decimals. Sometimes doing math with integers causes problems.

convert(decimal, yourField)

dcamlin · August 22, 2023, 5:48pm

Bingo… that worked for me:

(InvcDtl.ExtPrice * convert(decimal, CalcRepSplit)/100 * (CommPerc / 100))

dcamlin · August 22, 2023, 5:49pm

Odd though because all my calculated fields are decimal type… didn’t think I’d still have to convert to decimal if it started out as a decimal.