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
mNo edit summary |
(→Get project no. from Detail, and show the Project Description as a string with 'comma' separated) |
||
| (12 intermediate revisions by the same user not shown) | |||
Line 1:
==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.
{{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 80 ⟶ 81:
}
</syntaxhighlight>
<br/><br/>
==
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).▼
▲===Difference to direct assign to '''label.Text''' and assign to a '''Calculated Field'''===
*:[[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.]]
▲Though the task is the same, which both 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
*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/>
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;'''
Line 116 ⟶ 118:
if (!string.IsNullOrEmpty(projNoSqlList))
{
//SQL Query that find Description of project that
DataTable tbl = __report.DBSetting.GetDataTable(
string.Format("SELECT [Description] FROM Project WHERE ProjNo in ({0}) ORDER BY Description"
Line 134 ⟶ 136:
</syntaxhighlight>
Build a string for SQL SELECT statement with
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)
Line 160 ⟶ 165:
//This DataRow is a reference to an array of DataRow (sRows) by index
row = sRows[i];
//Skip if ProjNo is empty value, and avoid repeating in the list
if (
{
▲ projNo = row["ProjNo"].ToString();
//Add ProjNo to ProjNoSqlList
projNoSqlList = projNoSqlList == ""
| |||