Report Script: Simple Calculate Commission by Item UDF Rate

From AutoCount Resource Center
Revision as of 07:45, 15 August 2018 by DanielY (talk | contribs) (→‎Add Calculated Field)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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.

Tips icons.png 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)

VSProjectIcon.png Download Report Template (AutoCount Accounting 2.0)

Simple Calculation.

Prog ivlistcomm.png

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] Link 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 layout form
    • 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

IconAC81.png Go to top
ProgrammerGo.jpg Resources For AutoCount Software Developers