Firstly, thanks for replying me. My case is really complicated, not know if I can state it clearly to you.
OK, let me try.
Sales in Sales in Commision Commision
last month this month in last month in this month
10000 0 (0) <------ I hided it, no need to show in the report.
0 4500 (900) <------ I hided it, no need to show in the report.
10000 10000 (2900) <------ I hided it, no need to show in the report.
------------------------------------------------------------------------------------
20000 14500 4000 2900 <------ Total of a month, Group by month

-------------------------------------------------------------------------------------
Total of year: 14500 3800 <------ Total of a year, should be 2900 in this example.

In the above example, all my formula in the report gave the answer I want except the "Total of a year of Commision in this month", the 3800!!!
The formula is :
if the total sales < 50000, the commision rate is 0.2
if the total sales > 50000 and < 100000, the first portion of commision is 50000 x 0.2 = 10000, the second portion of commision will be x 0.25.
but in our example, we just have the first portion as the sales is below 50000.
So, how to calculate the 2900 is, (20000 + 14500) x 0.2 = 6900 but 4000 has been tooken in last month, so 6900 - 4000 = 2900.
(the 4000 in last month was given by 20000 x 0.2 = 4000) The expression using in the 2900 field is:
IIF(TotalCom < 50000, TotalCom*0.2, 0) + IIF(TotalCom > 50000 and TotalCom < 100000, 10000 + (TotalCom - 50000)*0.25, 0) - LastCom
where the LastCom is produced by similar expression but in this case, it is 4000.
And I used "Sum" in the Calculations and reset at "End of Report" in the property of the 2900 expression.
I want to have the sum of each total of each month's commission but in fact it added up all the individual value in the "detail" row in the report I marked with ( ) above. That became 3800 which is wrong.
Understand what I said? I hope it will not be too complicated to understand. In fact, I already simplified as much as I can in the expression since there are in fact not only 2 range for the commision rate but I have 5.
Hope you can understand and give me advice how to correct the 3800 to 2900.


>>I grouped my data by month and what I want to do is that I want to set the data to 0 if it is not the last record in the group like shown below, what can I do? Or can report do that?
>>
>>Jan
>>Mr. A $10000 <--- want to become 0
>>Mr. B $20000
>>Sum1= 30000
>>Feb
>>Mr. A $2000 <--- want to become 0
>>Mr. B $3000 <--- want to become 0
>>Mr. C $4000
>>Sum2= 9000
>>----------------------------
>
>
>I don't understand why you would want to do this. What are you trying to accomplish and why? Maybe there is a better way than what you are thinking.
>
>Cathy