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.
- Report Designer will switch to Scripts editor,
- 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.
- Report Designer will switch to Scripts editor,
- 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).
- 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 top
|
Resources For AutoCount Software Developers
|