Fast Report - Get Data Directly from DB

From eStream Software
Revision as of 01:09, 16 November 2015 by Twfaung (talk | contribs) (→‎Steps)

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

  • Simple Query
  • Advance Query

Simple Query

Only available SQL Accounting Version 723 & above

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 : 13 Nov 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
  //Get Shelf From ST_Item
  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 : 13 Nov 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
  //Get RefCost*Qty
  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.

Advance Query

  • Coming Soon....

See also