Report Script: Simple Calculate Commission by Item UDF Rate

Introduction

This tutorial is to use a simple example to demonstrate how to apply report scripting to calculate commission that is based on sub total and item rate.

This example can also be done with Expression of Calculated Field without scripting.

Tasks in this tutorial:-

• Add User Defined Field (UDF)
• Add Calculate Field in Report
• Calculate commission of each item
• Report - Add Group Total
• Report - Add Report Total

Product

AutoCount Accounting 2.0
Applicable to AutoCount Accounting 1.8 / 1.9 (require to manual modify some coding)

Download Report Template (AutoCount Accounting 2.0) Simple Calculation.

Tutorial

Add UDF

Create below udf in the table

Category	Table	Name	Caption	Type	Size	Precision	Scale
Maintenance	Stock Item	COMMRATE	COMM RATE	Decimal		18	0

Open Report Designer and Load Report Template

1. On Main Menu, go to Sales > Invoice
2. Click [Print Listing] button
3. Select option Print Invoice Detail Listing

   Click [OK]

4. Select a range of Document Date: in Basic Filter
5. Change Group By: to "Agent" in Report Options.
6. Click [Inquiry]
7. On Main Menu of Print Invoice Detail Listing,

   go to Report > Design Detail Listing Report

8. Select Report Template of "Invoice Detail Listing"

Add Calculated Field

1. While the report design is at Designer mode
   • Right click 1 Main Data: Invoice Master in Field List
   • Click Add Calculated Field
2. Rename newly added Calculated Field, and set property
   • Select the newly added Calculated Field in Field List
   • In Property Grid, change the (Name) to "calcTotalComm"
   • In Property Grid, change the Field Type to Decimal

Add Script Event

1. While "calcTotalComm" is selected
   • Click [+] beside Scripts in Property Grid

     Under Scripts, find "Get a Value" event name

2. Click the empty box next to "Get a Value"
   • Click the down arrow button, and select "(New)"

     The window will now focus at Scripts editor

     An event of calcTotalComm_GetValue is created.

Insert script into the event of calcTotalComm_GetValue

for version 2.0

private void calcTotalComm_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
{
	//Get the subtotal of item that exclude Tax Amount
	decimal subTotal = AutoCount.Converter.ToDecimal(GetCurrentColumnValue("SubTotalExTax"));

	//Get the UDF_COMMRATE that is maintained in Item Maintenance
	decimal rate = AutoCount.Converter.ToDecimal(GetCurrentColumnValue("ItemUDF_COMMRATE"));

	//Calculate commission
	e.Value = subTotal * rate / 100;
}

for version 1.8 / 1.9

private void calcTotalComm_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
{
	//Get the subtotal of item that exclude Tax Amount
	decimal subTotal = BCE.Data.Convert.ToDecimal(GetCurrentColumnValue("SubTotalExTax"));

	//Get the UDF_COMMRATE that is maintained in Item Maintenance
	decimal rate = BCE.Data.Convert.ToDecimal(GetCurrentColumnValue("ItemUDF_COMMRATE"));

	//Calculate commission
	e.Value = subTotal * rate / 100;
}

DataBinding of calcTotalComm to a label

1. Rename label at header
   • Rename "Debtor" to "Comm"
   • Rename "Code" to "Rate"
   • Rename "Description" to "Commission"
2. Data Binding from Field List to label in DetailBand1
   • Drag "_UDF:COMMRATE (Item)" to label which original show "Debtor Account"
   • Drag "calcTotalComm" to label which original show "Debtor Company Name"

Add Group Total of calcTotalComm

1. Right click the label of calcTotalComm in DetailBand1 and Copy
2. Right click on "GroupFooter2" band and Paste
   • Align the position of label in "GroupFooter2",
     so that it is aligned to "calcTotalComm" in DetailBand1
3. While this new label in "GroupFooter2" is selected
4. In Property Grid, find Summary, and click [+] to expand
   • Change Running to "Group"
   • Update Function to "Sum"
   • Change Ignore Null Value to "Yes"

Add Grand Total of calcTotalComm

1. Right click the label of calcTotalComm group total in GroupFooter2 and Copy
2. Right click on "SummaryBand1" band and Paste
   • Align the position of label in "SummaryBand1",
     so that it is aligned to "calcTotalComm" in DetailBand1 and GroupFooter2
3. While this new label in "SummaryBand1" is selected
4. In Property Grid, find Summary, and click [+] to expand
   • Change Running to "Report"
   • Update Function to "Sum"
   • Update Ignore Null Value to "Yes"

• Click Preview

Go to menu

Go to top

Resources For AutoCount Software Developers