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 in consideration of area and customer.

Product

AutoCount Accounting 1.8 / 1.9 Applicable to AutoCount Accounting 2.0

Download Report Template (AutoCount Accounting 2.0) Packing in Delivery Detail Listing.

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 = "";
	foreach (DataRow row in tblUOM.Rows)
	{
		if (smallestQty < 1)
			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)
		)
		.GroupBy(r => new {
			ItemCode = r.Field<string>("ItemCode"),
			UOMRate = r.Field<decimal>("UOMRate")
		})
		.Select(g => g.Sum(r => r.Field<decimal>("Qty") * r.Field<decimal>("Rate")))
		.FirstOrDefault();
}

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