Report Script: Get Field that is not in Report v2

Introduction

This is one of the training example that uses AR Invoice Listing as the report template to get Project Description from SQL Server Database.

The sample code here is used in training, not optimized for performance.

Download Report Template (AutoCount Accounting 2.0)

Get Field that is not in report.

Report Script

Get Project Description and set the text to a label

Add Detail Report of "AR Invoice Master.AR Invoice Detail"
If the Detail Report has not been added
Under the newly added Detail Report is a Detail (band)
Add a new Label (XRLabel) in this Detail
Rename the Label (Name) in Property Grid, while this label is selected
name it as "xrProjDesc"
In Property Grid, Click [+] button of Scripts to reveal the events
Find Before Print, and click into the text box on the right
Then click the arrow button, and click (New)
Report Designer will switch to Scripts editor,
and xrProjDesc_BeforePrint is created.
Insert below coding into the event of xrProjDesc_BeforePrint
Click Preview tab to see the result

//Get Project Description and set the text to a label
private void xrProjDesc_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
	//Get ProjNo from "Field List"
	string projNo = DetailReport.GetCurrentColumnValue("ProjNo").ToString();
	//Create a string variable with empty string
	string projDesc = "";

	//Run this if statement when ProjNo is not empty
	if (projNo.Length > 0)
	{
		//Get Project Description from SQL Server
		object oProjDesc = __report.DBSetting.ExecuteScalar(
			"SELECT Description FROM Project WHERE ProjNo = ?", projNo);
		projDesc = oProjDesc != null ? oProjDesc.ToString() : "";
	}

	//Finally assign Project Description to this label
	(sender as XRLabel).Text = projDesc == "" ? "[No Project]" : projDesc;
}

While there are many ways which the designer can add addition field to the report.
Next example shows how to apply a Calculated Field to do exact same task.

Get Project Description and assign the text to Calculated Field

Back to Designer
In Field List, expand 1 Main Data: AR Invoice Master
Right click on AR Invoice Detail,
and click Add Calculated Field on the popup menu
Rename this Calculated Field (Name) in Property Grid, while this calculated field is selected
name it as "calcProjDesc"
In Property Grid, Click [+] button of Scripts to reveal the event of Calculated Field
Find Get a Value, and click into the text box on the right
Then click the arrow button, and click (New)
Report Designer will switch to Scripts editor,
and calcProjDesc_GetValue is created.
Insert below coding into the event of calcProjDesc_GetValue
Add a new Label onto Detail band of Detail Report
which the DataMember is "AR Invoice Master.AR Invoice Detail"
Bind the calcProjDesc to this new label
Drag calcProjDesc from AR Invoice Detail (table) in Field List onto the designer
Click Preview tab to see the result

//Get Project Description and assign the text to Calculated Field
private void calcProjDesc_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
{
	string projNo = DetailReport.GetCurrentColumnValue("ProjNo").ToString();
	string projDesc = "";

	if (projNo.Length > 0)
	{
		object oProjDesc = __report.DBSetting.ExecuteScalar(
			"SELECT Description FROM Project WHERE ProjNo = ?", projNo);
		projDesc = oProjDesc != null ? oProjDesc.ToString() : "";
	}

	//Assign Project Description to e.Value (value of this calculated field)
	e.Value = projDesc == "" ? "[No Project]" : projDesc;
}

Differences of direct assign to label.Text and assign to a Calculated Field

Though the task is the same, which both display the result in a label.
Direct assign value to a label.Text does not store the value in the Table.
While, Calculated Field is able to temporary store the value.

Calculated Field is especially useful when the report needs to calculate the total of result(s).
"Simple Calculate Commission by Item UDF Rate" shows how to add Group Total and Grand Total with calculated field.
Furthermore, designer does not have to worry about the replacement to the control,
as the replacement of a label to another control will require only rebinding of data.

Get project no. from Detail, and show the Project Description as a string with 'comma' separated

This is an example of how to grab the detail(s) and display as a text on header.

Below coding requires adding directive of using System.Data;
(add at the top of the report script editor)

//Get project no. from Detail, and show the Project Description as a string with 'comma' separated
private void calculatedField1_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
{
	//Get the DocKey of current document from "Field List"
	long docKey = AutoCount.Converter.ToInt64(GetCurrentColumnValue("DocKey"));

	//Assign a string of "Project Description(s)" to this "Calculated Field"
	e.Value = GetProjDescOnHeader(docKey);
}

private string GetProjDescOnHeader(long docKey)
{
	string projNoSqlList = BuildSqlProjectRangeIn(docKey);
	string result = "";
	DataRow row;
	if (!string.IsNullOrEmpty(projNoSqlList))
	{
		//SQL Query that find Description of project that is non-repetitive and sorted
		DataTable tbl = __report.DBSetting.GetDataTable(
			string.Format("SELECT [Description] FROM Project WHERE ProjNo in ({0}) ORDER BY Description"
			, projNoSqlList), false);

		for (int i=0; i < tbl.Rows.Count; i++)
		{
			row = tbl.Rows[i];
			result = result == ""
				? row["Description"].ToString()
				: string.Format("{0}, {1}", result, row["Description"].ToString());
		}
	}

	return result == "" ? "[No Project]" : result;
}

Build a Sql string of Multiple Projects

Build a string for SQL SELECT statement with condition of one or more than one projects
Example of Multiple values in SQL statement:

SELECT [Description] FROM Project WHERE ProjNo in ('ProjA', 'ProjB')

The code in BuildSqlProjectRangeIn has not been optimized for performance.
Not suitable for customer whom have more than 500 details in an Invoice.

Suggest adding filter to empty ProjNo and distinct the ProjNo before process in the loop.

private string BuildSqlProjectRangeIn(long docKey)
{
	//Get the DataSet of this report
	DataSet ds = Report.DataSource as DataSet;
	//Get the Detail table in this DataSet
	DataTable tblDetail = ds.Tables["Detail"];

	//Prepare variables
	DataRow row;
	string projNo = "";
	string projNoSqlList = "";

	//Find all detail that is related to current DocKey (current document)
	DataRow[] sRows = tblDetail.Select(string.Format("DocKey={0}", docKey));

	//Loop the rows that is/are selected from the DataTable in "Field List"
	for (int i=0; i < sRows.Length; i++)
	{
		//assign to a DataRow.
		//This DataRow is a reference to an array of DataRow (sRows) by index
		row = sRows[i];
		projNo = row["ProjNo"].ToString();

		//Skip if ProjNo is empty value, and avoid repeating in the list
		if (!string.IsNullOrEmpty(projNo) && !projNoSqlList.Contains(projNo))
		{
			//Add ProjNo to ProjNoSqlList
			projNoSqlList = projNoSqlList == ""
				? string.Format("'{0}'", projNo)
				: string.Format("{0},'{1}'", projNoSqlList, projNo);
		}
		else
		{
		}
	}

	return projNoSqlList;
}

Go to menu

Go to top

Resources For AutoCount Software Developers