As part of the report writer that we ship with our payroll we have enabled the use of sub reports.
You can create a main report with summary information and refer to sub reports to get additional information.
Here is an example. Please note that you must have Pastel Payroll 2012 Update 1 or later installed.
If at first it seems a bit to advanced or difficult, don’t worry. We will be posting more on report writing that will fill in the gaps.
Create a report & call it SubReport1.rpx
Add a data control & call it dcRptData
On the data control set the SQL script to:
SELECT UserCode, Description FROM CostCentres
Set the connection string to Provider=PervasiveOLEDB.9.60;Data Source=REPORTSDEMO;Persist Security Info=False
Add the necessary data fields to print (UserCode & Description to print Cost Code & Cost Code Description)
In the Scripting, ActiveReports Document, OnReportStart event, add:
What this does is passes the current connection string in and the true or false indicates whether to change the connection string or not.
The sub report is set up for the DEMO company so if you are running this report & sub report for company XYZ then you’ll want the connection string to be changed to
Provider=PervasiveOLEDB.9.60;Data Source=REPORTSXYZ;Persist Security Info=False
In some cases the user may not want the connection string altered at all.
Main report SQL statement:
Select * from EmployeeMaster
Add fields to print perhaps Employee code, Name & Whatever else.
Add a field to the report & call it for example txtCostCentre, set the data field to CostCode (You may want it visible or not)
To add the sub report to the main report, add a sub report control to the detail section of the main report, usually under the fields needed for the main report.
Let’s call it SubReportControl.
In the Script editor, add the following to ActiveReports Document in the OnDataInitialize event.
set rpt.subreportcontrol.object = MyClass.GetSubreport(“C:\Development\Pastel Payroll 32-bit\Source\CUSTOM\Reports\Customised\SubReport1.rpx”)
subreportcontrol will be the name of the sub report control on the main report.
The path & name of the sub report are passed to the sub MyClass.GetSubreport, so change these if necessary.
In order to link for example an employee’s cost centre to the Cost centres file to get the cost centre description, add the following script in the Detail section to the OnFormat event:
rpt.subreportcontrol.object.dcRptData.Source = “SELECT UserCode, Description FROM CostCentres where UserCode = ‘” & rpt.Fields(“CostCode”) & “‘”
This changes the SQL statement on the data control of the sub report so that it reads the corresponding cost centre description & prints it.
(Thank you to Debbie for making this possible)