Report Script: Create a Packing List in report script

Introduction

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.

Product

AutoCount Accounting 1.8 / 1.9
Applicable to AutoCount Accounting 2.0

Download Report Template (AutoCount Accounting 1.8 / 1.9) Packing in Delivery Detail Listing.

UOM Table
UOM	Rate
UNIT	1
BOX	2
CTN	6

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)
		return;

	//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)
			break;

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

		//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

Go to top

Resources For AutoCount Software Developers