2,754
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 08:17, 23 July 2018
, 5 years agono edit summary
(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...") |
No edit summary |
||
Line 1:
==Introduction==
This
{{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:
==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 ⟶ 42:
</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 50 ⟶ 81:
</syntaxhighlight>
===Difference to direct assign to '''label.Text''' and assign to a '''Calculated Field'''===
Though the task is the same, which both can display the result in a label,<br/>
directly assign result to a label.Text does not store the value in the Table.<br/>
While, Calculated Field is able to temporary store the value.
Calculated Field is especially useful when the report needs to calculate<br/>
the total of result(s).
<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 will be 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 Project Range====
Build a string for SQL SELECT with Multiple values<br/>
Example of Multiple values in SQL:<br/>
SELECT [Description] FROM Project WHERE ProjNo in (''''ProjA', 'ProjB'''')
<syntaxhighlight lang="csharp">
private string BuildSqlProjectRangeIn(long docKey)
{
//Get the DataSet of this report
Line 60 ⟶ 143:
//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 79 ⟶ 159:
row = sRows[i];
//
if (row["ProjNo"]
{
projNo = row["ProjNo"].ToString();
//Add ProjNo to ProjNoSqlList
Line 97 ⟶ 169:
: string.Format("{0},'{1}'", projNoSqlList, projNo);
}
else
{
continue;
}
}
return projNoSqlList;
}
</syntaxhighlight>
<br/>
[[Category:Programmer]]
[[Category:Tutorials]]
| |||