Complicated Commissions Calculation

[lumination]

 Hey all,

   I have an "Employee Compensation" report that calculates commissions for employees for a week, and the English version of the formula goes something like this:

There is a number-of-sales quota that changes from week to week, and there is an average-sale quota that changes from week to week. If an employee does not meet the sales quota, (s)he gets absolutely nothing. If they meet the number-of-sales quota AND meet the average-sale quota, they get the full amount of all sales as their commission (this motivates sales; don't ask! :). If they meet the number-of-sales quota but not the average-sale quota, they get $10 per sale.
 

Here's the issue:

I have the following variables and parameters working:

• $P{sales_quota}
• $P{average_sale_quota}
• $V{rep_sale_COUNT}
• $V{rep_sale_amount_AVERAGE}
• $V{rep_sale_amount_SUM}

In the "Rep" group footer, I have a text field calculating commissions for each rep, the expression of which you can see in the code portion of this post.

I need to get a grand total of commissions paid for all reps across the board in the Summary band. How is this possible? Is there another way I should be going about this stuff?

Thanks!

Anthony A.

Code:

($V{rep_sale_COUNT} >= $P{sales_quota})?  ($V{rep_sale_amount_AVERAGE} >= $P{average_sale_quota})    ? $V{rep_sale_amount_SUM}    : 10 * ($V{rep_sale_COUNT}): 0

[cbarlow3]

Off the top of my head, here's what I would recommend:

1. Instead of a text field that has the calculation in it, I recommend making another variable called IndividualCommission, set the Calculation to "Nothing", the Reset type to "Group", the Rest group to whatever your group is (salesperson, or some such thing), and the Variable Expression to exactly the same expression you currently have in your text field.  Now drag a copy of that variable from the report inspector tree into your group footer and format it correctly.  Leave the text field for now just so you can compare and see if the variable is working appropriately.

2. If that works, copy that variable in the Report Inspector tree and do a Paste on "Variables".  It will automaticallyi create another variable that is identical to the first one, and it will give it an automatically generated name.  Go into Properties for that new variable, change the name to "TotalCommission", and change the reset type from Group to Report.  Drag a copy of this new variable into the Summary band and format it appropriately.

Hope that works!

Carl

[lumination]

 Carl,

Thanks very much for your quick response. I appreciate it very much.

The field for the rep footer now uses a variable that calculates the commission, and that one works great. I suppose that is because the text field contains the last value of the variable since it's in the footer.

The total field, however, is inaccurate and I'm not sure how to fix it. Instead of giving me the total of all of the rep commission text fields, it's giving me the total of all of the records after the quota. I have the same conditional logic inside the Report variable as I do inside the Rep Group variable, except with a "Sum" calculation. Let me give you an example:

For each record, the sale amount average is recalculated and the Rep Group's variable recalculates the commission. This is fine for the Rep Group text field because it always uses the last value (which is the correct one)... Here's a breakdown of an example where a sales-number quota is "5" and a sales-amount quota is "$50". Assume that each of these lines is a record belonging to a single Rep:

 

Sale 1: $55 (Average Sale Amount: 55; Commission: 0)

Sale 2: $55 (Average Sale Amount: 55; Commission: 0)

Sale 3: $55 (Average Sale Amount: 55; Commission: 0)

Sale 4: $55 (Average Sale Amount: 55; Commission: 0)

Sale 5: $55 (Average Sale Amount: 55; Commission: 275)

Sale 6: $20 (Average Sale Amount: 49; Commission: 60)

 

What my company group variable is doing is adding the $275 and the $60, instead of using just the $60 as is needed and as would show up on the Rep footer's text field for commission. This is blowing out the commission total, and I can't use "Highest" on the Rep Group's variable calculation type because highest isn't what they should be making.

Not sure exactly how to proceed from here. Any ideas?

Thanks,

Anthony A.

[lumination]

 Or, even better, can I set a variable FROM a text field? Something like "$V{mycount} = $V{mycount} + $V{rep_commission}" since the text field in the footer always displays the correct value?

[lumination]

 Thank you for your help, but I got it solved:

For the commission total variable, I had to set the Increment Type to "Group" and then set the group to "Rep" and now it only evaluates the Sum only at the end of each group.