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 multi-location, area and customer.

Tasks in this tutorial

• Add Calculate Field in Report
• Construct a text that join UOMs

Product

AutoCount Accounting 1.8 / 1.9
Applicable to AutoCount Accounting 2.0 (require to manual modify some code)

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

Open Report Designer and Load Report Template

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
   

Add Calculated Field

1. 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

2. Set the property of newly added Calculated Field

   Locate & Select the newly added calculated field

   In Property Grid, rename (Name) to calcPacking

Add Script Event

1. Create new event of GetValue to calcPacking

   Click [+] to expand Scripts

2. Click the box on the right of "Get a Value"

   Then click the arrow button, and click (New)

3. Report Designer will switch to Scripts editor,

   and calcPacking_GetValue event is created.
   
   

Add using directive

1. Add directive at the top of Scripts, if has not already added.
   • using System.Data;
   • using System.Linq;

Insert scripts into calcPacking_GetValue

//Main Entry
private void calcPacking_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
{
	//Get all UOM of current Item, and store in DataTable
	DataTable tblUOM = GetUOMFromItem(GetCurrentColumnValue("ItemCode"));
	if (tblUOM == null)
		return;

	//Calculate the quantity of smallest UOM that has the same "ItemCode" and "UOMRate"
	//It is based on the design of this report that uses "Group Header" to group "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;

		//Get the rate of current UOM in tblUOM (DataTable)
		uomRate = BCE.Data.Convert.ToDecimal(row["Rate"]);

		//Store each formatted UOM Packing
		result = FormatCurrentUomResult(result,
			uomRate > 0 ? Math.Floor(smallestQty / uomRate) : 0,
			row["UOM"].ToString());

		//Calculate the remaining Smallest Qty for next UOM
		smallestQty = smallestQty % uomRate;
	}

	//Assign the "Packing" to calcPacking
	e.Value = result;
}

1. Add function of GetSmallestQty
2. Add function of GetUOMFromItem
3. Add function of FormatCurrentUomResult

Function of GetSmallestQty

//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"));
}

Function of GetUOMFromItem

//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());
}

Function of FormatCurrentUomResult

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

Add label to display "Packing"

1. 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

2. Click Preview tab to see the result

Go to menu

Go to top

Resources For AutoCount Software Developers