Creating and Using User-Defined Functions

Here's an example of a UDF. This function determines the commission for a sale based on a complex formula: if the sale amount is over $1,000, the commission is 12%; between $500 and $1,000, it's 10%; between $100 and $500, it's 8%; and there's no commission for sales less than $100.

function CalculateCommission(SaleAmount)
do case
  case SaleAmount >= 1000
    Commission = SaleAmount * 0.12
  case SaleAmount >= 500 and SaleAmount < 1000
    Commission = SaleAmount * 0.10
  case SaleAmount >= 100 and SaleAmount < 500
    Commission = SaleAmount * 0.08
  otherwise
    Commission = 0
endcase
return Commission

To use this UDF in a commission report, create a new report and add the desired fields, including the "Amount" field from the "Activity" table in the Accounts Receivable application. Then, turn on the Advanced layout option and click Edit to bring up the Advanced Report Designer. Create a new field and enter the following for the field's expression:

CalculateCommission(ARX_X__ACTIVITY.Amount)

(ARX_X__ACTIVITY.Amount is the name of the field in this example that contains the sale amount.)

Enter 999,999.99 for the Format and then click OK. To create a subtotal of commissions, copy the field and paste it, then move the copy to the group footer. Double-click the copy, select the Calculate page, and choose Sum from the Calculation type option. Save and run the report.