Sub Reports to create more advanced reporting

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:

 Sub OnReportStart

      rpt.dcRptData.ConnectionString= MyClass.ChangeSubreportConnection(rpt.dcRptData.ConnectionString,true)

End Sub

 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

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.

 Sub OnDataInitialize

                set rpt.subreportcontrol.object = MyClass.GetSubreport(“C:\Development\Pastel Payroll 32-bit\Source\CUSTOM\Reports\Customised\SubReport1.rpx”)

End Sub

 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:

 Sub OnFormat

                rpt.subreportcontrol.object.dcRptData.Source = “SELECT UserCode, Description FROM CostCentres where UserCode = ‘” & rpt.Fields(“CostCode”) & “‘”

End Sub

 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)


Filed under Stuff you never knew about

2 responses to “Sub Reports to create more advanced reporting

  1. nicolene

    Hi I just would like to know a person that was booked off sick with a certificate worked overtime on those days, the company did not require him to work those days. do we pay him for all those days with the overtime.

    • Madelein van der Watt

      Hi Nicolene

      If an employee could not render any services due to illness and he has a medical certificate as proof of incapacity, then he is entitled to receive his normal wage for those days provided he still has sick leave days available (30 days over a period of 3 years is the minimum entitlement).
      Overtime is generally allowed upon agreement between the employee and employer. If the employer requires the employee to work overtime, it must be agreed and the payment – whether in money or time off – must also be agreed.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s