(→Steps) |
|||
(32 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
==Introduction== | ==Introduction== | ||
Sometime in the report you might wanted some extra information but you not able to select in the report design. So | Sometime in the report you might wanted some extra information but you not able to select in the report design. So you had to self query to get the extra information. | ||
== | ==Get Single Field== | ||
Below is | ===Example 1 - Get Shelf Field from Maintain Item=== | ||
[[File:RB-GetDirectDB-01.jpg|800 px]] | Below is Example are doing following actions using '''Variable''' | ||
* At Sales Invoice to get '''Shelf''' field from '''Maintain Item''' | |||
Last Script Update : 12 Oct 2015<br /> | |||
'''Steps''' | |||
[[File:RB-GetDirectDB-01.jpg|800 px|center]] | |||
:01. Click on '''SubRptNorm: Document_Detail''' | :01. Click on '''SubRptNorm: Document_Detail''' | ||
:02. Select the '''Variable''' button (the Calculator icon) | :02. Select the '''Variable''' button (the Calculator icon) | ||
:03. Click on the place to be print | :03. Click on the place to be print/shown. | ||
:04. Click on '''Calc''' Tab | :04. Click on '''Calc''' Tab | ||
[[File:RB-GetDirectDB-02.jpg|800 px]] | [[File:RB-GetDirectDB-02.jpg|800 px|center]] | ||
:05. Right click here & Select '''Event''' & find the component just now we had placed (eg Variable2) | :05. Right click here & Select '''Event''' & find the component just now we had placed (eg Variable2) | ||
:06. Click '''OnCalc''' | :06. Click '''OnCalc''' | ||
Line 19: | Line 26: | ||
s := 'SELECT Shelf FROM ST_ITEM ' + | s := 'SELECT Shelf FROM ST_ITEM ' + | ||
'WHERE CODE=' + QuotedStr(Document_Detail.GetFieldValue('ItemCode')); | 'WHERE CODE=' + QuotedStr(Document_Detail.GetFieldValue('ItemCode')); | ||
Value := Trim(DBSQL_GetFieldValue(s)); | |||
end; | |||
</syntaxhighlight> | |||
:08. Click '''File | Save As''' & enter New report Name after done | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 2 - Get Picture Field from Maintain Item=== | |||
Below is Example are doing following actions using '''Image''' | |||
* At Sales Invoice to get '''Picture''' field from '''Maintain Item''' | |||
Last Script Update : 18 Jul 2016<br /> | |||
'''Steps''' | |||
:01. Click on '''SubRptNorm: Document_Detail''' | |||
:02. Select the '''Image''' button (the Mountain & sun icon) | |||
:03. Click on the place to be print/shown. | |||
:04. Click on '''Calc''' Tab | |||
:05. Right click here & Select '''Event''' & find the component just now we had placed (eg Image1) | |||
:06. Click '''OnPrint''' | |||
:07. Click here & enter the below script | |||
<syntaxhighlight lang="delphi"> | |||
procedure Image1OnPrint; | |||
var s: string; | |||
begin | |||
s := 'SELECT Picture FROM ST_ITEM ' + | |||
'WHERE Code='+ QuotedStr(Document_Detail.GetFieldValue('ItemCode')); | |||
Image1.Visible := DBSQL_GetPicture(s, Image1.Picture); | |||
end; | |||
</syntaxhighlight> | |||
:08. Click '''File | Save As''' & enter New report Name after done | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 3 - Get Document Created UserName from Audit=== | |||
Below is Example is to Get the who created the Document from Audit Table. | |||
Last Script Update : 22 May 2017<br /> | |||
'''Steps''' | |||
:01. Select the '''Variable''' button (the Calculator icon) | |||
:02. Click on the place to be print/shown. | |||
:03. Click on '''Calc''' Tab | |||
:04. Right click here & Select '''Event''' & find the component just now we had placed (eg Variable2) | |||
:05. Click '''OnCalc''' | |||
:06. Click here & enter the below script | |||
<syntaxhighlight lang="delphi"> | |||
procedure Variable2OnCalc(var value: Variant); | |||
var s: string; | |||
begin | |||
s := 'SELECT UserName FROM AUDIT WHERE UPDATEKIND=''I'' ' + | |||
'AND REFERENCE LIKE ' + | |||
QuotedStr('%'+ | |||
Main.GetFieldValue('DocNo')+ | |||
'%Code: '+ //Delete this line for JV & CB | |||
Main.GetFieldValue('Code')+ //Delete this line for JV & CB | |||
',%'); | |||
Value := Trim(DBSQL_GetFieldValue(s)); | |||
end; | |||
</syntaxhighlight> | |||
:08. Click '''File | Save As''' & enter New report Name after done | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 4 - Get From Doc No. in Sales Invoice=== | |||
Below is Example is to Get the From Document Number at Invoice Header. | |||
Last Script Update : 14 Feb 2018<br /> | |||
'''Steps''' | |||
:01. Select the '''Variable''' button (the Calculator icon) | |||
:02. Click on the place to be print/shown. | |||
:03. Click on '''Calc''' Tab | |||
:04. Right click here & Select '''Event''' & find the component just now we had placed (eg Variable2) | |||
:05. Click '''OnCalc''' | |||
:06. Click here & enter the below script | |||
<syntaxhighlight lang="delphi"> | |||
procedure Variable2OnCalc(var value: Variant); | |||
var s, V : string; | |||
begin | |||
s := 'SELECT First 1 FromDocType FROM SL_IVDTL '+ | |||
'WHERE Dockey=' + Main.GetFieldValue('Dockey') + | |||
' AND FROMDOCTYPE IS NOT NULL'; | |||
V := Trim(DBSQL_GetFieldValue(s)); | |||
if Trim(V) <> '' then begin | |||
s := 'SELECT DocNo FROM SL_' + v + | |||
' WHERE DocKey=(SELECT First 1 FromDockey FROM SL_IVDTL '+ | |||
'WHERE Dockey=' + Main.GetFieldValue('Dockey') + | |||
' AND FROMDOCTYPE IS NOT NULL)'; | |||
Value := Trim(DBSQL_GetFieldValue(s)); | Value := Trim(DBSQL_GetFieldValue(s)); | ||
end else | |||
Value := ''; | |||
end; | end; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
:08. Click '''File | Save As''' & enter New report Name after done | :08. Click '''File | Save As''' & enter New report Name after done | ||
* | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==Get Whole Table== | |||
===Example 1 - Get Maintain Batch Information=== | |||
Below is Example doing following actions | |||
* Get data information From Stock Batch | |||
Last Script Update : 12 Jul 2018 | |||
Steps | |||
[[File:RB-GetDirectDB-03.jpg|800 px|center]] | |||
:01. Click on '''Calc''' Tab | |||
:02. Right click here & Select '''Module''' | |||
:03. Select '''Declarations''' | |||
:04. Select '''Variables''' | |||
:05. Add new/to existing as below variable | |||
<syntaxhighlight lang="delphi"> | |||
var | |||
SQL_Batch: String; | |||
</syntaxhighlight> | |||
<br /> | |||
[[File:RB-GetDirectDB-04.jpg|800 px|center]] | |||
:06. Select '''Events''' | |||
:07. Select '''OnCreate''' | |||
:08. Copy below script & paste to here | |||
<syntaxhighlight lang="delphi"> | |||
SQL_Batch := 'SELECT Code, Description, ExpDate, MfgDate, Remark1, Remark2 FROM ST_BATCH'; | |||
DBSQL_SELECT(plSQL_Batch, SQL_Batch, 'Code'); | |||
SetDataPipelineFieldLink(Document_Detail, plSQL_Batch, 'Batch', 'Code'); | |||
</syntaxhighlight> | |||
<br /> | |||
:09. Click ''File | Save As...'' to save the file (eg Sales Invoice 1) | |||
:10. Click '''File | Exit''' to exit the report design | |||
:11. Click Design again in the report designer for the file just save on Steps 9 (eg Sales Invoice 1) | |||
[[File:RB-GetDirectDB-05.jpg|800 px|center]] | |||
:12. Click SubRptNorm:Document_Detail tab | |||
:13. Click DBText icon | |||
:14. Click the place you wanted to show/print | |||
:15. Select plSQL_Batch pipeline | |||
:16. Type ExpDate (eg to get expired Date) | |||
:17. Save the report | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Example 2 - Row Number Query=== | |||
Below is Example Create SEQ Field base on SQL | |||
Last Script Update : 04 May 2019 | |||
Steps | |||
:01. Click on '''Calc''' Tab | |||
:02. Right click here & Select '''Module''' | |||
:03. Select '''Declarations''' | |||
:04. Select '''Variables''' | |||
:05. Add new/to existing as below variable | |||
<syntaxhighlight lang="delphi"> | |||
var | |||
SQL_1: String; | |||
</syntaxhighlight> | |||
:06. Select '''Events''' | |||
:07. Select '''OnCreate''' | |||
:08. Copy below script & paste to here | |||
<syntaxhighlight lang="delphi"> | |||
SQL_1 := 'SELECT DOCKEY, DTLKEY, SEQ, ROW_NUMBER() OVER (PARTITION BY DOCKEY ORDER BY SEQ) AS NSEQ '+ | |||
'FROM SL_IVDTL ' + | |||
'WHERE NOT (DESCRIPTION LIKE ''R-%'' ' + | |||
'OR DESCRIPTION LIKE ''T-%'') '+ | |||
'AND DOCKEY IN (SELECT DOCKEY FROM SL_IV '+ | |||
' WHERE DOCDATE BETWEEN ' + FormatSQLDate(Parameter.GetFieldValue('DateFrom')) + | |||
' AND ' + FormatSQLDate(Parameter.GetFieldValue('DateTo')) + | |||
') ORDER BY DocKey, NSeq'; | |||
DBSQL_SELECT(plSQL_1, SQL_1, 'Dockey;NSeq'); | |||
SetDataPipelineFieldLink(Document_Detail, plSQL_1, 'Dockey;Seq', 'Dockey;Seq'); | |||
</syntaxhighlight> | |||
[[File:RB-GetDirectDB-06.jpg|800 px|center]] | |||
:09. Select '''Programs''' | |||
:10. Right Click | New Function | |||
:11. Copy Function Script | |||
<syntaxhighlight lang="delphi"> | |||
function FormatSQLDate(D: TDateTime): String; | |||
var AFormat: string; | |||
begin | |||
AFormat := 'dd mmm yyyy'; //'dd/mmm/yyyy' if can't | |||
Result := QuotedStr(FormatDateTime(AFormat, D)); | |||
//If yr output for TxQuery use below coding | |||
// AFormat :='dd/mm/yyyy'; | |||
// Result := '#'+FormatDateTime(AFormat, D)+'#'; | |||
end; | |||
</syntaxhighlight> | |||
<br /> | |||
:12. Click ''File | Save As...'' to save the file (eg Sales Invoice 1) | |||
:13. Click '''File | Exit''' to exit the report design | |||
:14. Click Design again in the report designer for the file just save on Steps 12 (eg Sales Invoice 3 (SubTotal)-New) | |||
:15. Click SubRptNorm:Document_Detail tab | |||
:16. Click DBText icon | |||
:17. Click the place you wanted to show/print | |||
:18. Select plSQL_1pipeline | |||
:19. Type NSeq | |||
:20. Save the report | |||
{| style="margin: 1em auto 1em auto;" | |||
|- | |||
| | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
{| | |||
|- | |||
| [[File:Template.Warning-01.jpg|80px]]|| | |||
: Preview/Print/Export will delay/slow a bit if compare with build in Preview/Print/Export report | |||
|} | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
|} | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==See also== | |||
* [[Report Designer]] | |||
* Others [[Customisation]] |
Latest revision as of 04:05, 4 May 2019
Introduction
Sometime in the report you might wanted some extra information but you not able to select in the report design. So you had to self query to get the extra information.
Get Single Field
Example 1 - Get Shelf Field from Maintain Item
Below is Example are doing following actions using Variable
- At Sales Invoice to get Shelf field from Maintain Item
Last Script Update : 12 Oct 2015
Steps
- 01. Click on SubRptNorm: Document_Detail
- 02. Select the Variable button (the Calculator icon)
- 03. Click on the place to be print/shown.
- 04. Click on Calc Tab
- 05. Right click here & Select Event & find the component just now we had placed (eg Variable2)
- 06. Click OnCalc
- 07. Click here & enter the below script
procedure Variable2OnCalc(var value: Variant);
var s: string;
begin
s := 'SELECT Shelf FROM ST_ITEM ' +
'WHERE CODE=' + QuotedStr(Document_Detail.GetFieldValue('ItemCode'));
Value := Trim(DBSQL_GetFieldValue(s));
end;
- 08. Click File | Save As & enter New report Name after done
Example 2 - Get Picture Field from Maintain Item
Below is Example are doing following actions using Image
- At Sales Invoice to get Picture field from Maintain Item
Last Script Update : 18 Jul 2016
Steps
- 01. Click on SubRptNorm: Document_Detail
- 02. Select the Image button (the Mountain & sun icon)
- 03. Click on the place to be print/shown.
- 04. Click on Calc Tab
- 05. Right click here & Select Event & find the component just now we had placed (eg Image1)
- 06. Click OnPrint
- 07. Click here & enter the below script
procedure Image1OnPrint;
var s: string;
begin
s := 'SELECT Picture FROM ST_ITEM ' +
'WHERE Code='+ QuotedStr(Document_Detail.GetFieldValue('ItemCode'));
Image1.Visible := DBSQL_GetPicture(s, Image1.Picture);
end;
- 08. Click File | Save As & enter New report Name after done
Example 3 - Get Document Created UserName from Audit
Below is Example is to Get the who created the Document from Audit Table.
Last Script Update : 22 May 2017
Steps
- 01. Select the Variable button (the Calculator icon)
- 02. Click on the place to be print/shown.
- 03. Click on Calc Tab
- 04. Right click here & Select Event & find the component just now we had placed (eg Variable2)
- 05. Click OnCalc
- 06. Click here & enter the below script
procedure Variable2OnCalc(var value: Variant);
var s: string;
begin
s := 'SELECT UserName FROM AUDIT WHERE UPDATEKIND=''I'' ' +
'AND REFERENCE LIKE ' +
QuotedStr('%'+
Main.GetFieldValue('DocNo')+
'%Code: '+ //Delete this line for JV & CB
Main.GetFieldValue('Code')+ //Delete this line for JV & CB
',%');
Value := Trim(DBSQL_GetFieldValue(s));
end;
- 08. Click File | Save As & enter New report Name after done
Example 4 - Get From Doc No. in Sales Invoice
Below is Example is to Get the From Document Number at Invoice Header.
Last Script Update : 14 Feb 2018
Steps
- 01. Select the Variable button (the Calculator icon)
- 02. Click on the place to be print/shown.
- 03. Click on Calc Tab
- 04. Right click here & Select Event & find the component just now we had placed (eg Variable2)
- 05. Click OnCalc
- 06. Click here & enter the below script
procedure Variable2OnCalc(var value: Variant);
var s, V : string;
begin
s := 'SELECT First 1 FromDocType FROM SL_IVDTL '+
'WHERE Dockey=' + Main.GetFieldValue('Dockey') +
' AND FROMDOCTYPE IS NOT NULL';
V := Trim(DBSQL_GetFieldValue(s));
if Trim(V) <> '' then begin
s := 'SELECT DocNo FROM SL_' + v +
' WHERE DocKey=(SELECT First 1 FromDockey FROM SL_IVDTL '+
'WHERE Dockey=' + Main.GetFieldValue('Dockey') +
' AND FROMDOCTYPE IS NOT NULL)';
Value := Trim(DBSQL_GetFieldValue(s));
end else
Value := '';
end;
- 08. Click File | Save As & enter New report Name after done
Get Whole Table
Example 1 - Get Maintain Batch Information
Below is Example doing following actions
- Get data information From Stock Batch
Last Script Update : 12 Jul 2018
Steps
- 01. Click on Calc Tab
- 02. Right click here & Select Module
- 03. Select Declarations
- 04. Select Variables
- 05. Add new/to existing as below variable
var
SQL_Batch: String;
- 06. Select Events
- 07. Select OnCreate
- 08. Copy below script & paste to here
SQL_Batch := 'SELECT Code, Description, ExpDate, MfgDate, Remark1, Remark2 FROM ST_BATCH';
DBSQL_SELECT(plSQL_Batch, SQL_Batch, 'Code');
SetDataPipelineFieldLink(Document_Detail, plSQL_Batch, 'Batch', 'Code');
- 09. Click File | Save As... to save the file (eg Sales Invoice 1)
- 10. Click File | Exit to exit the report design
- 11. Click Design again in the report designer for the file just save on Steps 9 (eg Sales Invoice 1)
- 12. Click SubRptNorm:Document_Detail tab
- 13. Click DBText icon
- 14. Click the place you wanted to show/print
- 15. Select plSQL_Batch pipeline
- 16. Type ExpDate (eg to get expired Date)
- 17. Save the report
Example 2 - Row Number Query
Below is Example Create SEQ Field base on SQL
Last Script Update : 04 May 2019
Steps
- 01. Click on Calc Tab
- 02. Right click here & Select Module
- 03. Select Declarations
- 04. Select Variables
- 05. Add new/to existing as below variable
var
SQL_1: String;
- 06. Select Events
- 07. Select OnCreate
- 08. Copy below script & paste to here
SQL_1 := 'SELECT DOCKEY, DTLKEY, SEQ, ROW_NUMBER() OVER (PARTITION BY DOCKEY ORDER BY SEQ) AS NSEQ '+
'FROM SL_IVDTL ' +
'WHERE NOT (DESCRIPTION LIKE ''R-%'' ' +
'OR DESCRIPTION LIKE ''T-%'') '+
'AND DOCKEY IN (SELECT DOCKEY FROM SL_IV '+
' WHERE DOCDATE BETWEEN ' + FormatSQLDate(Parameter.GetFieldValue('DateFrom')) +
' AND ' + FormatSQLDate(Parameter.GetFieldValue('DateTo')) +
') ORDER BY DocKey, NSeq';
DBSQL_SELECT(plSQL_1, SQL_1, 'Dockey;NSeq');
SetDataPipelineFieldLink(Document_Detail, plSQL_1, 'Dockey;Seq', 'Dockey;Seq');
- 09. Select Programs
- 10. Right Click | New Function
- 11. Copy Function Script
function FormatSQLDate(D: TDateTime): String;
var AFormat: string;
begin
AFormat := 'dd mmm yyyy'; //'dd/mmm/yyyy' if can't
Result := QuotedStr(FormatDateTime(AFormat, D));
//If yr output for TxQuery use below coding
// AFormat :='dd/mm/yyyy';
// Result := '#'+FormatDateTime(AFormat, D)+'#';
end;
- 12. Click File | Save As... to save the file (eg Sales Invoice 1)
- 13. Click File | Exit to exit the report design
- 14. Click Design again in the report designer for the file just save on Steps 12 (eg Sales Invoice 3 (SubTotal)-New)
- 15. Click SubRptNorm:Document_Detail tab
- 16. Click DBText icon
- 17. Click the place you wanted to show/print
- 18. Select plSQL_1pipeline
- 19. Type NSeq
- 20. Save the report
|
See also
- Report Designer
- Others Customisation