Report Builder - 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 : 03 Feb 2016

Steps

RB-GetAvailDB-01.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_6: String;


RB-GetAvailDB-02.jpg
06. Select Events
07. Select OnCreate
08. Copy below script & paste to here
  SQL_6 := 'SELECT DocKey,  Tax, TaxRate, Sum(LocalAmount) LocalAmount, Sum(LocalTaxAmt) localTaxAmt, '+ 
	   'Description  FROM Document_Detail ' +
	   'Where Tax <> ''''' +
	   'GROUP BY Dockey, Tax, TaxRate';


RB-GetAvailDB-03.jpg
09. Select Event Handlers
10. Select procedure ReportBeforePrint
11. Copy below script & paste to here
   LocalSQL_SELECT(plSQL_6, SQL_6, 'Dockey;Tax'); //Create New pipeline
   SetDataPipelineFieldLink(Main, plSQL_6, 'DocKey', 'DocKey'); //Link with Main


12. Click File | Save As... to save the file (eg 0Sales Cash Sales 3 (GST 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 0Sales Cash Sales 3 (GST 1))
RB-GetAvailDB-04.jpg
15. Click Subreport icon
16. Click the place you wanted to show/print
RB-GetAvailDB-05.jpg
17. Right click the Sub report
18. Untick the ParentWitdh & manual adjust the sub report width to the width you wanted
19. Select the Pipeline just create (eg plSQL_6)
20. Scroll till end
21. Click SubReport2:plSQL_6 tab
RB-GetAvailDB-06.jpg
22. Click Report & set the following setting
  • Title - Select
  • Summary - Select
  • Header - UnSelect
  • Footer - UnSelect
RB-GetAvailDB-07.jpg
23. Click DBText icon
24. Click the place you wanted to show/print (in between Title & Detail Band)
25. Select LocalAmount field
26. Repeat Step 23 to 25 for LocalTaxAmt field
27. Click DBCalc icon
28. Click the place you wanted to show/print (in between Detail & Summary Band)
29. Select LocalAmount field (See Step 25)
30. Repeat Step 27 to 29 for LocalTaxAmt field
31. Click Variable icon
32. Click the place you wanted to show/print (in between Title & Detail Band)
33. Right Click the variable
RB-GetAvailDB-08.jpg
34. Copy below script & paste to here
  if Trim(plsql_6.getfieldvalue('TaxRate')) <> '' then
    Value := plsql_6.getfieldvalue('Tax') + ' @ ' + plsql_6.getfieldvalue('TaxRate') else
    Value := plsql_6.getfieldvalue('Tax');
35. Click Ok
36. For label can Click Label icon
37. Save the report after done

Example 2 - Show FromDocNo at Header

Below is Example is get From Doc No from record 1 at Document Detail

Last Script Update : 05 Jun 2020

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_6: String;
06. Select Events
07. Select OnCreate
08. Copy below script & paste to here
  SQL_6 := 'SELECT DocKey,  FromDocNo '+ 
	       ' FROM Document_Detail ' +
	       'Where Seq = 1 ';
09. Select Event Handlers
10. Select procedure ReportBeforePrint
11. Copy below script & paste to here
   LocalSQL_SELECT(plSQL_6, SQL_6, 'Dockey'); //Create New pipeline
   SetDataPipelineFieldLink(Main, plSQL_6, 'DocKey', 'DocKey'); //Link with Main
12. Click File | Save As... to save the file (eg 0Sales Cash Sales 3 (GST 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 0Sales Cash Sales 3 (GST 1))
15. Click DBText icon
16. Click the place you wanted to show/print at the Header
17. Select FromDocNo field
18. For label can Click Label icon
19. Save the report after done

See also