Fast Report - Get Data from Available Pipeline

From eStream Software

Introduction

Sometime in the report you might wanted some extra information or further analysis from the report itself but you not able to select in the report design. So you had to self query to get the extra information or further analysis.

Example 1 - Show GST Summary at Last Page

Below is Example doing following actions

  • Get data information (Tax, Tax rate, LocalAmount, LocalTaxAmt)
  • Group the data by Tax code

Last Script Update : 18 Nov 2015

Steps

FR-GetAvailDB-01.jpg
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 Dockey, Tax, TaxRate, Sum(CAST(LocalTaxAmt AS REAL)) LocalTaxAmt, ' +
         'Sum(CAST(LocalAmount AS REAL)) LocalAmount '+
         'FROM Document_Detail ' +
         'WHERE Tax <> '''' ' +
         'GROUP BY Dockey, Tax, TaxRate';
  AddDataSet('GSTAmt', ['Tax', 'TaxRate', 'LocalTaxAmt', 'LocalAmount']) // Create New Pipeline (eg here is GSTAmt)
  .GetLocalData(SQL) // Execute the Query
  .SetDisplayFormat(['LocalTaxAmt', 'LocalAmount'],<Option."AccountingValueDisplayFormat">) // Set Display Format
  .LinkTo('Main', 'Dockey', 'Dockey'); // Link the New Pipeline (eg here is to link with Main Pipeline)
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)
FR-GetAvailDB-02.jpg
06. Click Subreport Icon
07. Click the place to print
FR-GetAvailDB-03.jpg
08. Click Insert Band icon
09. Select Master Data
FR-GetAvailDB-04.jpg
10. In the Dataset select the New Pipeline created at Step 2 (eg GSTAmt) & Click Ok
11. Repeat Steps 8 to add the Header & Footer
FR-GetAvailDB-05.jpg
12. Click on Red A icon & click the place to print
13. Select the option for following setting
  • Dataset : GSTAmt
  • DataField : LocalAmount
14. Repeat Step 12 & 13 for other but DataField change to LocalTaxAmt
15. Click Sum Icon & click at the Footer
FR-GetAvailDB-06.jpg
16. Select Aggregate value & set the option as following setting
  • Function : SUM
  • Data band : MasterData2
  • DataSet : GSTAmt
  • DataField : LocalAmount
17. Click Ok
18. Repeat Step 16 & 17 for other but DataField change to LocalTaxAmt
FR-GetAvailDB-07.jpg
19. Right Click & select DisplayFormat
FR-GetAvailDB-08.jpg
20. Set the setting for following option
  • Category : Number
  • Format : 1,234.50
21. Click OK
22. Repeat Steps 19 to 21 for other Total field (i.e. LocalTaxAmt)
23. Click on Red A icon & click the place to show tax code
24. Copy below script & paste in the memo
[IIF(Trim(<GSTAmt."TaxRate">)='',<GSTAmt."Tax">,<GSTAmt."Tax"> +' @ ' + <GSTAmt."TaxRate">)]
FR-GetAvailDB-09.jpg
25. Click OK.
26. For Label, Click on Red A icon, click the place to print & enter the Decription
27. Save the report.

Example 2 - Show Show 1st FromDocNo at Header

Below is Example will get FromDocNo Field from the Detail Data

Last Script Update : 30 May 2017

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 Dockey, Min(Seq) Seq, FromDocDate, FromDocNo FROM Document_Detail '+
         'WHERE FromDocType <> '''' ';
  AddDataSet('pl_FromDocInfo', ['Dockey', 'FromDocDate', 'FromDocNo'])
  .GetLocalData(SQL)
  .LinkTo('Main', 'Dockey', 'Dockey');
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)


Example 3 - Group Overtime in Payslip

Below is Example will get FromDocNo Field from the Detail Data

Last Script Update : 19 Sep 2018

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 Dockey, Min(Seq) Seq, FromDocDate, FromDocNo FROM Document_Detail '+
         'WHERE FromDocType <> '''' ';
  AddDataSet('pl_FromDocInfo', ['Dockey', 'FromDocDate', 'FromDocNo'])
  .GetLocalData(SQL)
  .LinkTo('Main', 'Dockey', 'Dockey');
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)
  • Coming Soon...

See also