Report Script: Simple Calculate Commission by Item UDF Rate
Introduction
This tutorial uses 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 in Calculated Field without scripting. |
Tasks in this tutorial
- Add User Defined Field (UDF)
- Add Calculate Field in Report
- Calculate commission of each item
- Report Design - Add Group Total
- Report Design - Add Report Total
Product
AutoCount Accounting 2.0
Applicable to AutoCount Accounting 1.8 / 1.9 (require to manual modify some code)
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
- On Main Menu, go to Sales > Invoice
- Click [Print Listing] Link Button
- Select option Print Invoice Detail Listing
- Click [OK]
- Select a range of Document Date: in Basic Filter
- Change Group By: to "Agent" in Report Options.
- Click [Inquiry]
- On Main Menu of Print Invoice Detail Listing,
- go to Report > Design Detail Listing Report
- Select Report Template of "Invoice Detail Listing"
Add Calculated Field
- While the report design is at Designer layout form
- Right click 1 Main Data: Invoice Master in Field List
- Click Add Calculated Field
- 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
- While "calcTotalComm" is selected
- Click [+] beside Scripts in Property Grid
- Under Scripts, find "Get a Value" event name
- Click [+] beside Scripts in Property Grid
- 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.
- Click the down arrow button, and select "(New)"
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
- Rename label at header
- Rename "Debtor" to "Comm"
- Rename "Code" to "Rate"
- Rename "Description" to "Commission"
- 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
- Right click the label of calcTotalComm in DetailBand1 and Copy
- Right click on "GroupFooter2" band and Paste
- Align the position of label in "GroupFooter2",
so that it is aligned to "calcTotalComm" in DetailBand1
- Align the position of label in "GroupFooter2",
- While this new label in "GroupFooter2" is selected
- 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
- Right click the label of calcTotalComm group total in GroupFooter2 and Copy
- 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
- Align the position of label in "SummaryBand1",
- While this new label in "SummaryBand1" is selected
- 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 top
|
Resources For AutoCount Software Developers
|