Report Script: Filter Overdue Letter with specific age that is due

Introduction

A requirement to show Overdue Letter that is based on specific age range was brought to my attention.
I decided to write this tutorial to show how we can manipulate DataTable in the report with Report Script.

Attempted to apply DataView.RowFilter on the detail table that is in a DataRelation was unsuccessful.
Therefore, in this tutorial will delete the record in the DataTable that is not in the overdue age range.

Task in this tutorial

• Remove detail record, and show documents overdue age between 30-59.
• Calculate the total sum of amount due.

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) Coming soon...

Report Script

Add using directive

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

Add function of FilterDueAge

FilterDueAge removes detail table record that is not in the filter range of Age.

private void FilterDueAge(int fromAge, int toAge)
{
	//Get table of documents that are due (Detail table)
	DataTable dtDetail = (__report.DataSource as DataSet).Tables["Detail"];

	//Find document record that the age is not in the age range
	DataRow[] rows = dtDetail.Select(string.Format("Age < {0} OR Age > {1}", fromAge, toAge));

	//Delete document record
	foreach (DataRow row in rows)
	{ row.Delete(); }

	//Above execution may render to debtor has no overdue document,
	//below function is to remove debtor record that has no overdue document.
	RemoveEmptyMaster();
}

Add function of RemoveEmptyMaster

RemoveEmptyMaster function removes master record that has no Overdue document in detail.

private void RemoveEmptyMaster()
{
	//Get Master and Detail tables
	DataTable dtMaster = (__report.DataSource as DataSet).Tables["Master"];
	DataTable dtDetail = (__report.DataSource as DataSet).Tables["Detail"];

	string debtorCode;

	//Delete master record that has no detail (overdue document)
	foreach (DataRow rowM in dtMaster.Rows)
	{
		debtorCode = rowM["AccNo"].ToString();

		//Find DebtorCode in Detail Table
		DataRow[] selectRows = dtDetail.Select(string.Format("DebtorCode='{0}'", debtorCode));
		if (selectRows.Length == 0)
		{
			rowM.Delete();
		}
	}
}

Add function call in Report_BeforePrint event

Before this report is processed, add the function call in the event that is triggered at the beginning.

• Assign value of from age and to age for the function
• If the report is for overdue age that is greater than 60, assign value of 60 and 999.

private void Report_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
	__report = Report as BCE.AutoCount.Report.BaseReport;

	//Filter document that the age is between 30 and 59.
	//The result of Overdue Letter will show document that the due age is 30 to 59.
	FilterDueAge(30, 59);
}

Add/Replace a label to show the letter overdue message with Total Amount Due

1. Add/Replace a label, and amend the (Name) to "xrLabelOverdueTitle"
2. Type in the text "Our record shows that an amount of {0} is now overdue. A list of overdue invoices is shown as below:"
   • {0} is a parameter that will be replaced with the value of Total Amount Due in string.Format(...).
3. In Property Grid, Click [+] button of Scripts to reveal the events
4. Find Before Print, and click into the text box on the right
5. Then click the arrow button, and click (New)

   Report Designer will switch to Scripts editor,

   and xrLabelOverdueTitle_BeforePrint event is created.

Calculate Total Overdue Amount after records in detail table is updated

Apply a method Compute(...) of DataTable object is used to calculate the sum of AmountDue.

• Add a local variable myOverdueMsg

private string myOverdueMsg = null;

• Insert script to xrLabelOverdueTitle_BeforePrint event

private void xrLabelOverdueTitle_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
	object oAccNo = GetCurrentColumnValue("AccNo");
	if (oAccNo == null)
	{
		(sender as XRLabel).Text = "";
		return;
	}
	//Store the original text of this label
	if (myOverdueMsg == null)
	{
		myOverdueMsg = (sender as XRLabel).Text;
	}

	//Calculate the current debtor total overdue amount
	object oTotalAmtDue = (__report.DataSource as DataSet).Tables["Detail"]
		.Compute("SUM(AmountDue)", string.Format("DebtorCode='{0}'", oAccNo.ToString()));
	decimal totalAmtDue = oTotalAmtDue == null ? 0 : BCE.Data.Convert.ToDecimal(oTotalAmtDue);

	//Assign 'total amount due' to this label, while format the decimal digit with currency formatting
	(sender as XRLabel).Text = string.Format(myOverdueMsg, __report.DecimalSetting.FormatCurrency(totalAmtDue));
}

Go to menu

Go to top

Resources For AutoCount Software Developers