Fast Report - Get Data Directly from DB: Difference between revisions

From eStream Software
 
(41 intermediate revisions by the same user not shown)
Line 7: Line 7:


==Cache Query==
==Cache Query==
Only available SQL Accounting Version 723 & above
Only available SQL Accounting Version 723 & above<br />


====Pros====
'''Pros'''
# Easy to write
# Easy to write
# Can direct filter data from Local Pipeline
# Can direct filter data from Local Pipeline
# Less data Loading
# Less data Loading


====Cons====
'''Cons'''
# Unable to Total the all result shown
# Unable to Total the all result shown
# Only support = in the Query
# Only support = in the Query
Line 25: Line 25:
Last Script Update : 18 Dec 2015
Last Script Update : 18 Dec 2015


====Steps====
'''Steps'''
[[File:FR-DirectDB-01.jpg|800 px|center]]
[[File:FR-DirectDB-01.jpg|800 px|center]]
:01. Click the Red A Icon.
:01. Click the Red A Icon.
Line 37: Line 37:
<syntaxhighlight lang="delphi">
<syntaxhighlight lang="delphi">
procedure Memo18OnBeforePrint(Sender: TfrxComponent);
procedure Memo18OnBeforePrint(Sender: TfrxComponent);
var V : Variant;                          
var V : Variant;
begin
begin
   V := Null;
   V := Null;
Line 66: Line 66:
</syntaxhighlight>
</syntaxhighlight>
:12. Save the report.
:12. Save the report.
<div style="float: right;">  [[#top|[top]]]</div>


===Example 2 - Get RefCost Field from Maintain Item===
===Example 2 - Get RefCost Field from Maintain Item===
Line 72: Line 74:
* Use '''RefCost * Qty''' in Sales Invoice
* Use '''RefCost * Qty''' in Sales Invoice


Last Script Update : 13 Nov 2015
Last Script Update : 18 Dec 2015


====Steps====
'''Steps'''
:01. Click the Red A Icon.
:01. Click the Red A Icon.
:02. Click on the place to be print/shown.
:02. Click on the place to be print/shown.
Line 86: Line 88:
var V : Variant;                             
var V : Variant;                             
begin
begin
  V := Null;
   //Get RefCost*Qty
   //Get RefCost*Qty
   V := CacheQuery_GetValue(pST_Item_UOM, [<Document_Detail."ItemCode">, <Document_Detail."UOM">], 'RefCost');         
   if Trim(<Document_Detail."ItemCode">) <> '' then
    V := CacheQuery_GetValue(pST_Item_UOM, [<Document_Detail."ItemCode">, <Document_Detail."UOM">], 'RefCost');         


   if not VarIsNull(V) then
   if not VarIsNull(V) then
Line 106: Line 110:
</syntaxhighlight>
</syntaxhighlight>
:12. Save the report.
:12. Save the report.
<div style="float: right;">  [[#top|[top]]]</div>
===Example 3 - Get Picture Field from Maintain Item===
Below is Example are doing following actions
* Sales Invoice to get '''Picture''' field from '''Maintain Item'''
This function only available on ''Version 730 & above''<br />
Last Script Update : 28 Mar 2016
'''Steps'''
[[File:FR-DirectDB-07.jpg|800 px|center]]
:01. Click on '''Picture''' Icon (Below Red A icon)
:02. Click on the place to be print/shown.
:03. Click on '''Event''' tab on ''Object Inspector''
:04. Double Click '''OnBeforePrint'''
:05. Enter below script
<syntaxhighlight lang="delphi">
procedure Picture1OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
begin
  Picture1.Height := 0;   
  V := Null;
  V := CacheQuery_GetValue(pST_Item, [<Document_Detail."ItemCode">], 'Picture');
  if not VarIsNull(V) then begin                             
    Picture1.LoadPictureFromBytes(V);
    Picture1.Height := 48;                                           
  end;               
end;
</syntaxhighlight>
<br />
[[File:FR-DirectDB-08.jpg|center]]
:06. Scroll up till the top of the Code
:07. Enter below script at the '''First''' line
<syntaxhighlight lang="delphi">
var pST_Item : pointer;
</syntaxhighlight>
<br />
[[File:FR-DirectDB-09.jpg|800 px|center]]
:08. Scroll down till the end of the Code
:09. Enter below script in between '''begin''' and '''end.'''
<syntaxhighlight lang="delphi">
  pST_Item := CacheQuery_New('SELECT Picture FROM ST_ITEM WHERE Code=:Code');
</syntaxhighlight>
:10. Save the report.
<div style="float: right;">  [[#top|[top]]]</div>
===Example 4 - Get Document Created UserName from Audit===
Below is Example is to Get the who created the Document from Audit Table.
Last Script Update : 14 Jan 2019
'''Steps'''
:01. Click the Red A Icon.
:02. Click on the place to be print/shown.
:03. Right Click the Memo.
:04. Select '''Stretch'''
:05. Click on '''Events''' tab
:06. Double Click '''OnBeforePrint'''
:07. Enter below script
<syntaxhighlight lang="delphi">
procedure Memo10OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
    p : Pointer;
    s : String;                             
begin
  V := Null;
  if Trim(<Main."DocNo">) <> '' then begin
    //For AR, AP, SL & PH Only
    s := '%' + <Main."DocNo"> + '%Code: ' + <Main."Code"> + ',%';                                     
    //For JV & CB Only
    //s := '%' + <Main."DocNo"> + ',%';                                     
    V := CacheQuery_GetValue(p_Audit, [s], 'Code'); //for User Code
    //V := CacheQuery_GetValue(p_Audit, [s], 'Name'); //for User Name
  end;
  if not VarIsNull(V) then
    Memo10.Text := V else
    Memo10.Text := '';
end;
</syntaxhighlight>
:08. Scroll up till the top of the Code
:09. Enter below script at the '''First''' line
<syntaxhighlight lang="delphi">
var p_Audit : pointer;
</syntaxhighlight>
:10. Scroll down till the end of the Code
:11. Enter below script in between '''begin''' and '''end.'''
<syntaxhighlight lang="delphi">
  p_Audit := CacheQuery_New('SELECT CODE, NAME FROM SY_USER WHERE CODE = (SELECT First 1 UserName FROM AUDIT WHERE UPDATEKIND=''I'' AND REFERENCE LIKE :DocNo) ');
</syntaxhighlight>
:12. Save the report.
<div style="float: right;">  [[#top|[top]]]</div>
===Example 5 - Get Transfer Information - QT to DO to IV===
Below is Example are doing following action
* Quotation Transfer to Delivery Order to Invoice.
* Get the Quotation number & Date in the Invoice Detail
Last Script Update : 29 Aug 2017
'''Steps'''
:01. Click the Red A Icon.
:02. Click on the place to be print/shown (In DetailData).
:03. Right Click the Memo.
:04. Select '''Stretch'''
:05. Click on '''Events''' tab
:06. Double Click '''OnBeforePrint'''
:07. Enter below script (For DocNo)
<syntaxhighlight lang="delphi">
procedure Memo10OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
begin
  V := Null;
  if Trim(<Document_Detail."FromDocType">) <> '' then
    V := CacheQuery_GetValue(pSL_QT, [<Document_Detail."FromDtlKey">], 'DocNo');
  if not VarIsNull(V) then
    Memo10.Text := V else
    Memo10.Text := '';
end;
</syntaxhighlight>
:08. Repeat again Steps 01 to 06
:09. Enter below script (For DocDate)
<syntaxhighlight lang="delphi">
procedure Memo18OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
begin
  V := Null;
  if Trim(<Document_Detail."FromDocType">) <> '' then                                     
    V := CacheQuery_GetValue(pSL_QT, [<Document_Detail."FromDtlKey">], 'DocDate');
  if not VarIsNull(V) then
    Memo18.Text := V else
    Memo18.Text := '';
end;
</syntaxhighlight>
:10. Scroll up till the top of the Code
:11. Enter below script at the '''First''' line
<syntaxhighlight lang="delphi">
var pSL_QT : pointer;
</syntaxhighlight>
:12. Scroll down till the end of the Code
:13. Enter below script in between '''begin''' and '''end.'''
<syntaxhighlight lang="delphi">
    pSL_QT := CacheQuery_New('SELECT A.DocNo, A.DocDate, B.Qty FROM SL_QT A '+
                          'INNER JOIN SL_QTDTL B ON (A.Dockey=B.Dockey) ' +
                          'WHERE B.Dockey=(SELECT FROMDOCKEY FROM SL_DODTL ' +
                          'WHERE Dtlkey=:Dtlkey) '+
                          'AND B.DtlKey=(SELECT FROMDTLKEY FROM SL_DODTL '+
                          'WHERE Dtlkey=:DtlKey)');
</syntaxhighlight>
:14. Save the report.
<div style="float: right;">  [[#top|[top]]]</div>
===Example 6 - Get Customer Branch Email & Attention===
Below is Example are doing following actions
* Sales Invoice to get Branch '''Email''' field from '''Maintain Customer'''
Last Script Update : 14 Sep 2021
'''Steps'''
:01. Click the Red A Icon.
:02. Click on the place to be print/shown.
:03. Right Click the Memo.
:04. Select '''Stretch'''
:05. Click on '''Events''' tab
:06. Double Click '''OnBeforePrint'''
:07. Enter below script
<syntaxhighlight lang="delphi">
procedure Memo18OnBeforePrint(Sender: TfrxComponent);
var V : Variant;                           
begin
  V := Null;
  //For Email
  V := CacheQuery_GetValue(pAR_Branch, [<Main."Code">, <Main."BranchName">], 'EMail');
  //For Attention
  //V := CacheQuery_GetValue(pAR_Branch, [<Main."Code">, <Main."BranchName">], 'Attention');
  if VarIsNull(V) then
    V := '';
  Memo18.Text := V;
end;
</syntaxhighlight>
:08. Scroll up till the top of the Code
:09. Enter below script at the '''First''' line
<syntaxhighlight lang="delphi">
var pAR_Branch : pointer;
</syntaxhighlight>
:10. Scroll down till the end of the Code
:11. Enter below script in between '''begin''' and '''end.'''
<syntaxhighlight lang="delphi">
  pAR_Branch := CacheQuery_New('SELECT EMail, Attention FROM AR_CustomerBranch WHERE Code=:Code AND BranchName=:BranchName');
</syntaxhighlight>
:12. Save the report.
<div style="float: right;">  [[#top|[top]]]</div>


==Get DB Data Query==
==Get DB Data Query==
Line 111: Line 317:
The Steps is 99% same like [[Fast_Report_-_Get_Data_from_Available_Pipeline]] the only different is the Script part.
The Steps is 99% same like [[Fast_Report_-_Get_Data_from_Available_Pipeline]] the only different is the Script part.


====Pros====
'''Pros'''
# Can write complex query
# Can write complex query


====Cons====
'''Cons'''
# Not Easy to write
# Not Easy to write
# Unable to filter data from Local Pipeline (i.e. Had to Select ALL data from the Table)
# Unable to filter data from Local Pipeline (i.e. Had to Select ALL data from the Table)
# Might slow Loading report if not careful
# Might slow or Out of Memory on Print/Preview report if not careful


===Example 1 - Get Maintain Batch Information===
===Example 1 - Get Maintain Batch Information===
Line 125: Line 331:
Last Script Update : 07 Dec 2015
Last Script Update : 07 Dec 2015


====Steps====
Steps
[[File:FR-DirectDB-05.jpg|800 px|center]]
[[File:FR-DirectDB-05.jpg|800 px|center]]
:01. Click '''Code''' tab & scroll down look for '''procedure SetUp'''
:01. Click '''Code''' tab & scroll down look for '''procedure SetUp'''
Line 144: Line 350:
::* DataField : ExpDate
::* DataField : ExpDate
:08. Repeat '''Steps 6''' to '''7''' for other field if necessary
:08. Repeat '''Steps 6''' to '''7''' for other field if necessary
:09. Save the Report
<div style="float: right;">  [[#top|[top]]]</div>
===Example 2 - Get Supplier Bank Information===
Below is Example doing following actions
* Get Supplier Bank information From Maintain Supplier for Supplier Payment Voucher
Last Script Update : 24 Dec 2019
Steps
[[File:FR-DirectDB-10.jpg|800 px|center]]
:01. Click '''Code''' tab & scroll down look for '''procedure SetUp'''
:02. Copy below script & paste it above the '''procedure SetUp'''
{| class="mw-collapsible mw-collapsed wikitable"
! Supplier Bank Info. 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));
end;
function GetBankName(const lCode:String):String;
begin
  case lCode of
  'AIBBMY' : Result := 'Affin Bank Berhad';
  'RJHIMY' : Result := 'Al Rajhi Banking & Investment Corporation (Malaysia) Berhad';
  'MFBBMY' : Result := 'Alliance Bank Malaysia Berhad';
  'ARBKMY' : Result := 'AmBank (M) Berhad';
  'BNPAMY' : Result := 'BNP Paribas Malaysia Berhad';
  'BIMBMY' : Result := 'Bank Islam Malaysia Berhad';
  'BKRMMY' : Result := 'Bank Kerjasama Rakyat Malaysia Berhad';
  'BMMBMY' : Result := 'Bank Muamalat Malaysia Berhad';
  'BOFAMY' : Result := 'Bank of America Malaysia Berhad';
  'BOTKMY' : Result := 'Bank of Tokyo-Mitsubishi UFJ (Malaysia) Berhad';
  'AGOBMY' : Result := 'Bank Pertanian Malaysia Berhad';
  'BSNAMY' : Result := 'Bank Simpanan Nasional Berhad';
  'CIBBMY' : Result := 'CIMB Bank Berhad';
  'CITIMY' : Result := 'Citibank Berhad';
  'DEUTMY' : Result := 'Deutsche Bank (Malaysia) Berhad';
  'HLBBMY' : Result := 'Hong Leong Bank Berhad';
  'HBMBMY' : Result := 'HSBC Bank Malaysia Berhad';
  'ICBKMY' : Result := 'Industrial and Commercial Bank of China (Malaysia) Berhad';
  'CHASMY' : Result := 'J.P. Morgan Chase Bank Berhad';
  'KFHOMY' : Result := 'Kuwait Finance House (Malaysia) Berhad';
  'MBBEMY' : Result := 'Malayan Banking Berhad';
  'MHCBMY' : Result := 'Mizuho Bank (Malaysia) Berhad';
  'OCBCMY' : Result := 'OCBC Bank (Malaysia) Berhad';
  'PBBEMY' : Result := 'Public Bank Berhad';
  'RHBBMY' : Result := 'RHB Bank Berhad';
  'SCBLMY' : Result := 'Standard Chartered Bank Malaysia Berhad';
  'SMBCMY' : Result := 'Sumitomo Mitsui Banking Corporation Malaysia Berhad';
  'ABNAMY' : Result := 'The Royal Bank of Scotland Berhad';
  'UOVBMY' : Result := 'United Overseas Bank (Malaysia) Bhd';
  'ANZBSG' : Result := 'Australia and New Zealand Banking Group Limited';
  'BKKBSG' : Result := 'Bangkok Bank Public Company Limited';
  'BOFASG' : Result := 'Bank of America, National Association';
  'BKCHSG' : Result := 'Bank of China Limited';
  'BEASSG' : Result := 'The Bank of East Asia Limited';
  'BNINSG' : Result := 'P.T. Bank Negara Indonesia (Persero)';
  'BKIDSG' : Result := 'Bank of India';
  'BOTKSG' : Result := 'Bank Of Tokyo-Mitsubishi UFJ Limited';
  'BNPASG' : Result := 'BNP Paribas';
  'CTCBSG' : Result := 'Chinatrust Commercial Bank Corporation Limited';
  'CIBBSG' : Result := 'CIMB Bank Berhad';
  'CITISG' : Result := 'Citibank, National Association';
  'COBASG' : Result := 'Commerzbank AG';
  'AGRISG' : Result := 'Credit Agricole Corporate And Investment Bank';
  'DBSSSG' : Result := 'DBS Bank Limited';
  'DEUTSG' : Result := 'Deutsche Bank AG';
  'DNBASG' : Result := 'DNB Bank ASA';
  'FAEASG' : Result := 'Far Eastern Bank Limited';
  'FCBKSG' : Result := 'First Commercial Bank Limited';
  'HLBBSG' : Result := 'HL Bank';
  'HSBCSG' : Result := 'The Hongkong And Shanghai Banking Corporation Limited';
  'ICICSG' : Result := 'ICICI Bank Limited';
  'IDIBSG' : Result := 'Indian Bank';
  'IOBASG' : Result := 'Indian Overseas Bank';
  'iCBKSG' : Result := 'Industrial and Commercial Bank Of China';
  'BCITSG' : Result := 'Intesa Sanpaolo SpA';
  'CHASSG' : Result := 'JP Morgan Chase Bank, National Association';
  'KOEXSG' : Result := 'Korea Exchange Bank';
  'SOLASG' : Result := 'Landesbank Baden-Wurttemberg';
  'MBBESG' : Result := 'Malayan Banking Bhd';
  'MHCBSG' : Result := 'Mizuho Bank Limited';
  'NATASG' : Result := 'National Australia Bank Limited';
  'NDPBSG' : Result := 'Nordea Bank Finland PLC';
  'OCBCSG' : Result := 'Oversea-Chinese Banking Corporation Limited';
  'RHBBSG' : Result := 'RHB Bank Berhad';
  'ESSESG' : Result := 'Skandinaviska Enskilda Banken AB';
  'SOGESG' : Result := 'Societe Generale';
  'SBSASG' : Result := 'Standard Chartered Bank';
  'SBINSG' : Result := 'State Bank of India';
  'SMBCSG' : Result := 'Sumitomo Mitsui Banking Corporation';
  'HANDSG' : Result := 'Svenska Handelsbanken AB';
  'RBOSSG' : Result := 'The Royal Bank of Scotland PLC';
  'UBSWSG' : Result := 'UBS AG';
  'UCBASG' : Result := 'UCO Bank';
  'UOVBSG' : Result := 'United Overseas Bank Limited';
  end;
end;
procedure SetUp_BankInfo;
var s : string;
begin
  s := 'SELECT DocNo, BankAcc FROM AP_SP ';
  if <Parameter."SelectDate"> = True then
    s := Format(s, ['PostDate', FormatSQLDate(<Parameter."DateFrom">),
                                FormatSQLDate(<Parameter."DateTo">)]) else
    s := Format(s, ['DocDate', FormatSQLDate(<Parameter."DocDateFrom">),
                              FormatSQLDate(<Parameter."DocDateTo">)]);
  AddDataSet('pl_APPM', ['DocNo', 'BankAcc'])
  .GetDBData(s)
  .LinkTo('Main', 'DocNo', 'DocNo');
  s := 'SELECT * FROM AP_SUPPLIERBANKACC '+
        'WHERE IsActive=''T'' ';
  AddDataSet('pl_SUPPLIERBANKACC', ['Bank', 'AccNo', 'AccName'])
  .GetDBData(s)
  .LinkTo('pl_APPM', 'BankAcc', 'AutoKey');
end;
</syntaxhighlight>
|}
:03. Copy below script & paste it between the '''begin''' & '''end;''' in '''procedure SetUp'''
<syntaxhighlight lang="delphi">
  SetUp_BankInfo;
</syntaxhighlight>
:04. Click ''File | Save As...'' to save the file (eg GL Payment Voucher - Detail - Full (GST)-v BankInfo)
:05. Click '''File | Exit''' to exit the report design
:06. Click Design again in the report designer for the file just save on Steps 4 (eg GL Payment Voucher - Detail - Full (GST)-v BankInfo)
[[File:FR-DirectDB-11.jpg|center]]
:07. Click on Red A icon & click the place to print
:08. Select the option for following setting
::* Dataset : pl_SUPPLIERBANKACC
::* DataField : AccNo
:09. Repeat '''Step 7'''
[[File:FR-DirectDB-12.jpg|center]]
:10. Copy below script & paste it in the Memo
<syntaxhighlight lang="delphi">
  [GetBankName(<pl_SUPPLIERBANKACC."Bank">)]
</syntaxhighlight>
:11. Click OK
:12. Save the report
<div style="float: right;">  [[#top|[top]]]</div>


==See also==
==See also==
* [[Report Designer]]
* [[Report Designer]]
* Others [[Customisation]]
* Others [[Customisation]]

Latest revision as of 03:55, 14 September 2021

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.

There are 2 ways to Get the data directly from Database

  • Cache Query
  • Get DB Data Query

Cache Query

Only available SQL Accounting Version 723 & above

Pros

  1. Easy to write
  2. Can direct filter data from Local Pipeline
  3. Less data Loading

Cons

  1. Unable to Total the all result shown
  2. Only support = in the Query
  3. No pipeline is created

Example 1 - Get Shelf Field from Maintain Item

Below is Example are doing following actions

  • At Sales Invoice to get Shelf field from Maintain Item

Last Script Update : 18 Dec 2015

Steps

FR-DirectDB-01.jpg
01. Click the Red A Icon.
02. Click on the place to be print/shown.
03. Right Click the Memo.
FR-DirectDB-04.jpg
04. Select Stretch
05. Click on Events tab
06. Double Click OnBeforePrint
07. Enter below script
procedure Memo18OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
begin
  V := Null;
  //Get Shelf From ST_Item
  if Trim(<Document_Detail."ItemCode">) <> '' then
    V := CacheQuery_GetValue(pST_Item, [<Document_Detail."ItemCode">], 'Shelf');
    
  if not VarIsNull(V) then
    Memo18.Text := V else
    Memo18.Text := '';
end;


FR-DirectDB-02.jpg
08. Scroll up till the top of the Code
09. Enter below script at the First line
var pST_Item : pointer;


FR-DirectDB-03.jpg
10. Scroll down till the end of the Code
11. Enter below script in between begin and end.
  pST_Item := CacheQuery_New('SELECT Shelf FROM ST_ITEM WHERE Code=:Code');
12. Save the report.

Example 2 - Get RefCost Field from Maintain Item

Below is Example are doing following actions

  • Sales Invoice to get RefCost field from Maintain Item
  • Use RefCost * Qty in Sales Invoice

Last Script Update : 18 Dec 2015

Steps

01. Click the Red A Icon.
02. Click on the place to be print/shown.
03. Right Click the Memo.
04. Select Stretch
05. Click on Events tab
06. Double Click OnBeforePrint
07. Enter below script
procedure Memo18OnBeforePrint(Sender: TfrxComponent);
var V : Variant;                            
begin
  V := Null;
  //Get RefCost*Qty
  if Trim(<Document_Detail."ItemCode">) <> '' then
    V := CacheQuery_GetValue(pST_Item_UOM, [<Document_Detail."ItemCode">, <Document_Detail."UOM">], 'RefCost');        

  if not VarIsNull(V) then
    V := V * <Document_Detail."Qty"> else
    V := 0;
  Memo18.Text := FormatFloat(<Option."AccountingValueDisplayFormat">, V);
end;
08. Scroll up till the top of the Code
09. Enter below script at the First line
var pST_Item_UOM : pointer;
10. Scroll down till the end of the Code
11. Enter below script in between begin and end.
  pST_Item_UOM := CacheQuery_New('SELECT RefCost FROM ST_ITEM_UOM WHERE Code=:Code AND UOM=:UOM');
12. Save the report.

Example 3 - Get Picture Field from Maintain Item

Below is Example are doing following actions

  • Sales Invoice to get Picture field from Maintain Item

This function only available on Version 730 & above

Last Script Update : 28 Mar 2016

Steps

FR-DirectDB-07.jpg
01. Click on Picture Icon (Below Red A icon)
02. Click on the place to be print/shown.
03. Click on Event tab on Object Inspector
04. Double Click OnBeforePrint
05. Enter below script
procedure Picture1OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
begin
  Picture1.Height := 0;    
  V := Null;
  V := CacheQuery_GetValue(pST_Item, [<Document_Detail."ItemCode">], 'Picture');
  if not VarIsNull(V) then begin                              
    Picture1.LoadPictureFromBytes(V);
    Picture1.Height := 48;                                            
  end;                
end;


FR-DirectDB-08.jpg
06. Scroll up till the top of the Code
07. Enter below script at the First line
var pST_Item : pointer;


FR-DirectDB-09.jpg
08. Scroll down till the end of the Code
09. Enter below script in between begin and end.
  pST_Item := CacheQuery_New('SELECT Picture FROM ST_ITEM WHERE Code=:Code');
10. Save the report.

Example 4 - Get Document Created UserName from Audit

Below is Example is to Get the who created the Document from Audit Table.

Last Script Update : 14 Jan 2019

Steps

01. Click the Red A Icon.
02. Click on the place to be print/shown.
03. Right Click the Memo.
04. Select Stretch
05. Click on Events tab
06. Double Click OnBeforePrint
07. Enter below script
procedure Memo10OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
    p : Pointer;
    s : String;                              
begin
  V := Null;
  if Trim(<Main."DocNo">) <> '' then begin
    //For AR, AP, SL & PH Only
    s := '%' + <Main."DocNo"> + '%Code: ' + <Main."Code"> + ',%';                                      
    //For JV & CB Only
    //s := '%' + <Main."DocNo"> + ',%';                                      
    V := CacheQuery_GetValue(p_Audit, [s], 'Code'); //for User Code
    //V := CacheQuery_GetValue(p_Audit, [s], 'Name'); //for User Name
  end;

  if not VarIsNull(V) then
    Memo10.Text := V else
    Memo10.Text := '';
end;
08. Scroll up till the top of the Code
09. Enter below script at the First line
var p_Audit : pointer;
10. Scroll down till the end of the Code
11. Enter below script in between begin and end.
  p_Audit := CacheQuery_New('SELECT CODE, NAME FROM SY_USER WHERE CODE = (SELECT First 1 UserName FROM AUDIT WHERE UPDATEKIND=''I'' AND REFERENCE LIKE :DocNo) ');
12. Save the report.

Example 5 - Get Transfer Information - QT to DO to IV

Below is Example are doing following action

  • Quotation Transfer to Delivery Order to Invoice.
  • Get the Quotation number & Date in the Invoice Detail

Last Script Update : 29 Aug 2017

Steps

01. Click the Red A Icon.
02. Click on the place to be print/shown (In DetailData).
03. Right Click the Memo.
04. Select Stretch
05. Click on Events tab
06. Double Click OnBeforePrint
07. Enter below script (For DocNo)
procedure Memo10OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
begin
  V := Null;
  if Trim(<Document_Detail."FromDocType">) <> '' then
    V := CacheQuery_GetValue(pSL_QT, [<Document_Detail."FromDtlKey">], 'DocNo');

  if not VarIsNull(V) then
    Memo10.Text := V else
    Memo10.Text := '';
end;
08. Repeat again Steps 01 to 06
09. Enter below script (For DocDate)
procedure Memo18OnBeforePrint(Sender: TfrxComponent);
var V : Variant;
begin
  V := Null;
  if Trim(<Document_Detail."FromDocType">) <> '' then                                      
    V := CacheQuery_GetValue(pSL_QT, [<Document_Detail."FromDtlKey">], 'DocDate');

  if not VarIsNull(V) then
    Memo18.Text := V else
    Memo18.Text := '';
end;
10. Scroll up till the top of the Code
11. Enter below script at the First line
var pSL_QT : pointer;
12. Scroll down till the end of the Code
13. Enter below script in between begin and end.
    pSL_QT := CacheQuery_New('SELECT A.DocNo, A.DocDate, B.Qty FROM SL_QT A '+
                           'INNER JOIN SL_QTDTL B ON (A.Dockey=B.Dockey) ' +
                           'WHERE B.Dockey=(SELECT FROMDOCKEY FROM SL_DODTL ' +
                           'WHERE Dtlkey=:Dtlkey) '+
                           'AND B.DtlKey=(SELECT FROMDTLKEY FROM SL_DODTL '+
                           'WHERE Dtlkey=:DtlKey)');
14. Save the report.

Example 6 - Get Customer Branch Email & Attention

Below is Example are doing following actions

  • Sales Invoice to get Branch Email field from Maintain Customer

Last Script Update : 14 Sep 2021

Steps

01. Click the Red A Icon.
02. Click on the place to be print/shown.
03. Right Click the Memo.
04. Select Stretch
05. Click on Events tab
06. Double Click OnBeforePrint
07. Enter below script
procedure Memo18OnBeforePrint(Sender: TfrxComponent);
var V : Variant;                            
begin
  V := Null;
  //For Email
  V := CacheQuery_GetValue(pAR_Branch, [<Main."Code">, <Main."BranchName">], 'EMail');
  //For Attention
  //V := CacheQuery_GetValue(pAR_Branch, [<Main."Code">, <Main."BranchName">], 'Attention');

  if VarIsNull(V) then
    V := '';
  Memo18.Text := V;
end;
08. Scroll up till the top of the Code
09. Enter below script at the First line
var pAR_Branch : pointer;
10. Scroll down till the end of the Code
11. Enter below script in between begin and end.
  pAR_Branch := CacheQuery_New('SELECT EMail, Attention FROM AR_CustomerBranch WHERE Code=:Code AND BranchName=:BranchName');
12. Save the report.

Get DB Data Query

User can use this function to query & add new pipeline & also join/link the new pipeline to the existing/local pipeline.
The Steps is 99% same like Fast_Report_-_Get_Data_from_Available_Pipeline the only different is the Script part.

Pros

  1. Can write complex query

Cons

  1. Not Easy to write
  2. Unable to filter data from Local Pipeline (i.e. Had to Select ALL data from the Table)
  3. Might slow or Out of Memory on Print/Preview report if not careful

Example 1 - Get Maintain Batch Information

Below is Example doing following actions

  • Get data information From Stock Batch

Last Script Update : 07 Dec 2015

Steps

FR-DirectDB-05.jpg
01. Click Code tab & scroll down look for procedure SetUp
02. Copy below script & paste it between the begin & end; in procedure SetUp
  SQL := 'SELECT Code, Description, ExpDate, MfgDate, Remark1, Remark2 FROM ST_BATCH';
  AddDataSet('plST_Batch',['Code', 'Description', 'ExpDate', 'MfgDate', 'Remark1', 'Remark2'])
  .GetDBData(SQL)
  .LinkTo('Document_Detail', 'Batch', 'Code'); // Link to Detail
03. Click File | Save As... to save the file (eg Sales Invoice 7 (GST 2)-New)
04. Click File | Exit to exit the report design
05. Click Design again in the report designer for the file just save on Steps 3 (eg Sales Invoice 7 (GST 2)-New)
FR-DirectDB-06.jpg
06. Click on Red A icon & click the place to print
07. Select the option for following setting
  • Dataset : plST_Batch
  • DataField : ExpDate
08. Repeat Steps 6 to 7 for other field if necessary
09. Save the Report

Example 2 - Get Supplier Bank Information

Below is Example doing following actions

  • Get Supplier Bank information From Maintain Supplier for Supplier Payment Voucher

Last Script Update : 24 Dec 2019

Steps

FR-DirectDB-10.jpg
01. Click Code tab & scroll down look for procedure SetUp
02. Copy below script & paste it above the procedure SetUp
Supplier Bank Info. 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));
end;

function GetBankName(const lCode:String):String;
begin
  case lCode of
   'AIBBMY' : Result := 'Affin Bank Berhad';
   'RJHIMY' : Result := 'Al Rajhi Banking & Investment Corporation (Malaysia) Berhad';
   'MFBBMY' : Result := 'Alliance Bank Malaysia Berhad';
   'ARBKMY' : Result := 'AmBank (M) Berhad';
   'BNPAMY' : Result := 'BNP Paribas Malaysia Berhad';
   'BIMBMY' : Result := 'Bank Islam Malaysia Berhad';
   'BKRMMY' : Result := 'Bank Kerjasama Rakyat Malaysia Berhad';
   'BMMBMY' : Result := 'Bank Muamalat Malaysia Berhad';
   'BOFAMY' : Result := 'Bank of America Malaysia Berhad';
   'BOTKMY' : Result := 'Bank of Tokyo-Mitsubishi UFJ (Malaysia) Berhad';
   'AGOBMY' : Result := 'Bank Pertanian Malaysia Berhad';
   'BSNAMY' : Result := 'Bank Simpanan Nasional Berhad';
   'CIBBMY' : Result := 'CIMB Bank Berhad';
   'CITIMY' : Result := 'Citibank Berhad';
   'DEUTMY' : Result := 'Deutsche Bank (Malaysia) Berhad';
   'HLBBMY' : Result := 'Hong Leong Bank Berhad';
   'HBMBMY' : Result := 'HSBC Bank Malaysia Berhad';
   'ICBKMY' : Result := 'Industrial and Commercial Bank of China (Malaysia) Berhad';
   'CHASMY' : Result := 'J.P. Morgan Chase Bank Berhad';
   'KFHOMY' : Result := 'Kuwait Finance House (Malaysia) Berhad';
   'MBBEMY' : Result := 'Malayan Banking Berhad';
   'MHCBMY' : Result := 'Mizuho Bank (Malaysia) Berhad';
   'OCBCMY' : Result := 'OCBC Bank (Malaysia) Berhad';
   'PBBEMY' : Result := 'Public Bank Berhad';
   'RHBBMY' : Result := 'RHB Bank Berhad';
   'SCBLMY' : Result := 'Standard Chartered Bank Malaysia Berhad';
   'SMBCMY' : Result := 'Sumitomo Mitsui Banking Corporation Malaysia Berhad';
   'ABNAMY' : Result := 'The Royal Bank of Scotland Berhad';
   'UOVBMY' : Result := 'United Overseas Bank (Malaysia) Bhd';
   'ANZBSG' : Result := 'Australia and New Zealand Banking Group Limited';
   'BKKBSG' : Result := 'Bangkok Bank Public Company Limited';
   'BOFASG' : Result := 'Bank of America, National Association';
   'BKCHSG' : Result := 'Bank of China Limited';
   'BEASSG' : Result := 'The Bank of East Asia Limited';
   'BNINSG' : Result := 'P.T. Bank Negara Indonesia (Persero)';
   'BKIDSG' : Result := 'Bank of India';
   'BOTKSG' : Result := 'Bank Of Tokyo-Mitsubishi UFJ Limited';
   'BNPASG' : Result := 'BNP Paribas';
   'CTCBSG' : Result := 'Chinatrust Commercial Bank Corporation Limited';
   'CIBBSG' : Result := 'CIMB Bank Berhad';
   'CITISG' : Result := 'Citibank, National Association';
   'COBASG' : Result := 'Commerzbank AG';
   'AGRISG' : Result := 'Credit Agricole Corporate And Investment Bank';
   'DBSSSG' : Result := 'DBS Bank Limited';
   'DEUTSG' : Result := 'Deutsche Bank AG';
   'DNBASG' : Result := 'DNB Bank ASA';
   'FAEASG' : Result := 'Far Eastern Bank Limited';
   'FCBKSG' : Result := 'First Commercial Bank Limited';
   'HLBBSG' : Result := 'HL Bank';
   'HSBCSG' : Result := 'The Hongkong And Shanghai Banking Corporation Limited';
   'ICICSG' : Result := 'ICICI Bank Limited';
   'IDIBSG' : Result := 'Indian Bank';
   'IOBASG' : Result := 'Indian Overseas Bank';
   'iCBKSG' : Result := 'Industrial and Commercial Bank Of China';
   'BCITSG' : Result := 'Intesa Sanpaolo SpA';
   'CHASSG' : Result := 'JP Morgan Chase Bank, National Association';
   'KOEXSG' : Result := 'Korea Exchange Bank';
   'SOLASG' : Result := 'Landesbank Baden-Wurttemberg';
   'MBBESG' : Result := 'Malayan Banking Bhd';
   'MHCBSG' : Result := 'Mizuho Bank Limited';
   'NATASG' : Result := 'National Australia Bank Limited';
   'NDPBSG' : Result := 'Nordea Bank Finland PLC';
   'OCBCSG' : Result := 'Oversea-Chinese Banking Corporation Limited';
   'RHBBSG' : Result := 'RHB Bank Berhad';
   'ESSESG' : Result := 'Skandinaviska Enskilda Banken AB';
   'SOGESG' : Result := 'Societe Generale';
   'SBSASG' : Result := 'Standard Chartered Bank';
   'SBINSG' : Result := 'State Bank of India';
   'SMBCSG' : Result := 'Sumitomo Mitsui Banking Corporation';
   'HANDSG' : Result := 'Svenska Handelsbanken AB';
   'RBOSSG' : Result := 'The Royal Bank of Scotland PLC';
   'UBSWSG' : Result := 'UBS AG';
   'UCBASG' : Result := 'UCO Bank';
   'UOVBSG' : Result := 'United Overseas Bank Limited';
  end;
end;

procedure SetUp_BankInfo;
var s : string;
begin
  s := 'SELECT DocNo, BankAcc FROM AP_SP ';

  if <Parameter."SelectDate"> = True then
    s := Format(s, ['PostDate', FormatSQLDate(<Parameter."DateFrom">),
                                FormatSQLDate(<Parameter."DateTo">)]) else
    s := Format(s, ['DocDate', FormatSQLDate(<Parameter."DocDateFrom">),
                               FormatSQLDate(<Parameter."DocDateTo">)]);
  AddDataSet('pl_APPM', ['DocNo', 'BankAcc'])
  .GetDBData(s)
  .LinkTo('Main', 'DocNo', 'DocNo');

  s := 'SELECT * FROM AP_SUPPLIERBANKACC '+
        'WHERE IsActive=''T'' ';
  AddDataSet('pl_SUPPLIERBANKACC', ['Bank', 'AccNo', 'AccName'])
  .GetDBData(s)
  .LinkTo('pl_APPM', 'BankAcc', 'AutoKey');
end;
03. Copy below script & paste it between the begin & end; in procedure SetUp
  SetUp_BankInfo;
04. Click File | Save As... to save the file (eg GL Payment Voucher - Detail - Full (GST)-v BankInfo)
05. Click File | Exit to exit the report design
06. Click Design again in the report designer for the file just save on Steps 4 (eg GL Payment Voucher - Detail - Full (GST)-v BankInfo)
FR-DirectDB-11.jpg
07. Click on Red A icon & click the place to print
08. Select the option for following setting
  • Dataset : pl_SUPPLIERBANKACC
  • DataField : AccNo
09. Repeat Step 7
FR-DirectDB-12.jpg
10. Copy below script & paste it in the Memo
  [GetBankName(<pl_SUPPLIERBANKACC."Bank">)]
11. Click OK
12. Save the report

See also