Report Builder - Get Data Directly from DB

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

[top]

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

[top]

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

[top]

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

[top]

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;

[top]

See also

• Report Designer
• Others Customisation