Report Builder - Get Data from Available Pipeline

From eStream Software
Revision as of 10:23, 4 February 2016 by Twfaung (talk | contribs) (→‎Steps)

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

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


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.
  • More Coming soon...

See also