Crystal Reports and Sales Person Rate

[sbg2]

Using Crystal Reports - I am having trouble assigning the correct ARSAP.SALESRATE for any given shipment. For example I have a Sales Person setup as follows:

ARSAP.SALESRATE1 = 15%, from $0000 to ARSAP>SALESBASE1 = $0,999.99
ARSAP.SALESRATE2 = 10%, from $1000 to ARSAP.SALESBASE2 = $2,499.99
ARSAP.SALESRATE3 = 05%, over $2,499.99 (Accpac stores ARSAP.SALESBASE3 as 0)

Below is the formula I am trying to use for the above Sales Person. It will fail on a shipment where the total amount is greater than $2,499.99 (since $2,499.99 is not less than 0). I have been wracking my brain of how to create a simple formula to get at the correct ARSAP.SALESRATE but keep coming up blank. Any help appreciated.

IF {OESHIH.SHIITMTOT} <= {ARSAP.SALESBASE1} THEN {ARSAP.SALESRATE1} ELSE
IF {OESHIH.SHIITMTOT} <= {ARSAP.SALESBASE2} THEN {ARSAP.SALESRATE2} ELSE
IF {OESHIH.SHIITMTOT} <= {ARSAP.SALESBASE3} THEN {ARSAP.SALESRATE3} ELSE
IF {OESHIH.SHIITMTOT} <= {ARSAP.SALESBASE4} THEN {ARSAP.SALESRATE4} ELSE
IF {ARSAP.SALESBASE1} <= .01 THEN {ARSAP.SALESRATE1} ELSE
{ARSAP.SALESRATE5}

At this point I am putting blinders on to the fact that if an order is broken up into separate shipments we might be paying more commission than should be paid and to the fact that if a Salespersons rates change then historical data will be completely inacurrate. One Accpac problem at a time I guess.

 

[Jay Converse]

Why are you using a report? The system calculates it automatically.

 

[ettienne]

Unless they forgot to set it up

 

[sbg2]

We are using a report so we can send each broker a breakdown of their sales for a given period (month). Plus the owner of the comopany wants a breakdown by broker for each period.

Is there a module in Accpac that shows this information without the need for the report?

 

[ettienne]

There is an OE commission report, provided you setup the commission rates and set OE to track sales commissions.

 

[ettienne]

Look at OE Reports, Salesperson Commissions.

 

[Jay Converse]

O/E reports
Salesperson Commissions

But that assumes you went to O/E Setup/Options, and checked the Track Commissions box.

 

[sbg2]

The Track Commissions checkbox is checked under O/E Options and Commision By is set to Sales. However, the Salesperson Commissions report brings up a 0 value (Sales, Cost, Margin and Commission Earned) for every shipment.

So that report is not going to work. Any help with the original question?

 

[ettienne]

Go to IC Categories and see if Allow Commissions is checked.

 

[sbg2]

Allow Commissions is not checked under I/C Setup>Categories. The Accpac software was setup by a third party provider. I am going to have to go under the assumption that there is a good reason they setup the system this way.

All I really want to get at is how to format the formula.

 

[Jay Converse]

The didn't have a good reason, it was just a sloppy install.

How about:

IF {OESHIH.SHIITMTOT} <= {ARSAP.SALESBASE1} THEN {ARSAP.SALESRATE1} ELSE
IF ({OESHIH.SHIITMTOT} <= {ARSAP.SALESBASE2} or {ARSAP.SALESBASE2} = 0) THEN {ARSAP.SALESRATE2} ELSE
IF ({OESHIH.SHIITMTOT} <= {ARSAP.SALESBASE3} or {ARSAP.SALESBASE3} = 0) THEN {ARSAP.SALESRATE3} ELSE
IF ({OESHIH.SHIITMTOT} <= {ARSAP.SALESBASE4} or {ARSAP.SALESBASE4} = 0) THEN {ARSAP.SALESRATE4} ELSE
ELSE {ARSAP.SALESRATE5}

 

[sbg2]

Thanks, that looks like it works. I had to add the following to the start of the formula to account for Salspersons where 'Number of Commission Rates' = 1.

IF {ARSAP.SALESBASE1} = 0 THEN {ARSAP.SALESRATE1} ELSE

I had tried to use multi-criteria IF statements (and/or) but kept getting errors. Looking at your formula you provided I can't fathom how I went wrong. My lack of knowledge on the syntax they use is hindering my progress.