Difference between revisions of "Report Script: Create a Packing List in report script"

From AutoCount Resource Center
Jump to navigation Jump to search
Line 64: Line 64:
decimal uomRate;
decimal uomRate;
string result = "";
string result = "";
//Loop all uoms in the UOM Table
foreach (DataRow row in tblUOM.Rows)
foreach (DataRow row in tblUOM.Rows)

Revision as of 03:48, 2 August 2018


This tutorial shows how to add a text which displays packing that is based on largest UOM to smallest UOM with report scripting.

Does not take into consideration of area and customer.


AutoCount Accounting 1.8 / 1.9
Applicable to AutoCount Accounting 2.0

VSProjectIcon.png Download Report Template (AutoCount Accounting 1.8 / 1.9)

Packing in Delivery Detail Listing.

Prog PickList.PackUOM.png

UOM Table
UOM Rate

Report Script

Show the packing from any UOM

  1. At main window, go to Sales | Delivery Order
  2. On the main menu, click Report | Design Detail Listing Report
  3. Select "Picking List - Delivery Order" report template

  4. Insert a Calculated Field
    While the view is at Designer,
    Right click 1 Main Data: Delivery Order Master (aka. Master table)
    and Add Calculated Field
  5. Set the property of newly added Calculated Field
    Locate & Select the newly added calculated field
    In Property Grid, rename (Name) to calcPacking
  6. Create new event of GetValue to calcPacking
    Click [+] to expand Scripts
    Click the box on the right of "Get a Value"
    Then click the arrow button, and click (New)
    Report Designer will switch to Scripts editor,
    and calcPacking_GetValue event is created.

  7. Add directive at the top of Scripts, if has not already added.
    • using System.Data;
    • using System.Linq;
  8. Insert scripts into calcPacking_GetValue
  9. Add function of GetSmallestQty
  10. Add function of GetUOMFromItem
  11. Add function of FormatCurrentUomResult

  12. Add a new Label into existing Group Header
    Bind the calcProjDesc to this new label
    Drag calcPacking from 1 Main Data: Delivery Order Master in Field List onto the new label
  13. Click Preview tab to see the result
//Main Entry
private void calcPacking_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
	DataTable tblUOM = GetUOMFromItem(GetCurrentColumnValue("ItemCode"));
	if (tblUOM == null)

	//Get total smallest UOM Qty of same ItemCode and UOMRate 
	decimal smallestQty = GetSmallestQty(GetCurrentColumnValue("ItemCode"), GetCurrentColumnValue("UOMRate"));

	decimal uomRate;
	string result = "";

	//Loop all uoms in the UOM Table
	foreach (DataRow row in tblUOM.Rows)
		if (smallestQty <= 0)

		uomRate = BCE.Data.Convert.ToDecimal(row["Rate"]);
		result = FormatCurrentUomResult(result,
			uomRate > 0 ? Math.Floor(smallestQty / uomRate) : 0,

		//Calculate the remaining Smallest Qty for next UOM
		smallestQty = smallestQty % uomRate;
	e.Value = result;
//ItemCode and UOMRate are the grouping in this report
private decimal GetSmallestQty(object oItemCode, object oUomRate)
    if (oItemCode == null)
        return 0;

    return (Report.DataSource as DataSet).Tables["Master"].AsEnumerable()
        .Where(r =>
            r.Field<string>("ItemCode") == oItemCode.ToString() &&
            r.Field<decimal>("UOMRate") == BCE.Data.Convert.ToDecimal(oUomRate)
        ).Sum(r => r.Field<decimal>("Qty") * r.Field<decimal>("Rate"));
//Get the table of available UOM of one Item
private DataTable GetUOMFromItem(object oItemCode)
	if (oItemCode == null)
		return null;
	return __report.DBSetting.GetDataTable(
		"SELECT UOM, Rate FROM ItemUOM WHERE ItemCode=? ORDER BY Rate DESC", false, oItemCode.ToString());
private string FormatCurrentUomResult(string result, decimal qty, string uom)
	//Format the display text of qty and uom
	if (qty == 0)
		return result;
	string formatQtyUom = string.Format("{0} {1}", qty, uom);
	return string.IsNullOrEmpty(result) ? formatQtyUom
		: string.Format("{0}, {1}", result, formatQtyUom);

Go to menu

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