Report Script: Get Field that is not in Report v2

From AutoCount Resource Center
Jump to navigation Jump to search

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.

Tips icons.png The sample code here is used in training, not optimized for performance.
VSProjectIcon.png Download Report Template (AutoCount Accounting 2.0)

Get Field that is not in report.

ARInvoiceListing.ProjDesc.png

Report Script

Get Project Description and set the text to a label

  1. Add Detail Report of "AR Invoice Master.AR Invoice Detail"
    If the Detail Report has not been added
  2. Under the newly added Detail Report is a Detail (band)
  3. Add a new Label (XRLabel) in this Detail
  4. Rename the Label (Name) in Property Grid, while this label is selected
    name it as "xrProjDesc"
  5. In Property Grid, Click [+] button of Scripts to reveal the events
  6. Find Before Print, and click into the text box on the right
  7. Then click the arrow button, and click (New)
    Report Designer will switch to Scripts editor,
    and xrProjDesc_BeforePrint is created.
  8. Insert below coding into the event of xrProjDesc_BeforePrint
  9. 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

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



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')
Tips icons.png 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

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