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
- Easy to write
- Can direct filter data from Local Pipeline
- Less data Loading
Cons
- Unable to Total the all result shown
- Only support = in the Query
- 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
- 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 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;
- 08. Scroll up till the top of the Code
- 09. Enter below script at the First line
var pST_Item : pointer;
- 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.
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
- Can write complex query
Cons
- Not Easy to write
- Unable to filter data from Local Pipeline (i.e. Had to Select ALL data from the Table)
- Might slow Loading 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
- 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)
- 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
See also
- Report Designer
- Others Customisation