Report Script: Create a Packing List in report script

From AutoCount Resource Center

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)

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

Packing in Delivery Detail Listing.

Prog PickList.PackUOM.png

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

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