Report Script: Simple Calculate Commission by Item UDF Rate: Difference between revisions
Content added Content deleted
mNo edit summary |
|||
Line 40: | Line 40: | ||
===Add Calculated Field=== |
===Add Calculated Field=== |
||
#While the report design is at '''Designer''' |
#While the report design is at '''Designer''' layout form |
||
#*Right click '''1 Main Data: Invoice Master''' in '''Field List''' |
#*Right click '''1 Main Data: Invoice Master''' in '''Field List''' |
||
#*Click '''Add Calculated Field''' |
#*Click '''Add Calculated Field''' |
Latest revision as of 07:45, 15 August 2018
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
|