2,743
edits
Report Script: Get Field that is not in Report v2: Difference between revisions
Report Script: Get Field that is not in Report v2 (view source)
Revision as of 03:31, 20 February 2019
, 5 years ago→Get project no. from Detail, and show the Project Description as a string with 'comma' separated
(Created page with "==Introduction== This sample uses AR Invoice Listing as the report template to get Project Description from SQL Server Database. {{SourceDownload|link=https://drive.google.co...") |
(→Get project no. from Detail, and show the Project Description as a string with 'comma' separated) |
||
| (16 intermediate revisions by the same user not shown) | |||
Line 1:
==Introduction==
This
{{Note|The sample code here is used in training, not optimized for performance.}}
{{SourceDownload|link=https://drive.google.com/open?id=1u10Aq8PnC4hYMbOYs_uMnXb3U_i4xrNq|remark=(AutoCount Accounting 2.0)|Download Report Template|Get Field that is not in report.}}
Line 7 ⟶ 8:
==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,<br/>and '''xrProjDesc_BeforePrint''' is created.
#Insert below coding into the event of xrProjDesc_BeforePrint
#Click '''Preview''' tab to see the result
<syntaxhighlight lang="csharp">
//Get Project Description and set the text to a label
Line 30 ⟶ 43:
</syntaxhighlight>
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,<br/>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
<syntaxhighlight lang="csharp">
//Get Project Description and
private void calcProjDesc_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
{
Line 49 ⟶ 81:
}
</syntaxhighlight>
<br/><br/>
==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).
*:[[Report Script: Simple Calculate Commission by Item UDF Rate|"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.
<br/><br/>
==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)
<syntaxhighlight lang="csharp">
//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);
}
</syntaxhighlight>
<syntaxhighlight lang="csharp">
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;
}
</syntaxhighlight>
===Build a Sql string of Multiple Projects===
Build a string for SQL SELECT statement with condition of one or more than one projects<br/>
Example of Multiple values in SQL statement:
SELECT [Description] FROM Project WHERE ProjNo in (''''ProjA', 'ProjB'''')
{{Note|The code in BuildSqlProjectRangeIn has not been optimized for performance.<br/>Not suitable for customer whom have more than 500 details in an Invoice.<br/>
Suggest adding filter to empty ProjNo and distinct the ProjNo before process in the loop.
}}
<syntaxhighlight lang="csharp">
private string BuildSqlProjectRangeIn(long docKey)
{
//Get the DataSet of this report
Line 60 ⟶ 150:
//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}"
//Loop the rows that is/are selected from the DataTable in "Field List"
Line 78 ⟶ 165:
//This DataRow is a reference to an array of DataRow (sRows) by index
row = sRows[i];
projNo = row["ProjNo"].ToString();
//
if (!string.IsNullOrEmpty(projNo) && !projNoSqlList.Contains(projNo))
{
//Add ProjNo to ProjNoSqlList
projNoSqlList = projNoSqlList == ""
Line 97 ⟶ 175:
: string.Format("{0},'{1}'", projNoSqlList, projNo);
}
else
{
}
}
return projNoSqlList;
}
</syntaxhighlight>
<br/>
[[Category:Programmer]]
[[Category:Tutorials]]
| |||