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

From eStream Software
 
(12 intermediate revisions by the same user not shown)
Line 2: Line 2:
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.
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===
===Example 1 - Get Shelf Field from Maintain Item===
Below is Example are doing following actions using '''Variable'''
Below is Example are doing following actions using '''Variable'''
Line 76: Line 77:
begin
begin
   s := 'SELECT UserName FROM AUDIT WHERE UPDATEKIND=''I'' ' +
   s := 'SELECT UserName FROM AUDIT WHERE UPDATEKIND=''I'' ' +
         'AND REFERENCE LIKE' +  
         'AND REFERENCE LIKE ' +  
         QuotedStr('%'+
         QuotedStr('%'+
         Main.GetFieldValue('DocNo')+
         Main.GetFieldValue('DocNo')+
Line 86: Line 87:
</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>
===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));
  end else
    Value := '';
end;
</syntaxhighlight>
: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>
<div style="float: right;">  [[#top|[top]]]</div>

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

RB-GetDirectDB-01.jpg
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
RB-GetDirectDB-02.jpg
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

RB-GetDirectDB-03.jpg
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;


RB-GetDirectDB-04.jpg
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)
RB-GetDirectDB-05.jpg
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');
RB-GetDirectDB-06.jpg
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

Template.Warning-01.jpg
Preview/Print/Export will delay/slow a bit if compare with build in Preview/Print/Export report

See also