Report Script: Get Field that is not in Report v2

Introduction

This sample uses AR Invoice Listing as the report template to get Project Description from SQL Server Database.

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

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

Get Project Description and set the text to Calculated Field

//Get Project Description and set 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;
}

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

• 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 DataSet of this report
	DataSet ds = Report.DataSource as DataSet;
	//Get the Detail table in this DataSet
	DataTable tblDetail = ds.Tables["Detail"];

	//Get the DocKey of current document from "Field List"
	long docKey = AutoCount.Converter.ToInt64(GetCurrentColumnValue("DocKey"));

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

	//Find all detail that is related to current DocKey (current document)
	DataRow[] sRows = tblDetail.Select("DocKey=" + docKey.ToString());

	//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];

		//If ProjNo is empty value, skip
		if (row["ProjNo"] == DBNull.Value)
		{
			continue;
		}
		else
		{
			projNo = row["ProjNo"].ToString();

			//Avoid repeating list
			if (projNoSqlList.Contains(projNo))
				continue;

			//Add ProjNo to ProjNoSqlList
			projNoSqlList = projNoSqlList == ""
				? string.Format("'{0}'", projNo)
				: string.Format("{0},'{1}'", projNoSqlList, projNo);
		}
	}

	string result = "";
	if (!string.IsNullOrEmpty(projNoSqlList))
	{
		//SQL Query that find Description of project that will be non-repetitive and sorted
		DataTable tbl = __report.DBSetting.GetDataTable(
			string.Format("SELECT DISTINCT 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());
		}
	}

	//Assign a string of "Project Description(s)" to this "Calculated Field"
	e.Value = result == "" ? "[No Project]" : result;
}

Go to menu

Go to top

Resources For AutoCount Software Developers