Report Builder - Get Data Directly from DB

From eStream Software
Revision as of 04:05, 4 May 2019 by Twfaung (talk | contribs) (→‎Example 2 - Row Number Query)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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

Retrieved from "https://wiki.sql.com.my/index.php?title=Report_Builder_-_Get_Data_Directly_from_DB&oldid=9549"
Last modified
This page was last edited on 4 May 2019, at 04:05.