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.

Steps

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

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');
   SetDataPipelineFieldLink(Main, plSQL_6, 'DocKey', 'DocKey');

xxxxx

09. Click File | Save As... to save the file (eg 0Sales Cash Sales 3 (GST 1))

10. Click File | Exit to exit the report design

11. Click Design again in the report designer for the file just save on Steps 9 (eg 0Sales Cash Sales 3 (GST 1))

12. Click Subreport icon

13. Click the place you wanted to print

14. Right click the Sub report

15. Untick the ParentWitdh & manual adjust the sub report width to the with you wanted

16. Select the Pipeline just create (eg plSQL_6)

More Coming soon...

Introduction

Steps

See also