Report Script: Simple Calculate Commission by Item UDF Rate: Difference between revisions
Content added Content deleted
(Created page with "==Under Construction== ==Introduction== This tutorial shows how to apply report scripting to calculate commission that is based on item rate commission rate entitlement.<br/>...") |
No edit summary |
||
Line 1: | Line 1: | ||
==Under Construction== |
|||
==Introduction== |
==Introduction== |
||
This tutorial |
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.<br/> |
||
This example can also be done with '''Expression''' of Calculated Field without scripting. |
|||
Tasks in this tutorial:- |
Tasks in this tutorial:- |
||
*Add User Defined Field (UDF) |
*Add User Defined Field (UDF) |
||
*Add Calculate Field |
*Add Calculate Field in Report |
||
*Calculate commission of each item |
*Calculate commission of each item |
||
*Report - Group by Sale Agent |
|||
*Report - Add Group Total |
*Report - Add Group Total |
||
*Report - Add Report Total |
*Report - Add Report Total |
||
Line 14: | Line 12: | ||
==Product== |
==Product== |
||
AutoCount Accounting 2.0<br/> |
AutoCount Accounting 2.0<br/> |
||
Applicable to AutoCount Accounting 1.8 / 1.9 |
Applicable to AutoCount Accounting 1.8 / 1.9 (require to manual modify some coding) |
||
⚫ | |||
⚫ | |||
<!-- |
|||
⚫ | |||
⚫ | |||
--> |
|||
==Tutorial== |
==Tutorial== |
||
===Add UDF=== |
===Add UDF=== |
||
Create below udf in the table |
|||
{| class="wikitable" |
{| class="wikitable" |
||
|- |
|- |
||
Line 34: | Line 32: | ||
#Click [Print Listing] button |
#Click [Print Listing] button |
||
#Select option '''Print Invoice Detail Listing''' |
#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] |
|||
⚫ | |||
#:go to Report > Design Detail Listing Report |
#:go to Report > Design Detail Listing Report |
||
#Select |
#Select Report Template of '''"Invoice Detail Listing"''' |
||
=== |
===Add Calculated Field=== |
||
#While the report design is at '''Designer''' mode |
|||
# |
|||
#*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 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==== |
|||
<syntaxhighlight lang="csharp"> |
<syntaxhighlight lang="csharp"> |
||
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; |
|||
} |
|||
</syntaxhighlight> |
</syntaxhighlight> |
||
====for version 1.8 / 1.9==== |
|||
<syntaxhighlight lang="csharp"> |
|||
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; |
|||
} |
|||
</syntaxhighlight> |
|||
===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",<br/>so that it is aligned to "calcTotalComm" in '''DetailBand1''' |
|||
#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",<br/>so that it is aligned to "calcTotalComm" in '''DetailBand1''' and '''GroupFooter2''' |
|||
#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" |
|||
<br/> |
|||
*'''Click Preview''' |
|||
<br/> |
<br/> |