Open main menu

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.

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