The report I am trying to create is a salesperson commission report. A salesman is paid a different commission on each category of item he sells as well as the profit percentage he sells it at. Our store has 20 different categories and within each category there is 6 different commission percentages. So I need to build a formula that can accommodate these different possibilities.

I have tried nesting if-then-else statements but ran into a problem. If I try to do each category seperate I run into the "Maximum size of report" error message rather quickly. If I try combining multiple categories into the same if-then-else statement.

Example:

If category code = "ccd" or category code = "cam" or category code = "ctp" and points in 45.00 to 55.00 then gross profit * .20 else..

If category code = "ccd" or category code = "cam" or category code = "ctp" and points in 35.00 to 44.99 then gross profit * .15 else..

The formula only calculates the correct commission for the last category code "ctp".

Do you have any suggestions on how I can fix this?

Is there any way to get around the error message "maximum size of report"?

Thank you
Scott

Hi Scott,

The error message that you are getting, ?Maximum size of report?, means that you need to reduce the size of your report. You may do this by writing your formulas more efficiently, for example:

If Category code in [?ccd?, ?cam?, ?ctp?] and points in 45.00 to 55.00 then gross profit * .20 else?

Your original formula did not work because you did not have parentheses around the ?or? statements. This means that only the last category ?ctp? was tested for the range of points. If you are using ?or? statements, you want to group them with parentheses. For example:

If (category code = "ccd" or category code = "cam" or category code = "ctp") and points in 45.00 to 55.00 then gross profit * .20 else..