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.

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?

Sorry for the misunderstanding, but I'm still trying to find a way to calculate the sum of these commissions, and it's the same problem as the other post ("Complicated Commissions Calculation"). I figured if there's a way to get the variable to assign a value only on the last record in the "Rep" group, then the "Sum" calculation in the Summary band would be correct. But since a variable is assigned a new value for every record, there are multiple records that match my conditional statement and the Report total variable is calculating all of the variable values rather than the last value for the variable for each rep (which is the correct value). See my other post for more information. It's making my head spin, but hopefully it will make sense. Each record in my database table is a sale, and this is the way it has to be for more of the fields in my report. If I only had one record for each Rep, it would be fine, but that's not the case. Thanks!

Hi All, Need to know if there's a way, in my variable expression, to check if the record being processed is the last in the group or not? I need to have a value for only the last record before the group is reset. This is for sum/total calculations. I need only one value for this variable per group, but since variables are processed on each record, I'm getting too high a number in my total. Thanks in advance, Anthony A.

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.

Is it possible to use a JOIN in your SQL query to join the current query and the names table on the ID field?

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