Report Builder - Get Data from Available Pipeline

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

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,  Tax, TaxRate, Sum(LocalAmount) LocalAmount, Sum(LocalTaxAmt) localTaxAmt, '+ 
	   'Description  FROM Document_Detail ' +
	   'Where Tax <> ''''' +
	   'GROUP BY Dockey, Tax, TaxRate';

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))

15. Click Subreport icon

16. Click the place you wanted to show/print

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

22. Click Report & set the following setting

Title - Select
Summary - Select
Header - UnSelect
Footer - UnSelect

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

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

[top]

Example 2 - Show 1st FromDocNo at Header

Below is Example will get FromDocNo Field from the Detail Data

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, Min(Seq) Seq, FromDocDate, FromDocNo FROM Document_Detail '+ 
	       'WHERE FromDocType <> ''''  ';

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

[top]

Example 3 - Show Total SVE at Footer

Below Example is to Get total SVE for 6%

Last Script Update : 23 Jul 2024

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, SUM(LocalAmount)*0.06 SVE FROM Document_Detail ' +
           'WHERE Tax=''SVE'' ' +
           'GROUP BY DocKey';

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 Sales Invoice 1-New)

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 Sales Invoice 1-New)

15. Click DBText icon

16. Click the place you wanted to show/print at the Header

17. Select plSQL_6 pipeline

18. Select SVE field

19. For label can Click Label icon

20. Save the report after done

Introduction

Example 1 - Show GST Summary at Last Page

Example 2 - Show 1st FromDocNo at Header

Example 3 - Show Total SVE at Footer

See also