Creating Reports using the Standard Report Writer

The Subscribe-HR Software system is Object Orientated and this is the same for Reports. The ability to add particular fields into a report solely relies on that field being part of the object.

After logging into the Subscribe-HR HCM Platform, select "REPORTS" from the left hand navigation located under "TOOLS".
You will arrive on the "STANDARD" Tab.
Click on a Standard Report to open this Report.
You will then arrive on the "GENERAL" tab. If you are using Team Dashboards, the options will be displayed in the Standard Report Set-up Wizard. This will be activated when you "Create a New Report from the Standard Report Tab or the My Standard Report Tab. For Team Dashboard Users, you will be able to Create an Advanced / HR Metrics Report using SQL through the Advanced Report Wizard.
If you want to create a "NEW" Report for Standard Reporting or Advanced HR Metrics Reporting. You will need to select the " STANDARD REPORTS or MY STANDARD REPORTS" for Standard Reporting or "STANDARD ADVANCED REPORTS or MY ADVANCED REPORTS" for Advanced HR Metrics Report Creation. Selection "Reporting" from the Tools options from the Left Hand Navigation. Please note Standard and Advanced Reporting is only available if you are using "Team Dashboards".
When you are creating a new Standard Report: Select the Public check box.
By selecting the Public check box you are making all information in this report available to all users, regardless of Self Service permission settings.
By removing the check from the Public check box you are making this report private and only available to users with permissions.
Select the Report Type from the drop-down, such as ‘Human Resources’, ‘Recruitment’, etc. This selection is to assist in finding the correct type of report, but doe not mechanically affect the report.
Enter the Report Name. Ensure the report name is descriptive.
Select the Object this Report is to be based upon.
If you want to retrieve data from more than one Object (Table), tick the Join Tables (Advanced).
Select the Display Empty Records if required. This displays all entries in an object and sub-object relationship, so that all entries in the object can be viewed even if they do not have an entry in the sub-object.
If you do not want headers for columns in your exported reports, select Remove Column Headers(Export Only).
Enter the Export File Name. Any Exports or scheduled Exports will use this name.
Enter a report Description if required.
Enable the Report Scheduler if required.
If Report Scheduler is selected, then you will get the following options:
Select the Send with Group Permission drop-down option. This will send the report with this groups specific permission settings to each recipient.
Select the Format you want the report delivered in.
Select the Frequency you would like to send report.
Select the Recipients by selecting the user.
Select any permission Groups you would like to send the export to.
Save Report before Joining Tables or setting up fields.

Reports created in this way, can be found in the "Standard Reports" tab, or in the "MY STANDARD REPORTS" tab, in the Reporting Tool. If you are using Team Dashboards, Standard Reports can be inserted into the Standard Reports Dashboard. If you are not using Team Dashboards, Standard Reports can be added into your User Dashboard by using the Add Report Option at the top left hand side of the Dashboard.

Join Tables

After ticking Join Tables (Advanced) & then saving the Report, this will then activate the Join Tables SubTab.
On the Join Tables SubTab, enter Table (Object) name (or part of) you wish to include in the Join.
Click on table, then drag line to next table to join them.
Then you will be prompted to select required From Field & To Field to join, then the Join Type:
LEFT JOIN shows all records from left table regardless of the existence of matching records in the right table.
RIGHT JOIN means opposite of LEFT JOIN. It shows all records from the second (right) table and only matching records from first (left) table.
INNER JOIN shows all records where there is at least one match in either table.
Click OK & repeat process for other Tables to include.
To remove a Link between Object, simply click on the dot where the link connects to the Object & drag & drop it away from the Object.
Save changes, before moving onto the Fields SubTab.

Fields

Select the Fields SubTab.
To find the required Field (Element), start typing in the Fields section. This will show in a dropdown of all available Fields matching the search criteria. You can reorder the Fields selected by Drag & Drop.
Optionally select the field(s) to Group the Report by.
Save changes.

Totals & Aggregate Functions

Click on the Pencil Icon on the right of the Field. Select from the dropdowns, the Basic Function (Average, Count, Max, Min or Sum).
The Calculate Total field is populated with a check box when a numeric field is added. If the check box is selected this will provide a total of all numeric values at the bottom of the report, if the report is grouped by a field, then the total is placed at the bottom of each grouping.
Save changes.

Formatting

Click on the Pencil Icon on the right of the Field. The Date Formatting field is populated with a drop down when a date field is added.
If formatting is selected such as Month Name this will only display the Month name in the report and not the dd/mm/yyyy.
Save changes.

Sorting

Click on the Pencil Icon on the right of the Field. The Sorting Order field is populated with a drop down for all fields.
Selecting the sort order of either Ascending or Descending for a field will cause the report to output the data in either an Ascending or Descending order highest to lowest.
Save changes.

Condition

Conditions are used to filter your report based on a criteria.

Down the bottom of the Fields Sub option there is the Condition section.
Select the field you wish to filter by.
Select the filter type. The filter type varies depending on what field you are filtering on.
The most common are Equals To, Not Equals To, Is Last, This Last, Is Next.
Select or Enter the value you are filtering on.
For more than one Condition an AND or a OR operator can be selected.
You can select multiple results for some fields by clicking on Options (if available) as shown below.
To group Condition clauses together, brackets can be used. This is done by clicking on the "Greater Than" icon to the right of the Ffeld in the condition clause. Clicking on the "Less Than" icon will remove a Bracket.
The below Condition clause can be written as:
:IF (Leaver = No OR Last Day of Work is Last Week) AND Department=”???list of Department???”
Save changes.

View Report

The View Report TAB allows us to generate and view the report. We can export to PDF or Excel file formats.
Only reports that are 'Grouped by can generate a Graph, otherwise a plain table of results will be generated.

Select the View Report SubTab.

Graph Types

The Graph type option will not be shown if a report has not been Grouped by. Selecting a Graph type will change the graph drawn on the report.

Graph Record Count

Selecting the Graph Record Count will show the number of records being reported on.

Export Options

Selecting an Export Option and then selecting the Export link will cause the report to be exported into the chosen format (Excel, PDF, etc).

Do Not Export Graph

Selecting this checkbox will cause the graph to be excluded when exporting the report to the chosen format.

Scheduling Reports

Reports can be scheduled to be sent to user roles or for specific users. These will be sent in an exported format via email to users.
If sent to a user, the exported file will only show information that the user has permissions to see within the Subscribe-HR system.
To set up report scheduling:

After logging into the Subscribe-HR HCM Platform, select "REPORTS" which is located under "TOOLS". From the left hand navigation.
Select Standard or MyReports.
Select the desired report.
Tick the Enable Scheduler checkbox.
Choose which Permission group to send the report in, using the Send with Permission Group drop-down.
Choose the Format that the report will be output in. This can be in excel, or as a .csv.
Select the Frequency of the Report emails. Daily, Weekly, Fortnightly or Monthly.
Choose the Recipients of the report. If selecting Specific Users, you will need to select those users from the multiselect. You can also select Groups to send to, instead of or in addition to the users.. If selecting FTP, you will need to select which FTP from those that have been setup in the Maintenance module, here.
Select the Save button

Standard Reports Visibility

The content of Standard Reports (which fields on which Reports are visible to which Users) are based on based on User Group Permissions.

Creating Advanced Reports using the Advanced Reporting Tool - Team Dashboards Only

After logging into the Subscribe-HR HCM Platform, select "REPORTS" from the left hand navigation located under "TOOLS".
You will see options which allow you to select Advanced Reports and My Advanced Reports.
Click on either Advanced Reports or My Advanced Reports to activate the Advanced Reporting set-up Wizard. Please note that if you are using "Team Dashboards", the Standard Report set-up which is activated from the "Standard Report Tab" and or the "My Standard Reports Tab" will also use a Wizard to allow you to create a Report.
The first Step in the "Advanced Report" following Clicking the "Create" Button, will be the SQL stage. You can create SQL here which will allow you to create the SQL which will be used in the Report.

Basic Settings

Enter in the Reporting Tool Name.
Select the Default Graph Type to be displayed. Options include Line Chart, Bar Chart, Vertical Bar Chart, Pie Chart, Donut Chart and Numbers.
Select the Reporting Type to be displayed. Options include:
- Graph Only
- Numbers only
- Both Graph & Numbers
- Custom Template - When you select this option, Refine Settings and Graph Settings does not show up. Custom Template, shows 4 Tabs: API, Template, Front End JavaScript, CSS and Preview. These options allow you to create your own Custom Template for Displaying data.
The Data Source field allows you to select whether you would like to enter in via SSQL Statements or via a script. Options include:
- SSQL - a single SSQL statement used to generate report data
- Script - multiple SSQL statements with logic behind and combine the data into a single data set, which will be for report generating. (Hint) Sample has to be given in the system when you choose Script as data source is in the wizard.

Refine Settings - Does not show if Custom Template is being used

Display in Result field allows you to select which report data (from the original data set) to display in the Report.
Sorting field allows you sort the data based on the options included in Display Results (the options selected in the above/previous field).
Filtering field allows you to filter the data based on the options included in Display Results (the options selected in the above/previous field).

Custom Template - Shows if you select Custom Template Reporting Type in Basic Settings

API Tab - Allows you to query the database.
Template - This is for your HTML Template for structuring your design/layout. This is where you put your results.
Front End JavaScript - This allows you to further manipulate your design/layout.
CSS - This allows you to further style your Layout - Design.
Preview - Preview your layout - design.

Graph Settings - Does not show if Custom Template is being used

Group By (for Charts) field allows you specify which data set to 'Group' the display by in the Report.
Display in Charts field allows you to specify which data set/s to display in the Report.
Group By (for Table Data) allows you to drill down into the data further (if the Reporting type selected includes table data as opposed to a graph only) based on the Groups specified. This feature is optional and Table Data (if included in the Report) will only be grouped in this way if grouping options are applied here.

SSQL

SSQL consists of a subset of the operations of SQL standard. The following outlines SSQL compared to standard SQL statements.

Generally support only SELECT, INSERT, UPDATE and DELETE operations
Do not support subquery
Aggregate functions (https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html) are not supported in conditional clause
User has to name specifically what to select. e.g. SELECT t.Id, t.DateOfBirth FROM Employee t
Do not support the LIMIT keyword
Limited support of having underscore (_) as part of the alias

SSQL or Script

It is always recommended to start with SSQL. If the report requires data that a single SSQL cannot gather, Script will be used to extract the right information. With the limitation of SSQL outlined in the above section, Script is recommended. Script is recommended for advanced users.

Building a Report with SSQL

For “Numbers Only” or “Both Graph&Numbers” reporting type, the SSQL must include a datetime element in the ‘Select’ clause. E.g. SELECT t.Id, t.DateOfBirth from Employee t.

The system will pick up the field name for use later. Some examples are given below:

“t.Id” becomes “Id”
“t.DateOfBirth AS dob” become “dob”
“t.TaxFileNumber EmpTFN” become “EmpTFN”

In the setup wizard, you can use a different name for the field selected. Please provide meaningful name or use existing name to understand what is being built.

Build Report with Script

At start, the Script box is already prefilled with some sample code. It is recommended to carefully study the sample code before moving on modify the content. Please develop your code at the same time outside of the browser. Code in editor - browser is volatile and harder if not impossible to recover.

The box takes in JavaScript code with a level of support of recent additions to the JavaScript family.

The beginning of the sample code includes a library of “lodash” (https://lodash.com) so it is expected to use of lodash function. Another library that can be included is ‘momentjs’ (https://momentjs.com). Users are not expected include other libraries.

The empty structure of ‘result’ structure corresponds to the last box where the ‘result’ will be returned to the system for further processing. The processed results will be fed into the ‘data’ property inside the ‘result’ structure as part of the result.

The ‘queryObjects’ is a key function to get data from the database.

 var SSQls = [
     "…"
 ];

The SSQls variable is where you include all your queries. The system will obtain the full text from the first character “v” up until the last character of “;”. It is important to confirm this in the system because it will extract key names and labels from this variable.

After the line “Start your own code from below” is where the user code sits. Here the user will write their own code including fire query using the ‘queryObjects’ function. The function will return rows of information and each row becomes 1 array element (which is a JavaScript object). The selected field (and alias) in the statement will become the property of the array element objects.

The returning result structure requires the same field/alias used in the SSQL statements. If this is not done the system will not be able to pick up the generated numbers of interest. For example: with the query “SELECT t.Manager isManager, t.Age AS Generation from Employee t WHERE t.Manager = 1”, the result should be:

 [
   data: [
     { isManager: 1, Generation: 52 },
     { isManager: 1, Generation: 29 },
     .
     .
     .
   ],
   count: 42
 ]

Refine Field Settings

In the Display field you can select how the fields will be displayed in the table sheet when viewing the full report.
Fields can be sorted in the Sorting field.
Filters can also be applied under Filtering.

Graph Settings

These settings will display if Graph Only or Both has been selected as the Reporting Type.

Select and add the fields to group by or display by clicking on the dropdown and selecting the appropriate fields.
If needed, you can select the functional output. Options include Average, Count, Max, Min and Sum.
You can enter in a custom label for each field for the report in the "Name showed in chart" field.

Numbers Settings

These settings will display if Numbers Only or Both has been selected as the Reporting Type.

Select the field to display on the report.
Select the function that should apply on the field. This is optional if the value of the field is numeric.
Select the Field for Comparison. Only date fields can be selected. This will compare the values to the values recorded on this specified date.
Select the duration of the compared timeframe. Options include Quarter, Month, Half Year, Year and Day.
Select the appropriate period to compare to. Options include Previous Period and Previous Year.

Example 1: Compare number of employees in the current quarter vs the previous quarter.

Field: EmployeeNumber (alias or field in the selection of SSQL statement)
Function: Average
Field for Comparison: Month (alias or field in the selection of SSQL statement)
Duration: Quarter
Period: Previous Period

Example 2: Compare number of employees in the current month vs the same month last year

Field: EmployeeNumber (alias or field in the selection of SSQL statement)
Function:
Field for Comparison: Month (alias or field in the selection of SSQL statement.
Duration: Month
Period: Previous Year

Edit Reporting Tool

This can be triggered by clicking the pencil icon in the list under the "My Advanced Reports" tab.

All the steps are exactly the same as the above create report tool. Please bear in mind that when you change any of the below setting, the created report will be flushed.

SSQL statements in SSQL or Script.
Fields for sorting & filtering.
Field for comparison in Numbers only report.

Filters in Reports in Advanced Reporting and Metrics Dashboard

For each Widget in the Standard Reports Dashboard and the Advanced Reports and Metrics Dashboard, there is a Icon on the Top Right of the Widgets which allows you to edit filters for the Reports. These filters can relate to Date Ranges and also selection of the information being showed in the Report within the Dashboard.

Standard Advanced Reports

Hired (by Period) - This Report show people who have been hired. Using the "Start Date" at the main data point. You can run this report across periods of time using the filters in the Advanced Report. Department is used to view the Data.
Percentage of Holidays Used v Remaining as of Today (by Department) - This Report show how many "Annual Leave" days have been used in comparison to how many are remaining. Current data as per that day. By Department.
Average Tenure (by Department) - This Reports shows the Length of service by Department.
Voluntary Termination Rate - This report shows how many people have left the business "Voluntarily". When you mark someone as a "Leaver", the "Termination Type" should be "Voluntary".
Involuntary Termination Rate - This report show how many people have left the business "Involuntarily. When you mark someone as a "Leaver", the "Termination Type" should be "Involuntary".
Number of Employees - This Report show the Head Count across periods of time.
Hired - This Report shows % of People Hired in comparison to the same Period the previous year. The report will show if you are + or -.
Leavers - This Report show the & or Leavers in comparison to the same period the previous year. The report will show if you are + or -.
Total Salary - This Report will show the Yearly Average v the Previous Year. the report will show if you are + or - .
Attrition Rate - This Report will show the Remaining v Hired v Left Employees. Across period of time.
Offer Acceptance Rate - This Report will show the number of people who accept Onboarding and Complete this process. V the number of people who are incomplete for their Onboarding.
Time to Hire (by Department) - This Report will show the number of days it takes to find an Applicant and Hire them.
Current CPD Hours Require per Job - This Report will show the number of Continual Professional Development hours required for an individual Job. Across a Period of Time.
Staff FTE Summary - This Report shows a summary of FTE by Job/s:
- Budgeted FTE - relates to the Headcount Required field for a given Job in Subscribe-HR.
- Approved FTE - relates to the Headcount Approved field for a given Job in Subscribe-HR.
- Occupancy - relates to the Employee’s Job FTE field in Subscribe-HR.
- Variance - is calculated based on the above to be displayed in the Report.
Staff FTE Summary Via Department - This Report shows a summary of FTE by Department/s:
- Budgeted FTE - relates to the Headcount Required field for a given Job in Subscribe-HR.
- Approved FTE - relates to the Headcount Approved field for a given Job in Subscribe-HR.
- Occupancy - relates to the Employee’s Job FTE field in Subscribe-HR.
- Variance - is calculated based on the above to be displayed in the Report.
Skills Audit by Department – This Report shows Employee Skills by Department and the latest Review Score. Based on Self and Manager assessment for the last Review.
Goals Audit by Department – This Report shows Employee Skills by Department and the latest Review Score. Based on Self and Manager assessment for the last Review.
Values Audit by Department – This Reports shows Employee Skills by Department and the latest Review Score. Based on Self and Manager assessment for the last Review.
Behaviour Audit by Department - This Report shows Employee Skills by Department and the latest Review Score. Based on Self and Manager assessment for the last Review.
Skills Final Score by Department – This Report shows the Average of Self and Manager Rating. Final Score per person by Department. Latest Review.
Goals Final Score by Department – This Report shows the Average of Self and Manager Rating. Final Score per person by Department.
Values Final Score by Department – This Report shows the Average of Self and Manager Rating. Final Score per person by Department.
Behaviours Final Score by Department – This Report Shows the Average of Self and Manager Rating. Final Score per person by Department.
Final Score ALL by Department – This Report shows the overall average (across Skills, Goals, Values and Behaviours) – Final Score for all.
Final Score Category by Department - This Report shows the overall average (across Skills, Goals, Values and Behaviours) – Final Score by Department.
Average Score Category by Employee - This Report shows the average per Category (by Skills, Goals, Values and Behaviours) – by Employee.
Final Score by Employee - This Report shows the overall average (across Skills, Goals, Values and Behaviours) – Final Score by Employee.
Training Hours by Department - This Report shows Employees and the Training they have undertaken across Periods of Time including Hours for Training Completed.
Training Spend by Department - This Report shows Employees and the Training they have undertaken across Periods of Time and the Spend for Training Completed.

Advanced Reports Visibility

If Advanced Reports are visible (not hidden), they are all available to any/all Users that have been granted access to the Advanced Reports.

Common Issues and Troubleshooting

Zero data points in the graph
- First check with the query statement and start of with simple. Remove all/most complex handling code or selection criterion. Take baby steps.
“SSQls can't be parsed. Issue code: 4”
- This is about the ‘SSQls’ variable. The platform cannot parse the line. Please check and remove syntax that could potentially trigger the error

Creating Standard Reports and Advanced Reports (Team Dashboards)

Contents