Analytics

Analytics is an easy and powerful feature that lets you create reports and analyse payroll data using Excel or LibreOffice. Analytics uses the Finance Journal report.

Universal Finance Journal

Add the report as follows:

  • Navigate to Report Definition

  • Add…

  • Select the Company

  • For the Template select Finance Journal

  • In the Settings enter xlsx if you are using Excel or ods for LibreOffice. csv is also an option.

  • For convenience the Start date and End date can be set in the past and in the future to capture more data than needed. In the report analysis later you can select the exact period required.

  • Optionally, add any Pay definitions… which you may want to always have available

  • Add..

After every payrun, generate an updated journal file as follows:

  • Click on My analytics

  • Ensure the Start date and End date cover at least the period required (more is fine)

  • Optionally, add any Pay definitions… if you want to query those

  • click Download

The location of the downloaded Finance Journal.xlsx file is browser and operating system dependent, but it will be in the usual ‘Downloads’ folder. The file is organised as a data table and ready for pivot table analysis. If you are not familiar with Pivot tables, there are a huge number of web resources.

For example to create a pivot table to compare 2 pay runs:

  • Open in Excel

  • Select any cell in the table

  • Insert ‣ Table ‣ PivotTable into a new worksheet

  • Click OK.

  • Open a new workbook File ‣ New which will be your new report.

  • Select employee, actual_t and pay definitions as PivotTable Fields.

  • Filter the two relevant actual_t dates

  • Disable sub-totals

  • Save the report which contains all data required

../../../_images/analytics_finance_journal_pt.png

Creating and saving a Custom Report

Generally you may want to use a similar report for every pay run. Create and save a Custom report as follows:

  • Delete or move any previous reports out of your downloads folder

  • Download a new report and check that is is named as Finance Journal.xlsx

    Tip

    If you are using a cloud storage service like Microsoft OneDrive, then you should download to a folder on that service and not the default folder on a local machine. This avoids errors when the Custom Report accesses the Finance Journal.

  • Open in Excel

  • Select any cell in the table

  • Insert ‣ Table ‣ PivotTable into a new worksheet,

  • Before pressing proceeding, carefully edit the Table/Range to only specify columns

    • For example the default Table/Range might be ‘Finance Journal’!$A$1:$BE$118

    • Edit this to ‘Finance Journal’!$A:$BE to only use the columns A to BE. This way, when you generate a new file which has more rows, you can pick up the extra rows

    • Click OK

  • Now prepare the Custom report with all the content that you wish to reuse

  • Save the report with a different name which will now be your Custom report

Refreshing a Custom report with new data

Each time you generate a new Finance Journal, you can open any Custom report and automatically pick up new data as follows:

  • Download a new finance journal report

  • Open the finance journal report in Excel, but merely leave it in the background (this step should not be necessary but an issue in Excel means this is a required step)

  • Now open your Custom report

  • If you see a Security Warning then click Enable Content to allow the Custom report to read content from the finance journal

  • Refresh the report with Data ‣ Refresh All.

  • The Custom report now has access to all new data

More information. The Finance Journal report definition specifies:

Format:

Spreadsheet file (csv, ods, xlsx)

Start date:

Pay runs start. Blank is 31 days before end date

End date:

Pay runs end. Blank is 31 days after start date, or today

Pay definitions:

Semi-colon separated names or regular expressions (.* for all)

Group numbered:

Group numbered Pay Items into one column e.g. Shift 1, 2