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
Example 2 - Show FromDocNo at Header
Below is Example doing following actions
- Get data information (Tax, Tax rate, LocalAmount, LocalTaxAmt)
- Group the data by Tax code
Last Script Update : 05 Jun 2020
Steps
See also
- Report Designer
- Others Customisation