Reports

Payroll Reports

The creation of a report with the Payroll Engine is divided into two steps:

• Backend: preparation of the report data
• Client: transformation of the report data using a report template

The engine is designed so that any reporting tool that supports ADO.NET DataSets as a data source can act as a report client. The data format in the report template can be determined by the client, e.g. .docx (Word Mail Merge), .frx (FastReport) or .rdlc (SQL Server).

FastReport

The payroll console and the web application use the open source reporting tool FastReport Open Source as Report Client.

For the visual design of reports FastReport offers the free Community-Edition Report Designer. Please start the download FastReport.Community.{Version}.zip, unpack the archive locally and start the programme Designer.exe.

Regulation Report

In the following, a PDF report of the payroll results of one month is created for the example Basic Payroll. A payrun job name is expected as a report parameter.

The report is a regulation object and consists of:

• Queries: Queries from REST API endpoints
• Parameters: Runtime values to control the report
• Templates: Templates for transforming the report data into documents

Example payroll report Report.json:

1  {
2    "$schema": "../../Schemas/PayrollEngine.Exchange.schema.json",
3    "createdObjectDate": "2023-01-01T00:00:00.0Z",
4    "tenants": [
5      {
6        "identifier": "StartTenant",
7        "updateMode": "NoUpdate",
8        "regulations": [
9          {
10            "name": "StartRegulation",
11            "updateMode": "NoUpdate",
12            "reports": [
13              {
14                "name": "StartReport",
15                "queries": {
16                  "Employees": "QueryEmployees",
17                  "Results": "QueryPayrollResultValues"
18                },
19                "parameters": [
20                  {
21                    "name": "TenantId",
22                    "parameterType": "TenantId",
23                    "hidden": true
24                  },
25                  {
26                    "name": "PayrunJobName",
27                    "mandatory": true
28                  },
29                  {
30                    "name": "Results.Filter",
31                    "value": "jobName eq '$PayrunJobName$'",
32                    "hidden": true
33                  }
34                ],
35                "templates": [
36                  {
37                    "name": "StartReportTemplate English",
38                    "contentFile": "Report.English.frx",
39                    "culture": "en"
40                  },
41                  {
42                    "name": "StartReportTemplate German",
43                    "contentFile": "Report.German.frx",
44                    "culture": "de"
45                  }
46                ]
47              }
48            ]
49          }
50        ]
51      }
52    ]
53  }

The components of the report in detail:

• 2: Path to the JSON schema (adapt this to your local environment)
• 3: Default creation date for each object
• 7: Do not update the tenant (see Basic-Payroll Test)
• 11: Do not update the payroll (see Basic-Payroll Test)
• 13-47: Report definition
• 14: The report name
• 15-17: The report queries
• 16: Query for the employees
• 17: Query for the payroll results
• 19-34: The report parameters
• 20-24: Report parameter tenant
• 22: Parameter type tenant
• 23: Hide parameter tenant
• 25-28: Report parameter payrun job name
• 27: Mandatory report parameter
• 29-33: Report parameter for the result query filter
• 31: The filter for the results query, matches the job name parameter 26 (OData expression)
• 23: Hide parameter result query filter
• 35-46: The report templates
• 41: The template language
• 36-40: The English remport template
• 38: The English template file name (same folder)
• 39: The English template culture en
• 41-45: The German remport template
• 43: The German template file name (same folder)
• 39: The German template culture de

Report Template

The Report Template FastReport is an XML file. To use a DataSet as a data source, the TableDataSource must be used.

Example report template (extract) Report.frx:

1  <?xml version="1.0" encoding="utf-8"?>
2  <Report ScriptLanguage="CSharp" ReportInfo.Created="06/20/2009 22:40:42" ReportInfo.Modified="07/13/2023 19:22:57"
           ReportInfo.CreatorVersion="2023.2.0.0">
3    <Dictionary>
4      <TableDataSource Name="Employees" ReferenceName="Data.Employees" DataType="System.Int32" Enabled="true">
5        <Column Name="Id" DataType="System.Int32"/>
6        <Column Name="Status" DataType="System.String"/>
7        <Column Name="Created" DataType="System.DateTime"/>
8        <Column Name="Updated" DataType="System.DateTime"/>
9        <Column Name="Identifier" DataType="System.String"/>
10        <Column Name="FirstName" DataType="System.String"/>
11        <Column Name="LastName" DataType="System.String"/>
12        <Column Name="Culture" DataType="System.String"/>
13        <Column Name="Calendar" DataType="System.String"/>
14      </TableDataSource>
15      <TableDataSource Name="Results" ReferenceName="Data.Results" DataType="System.Int32" Enabled="true">
16        <Column Name="Id" DataType="System.Int32"/>
17        <Column Name="Status" DataType="System.String"/>
18        <Column Name="Created" DataType="System.DateTime"/>
19        <Column Name="Updated" DataType="System.DateTime"/>
20        <Column Name="PeriodName" DataType="System.String"/>
21        <Column Name="EmployeeIdentifier" DataType="System.String"/>
22        <Column Name="DivisionName" DataType="System.String"/>
23        <Column Name="ResultKind" DataType="System.String"/>
24        <Column Name="KindName" DataType="System.String"/>
25        <Column Name="ResultNumber" DataType="System.Decimal"/>
26        <Column Name="ResultValue" DataType="System.Int32"/>
27        <Column Name="ResultNumericValue" DataType="System.Decimal"/>
28      </TableDataSource>
29      <Relation Name="EmployeeResults" ParentDataSource="Employees" ChildDataSource="Results" ParentColumns="Identifier"
                 ChildColumns="EmployeeIdentifier" Enabled="true"/>
30    </Dictionary>
31    <ReportPage Name="StartPage" Watermark.Font="Arial, 60pt">
32      <DataBand Name="Employee" Top="54" Width="718" Height="65" DataSource="Employees">
33        <TextObject Name="EmployeeTitle" Left="38" Top="35" Width="641" Height="30" 
                   Text="[Employees.FirstName] [Employees.LastName] ([Employees.Identifier])" Font="Arial, 12pt"/>
34        <DataBand Name="Result" Top="143" Width="718" Height="20" VisibleExpression="[Results.ResultNumericValue] != 0" DataSource="Results">
35          <TextObject Name="PeriodName" Left="38" Width="134" Height="20" Text="[Results.PeriodName]" Font="Arial, 10pt"/>
36          <TextObject Name="ResultKind" Left="189" Width="2079" Height="20" Text="[Results.KindName]" Font="Arial, 10pt"/>
37          <TextObject Name="ResultValue" Left="530" Width="150" Height="20" Text="[Results.ResultNumericValue]" Format="Currency" 
                          Format.UseLocale="false" Format.DecimalDigits="2" Format.DecimalSeparator="." Format.GroupSeparator=","
                          Format.CurrencySymbol="$" Format.PositivePattern="0" Format.NegativePattern="0" HorzAlign="Right" Font="Arial, 10pt"/>
38          <TextObject Name="Text1" Left="415" Width="94" Height="18" VisibleExpression="[Results.ResultNumber] &gt; 0"
                          Text="[Results.ResultNumber]" WordWrap="false" Font="Arial, 10pt" Trimming="EllipsisCharacter">
39            <Formats>
40              <CustomFormat Format="0.##"/>
41              <GeneralFormat/>
42            </Formats>
43          </TextObject>
44          <DataHeaderBand Name="ResultsHeader" Top="121" Width="71" Height="18.9">
45            <TextObject Name="PeriodTitle" Left="38" Width="134" Height="15" Text="Period" Font="Arial, 8pt"/>
46            <TextObject Name="KindTitle" Left="189" Width="207" Height="15" Text="Kind" Font="Arial, 8pt"/>
47            <TextObject Name="ValueTitle" Left="530" Width="150" Height="15" Text="Value" HorzAlign="Right" Font="Arial, 8pt"/>
48          </DataHeaderBand>
49          <Sort>
50            <Sort Expression="[Results.ResultKind]" Descending="true"/>
51          </Sort>
52        </DataBand>
53      </DataBand>
54    </ReportPage>
55  </Report>

The components of the FastReport template:

• 3-30: The data declaration with tables and relationships
• 4-14: The data table Employees
• 15-28: The data table Results
• 29: The relationship between Employees and Results
• 31-54: Report page
• 32-53: Parent report band Employee
• 34-52: Child report band Result
• 35-43: Report row with values
• 44-48: Report band header
• 45-47: Report band header row with values
• 50: Sort report results by ResultKind

Execute Report

With Payroll Console, the required report parameters must be stored in a JSON file.

Example JSON report parameters Report.Parameters.json:

{
  "PayrunJobName": "StartPayrunJob.Jan23"
}

The parameter contains the

• PayrunJobName is determined by the report, JSON line 26.
• StartPayrunJob.Jan23 comes from Payroll Test.

The report is created with the following commands:

• Report.Setup.cmd: Creates the company, generates test data and creates a PDF report (Initial).
• Report.Pdf.English.cmd: Generates an English PDF report
• Report.Pdf.German.cmd: Generates a German PDF report
• Report.Excel.cmd: Creates an Excel report
• Report.XmlRaw.cmd: Generates a raw XML report

In the report dialog of the web application, parameters and target format are set and the report is made available for download.

Next steps

• Forecasts
• Payroll Automation with No-Code and Low-Code
• Payroll Testing
• Extended Functions
• Custom Actions
• Resources with documents, blogs, tests and examples