Export Data

Description

Use this page to create export formats and use them to export data.

Usage

This page allows the creation of customised exports. An export can contain either Employee or Company data by selecting information from various related tables. Functionality is also available to filter and sort the result set.

Creating an Export Format

Click on New in the Export Format box to create an export format. This will show a Format Name field and two Export Data Type radio buttons. Enter the Format Name and choose the Export Data Type you require, then click OK.

The new format will appear in the list in the Export Format box. To delete a format, select the required format and click Delete.

Select Data For a Format

To edit an existing entry, click on the required entry in the Format Columns grid. This will populate the boxes in the Column Details section. If the boxes in the Column Details section need to be cleared to add a new entry, click the New button.

Click on the Table drop down box and select the table that contains the data you require.
Click the Column drop down box and select the data column you require.
Enter a name in the Column Alias box. If left blank it will default to the column name.
To sort the data, select the sort type from the Sort Type drop down box and enter a number in the Sort Order box. The data will be sorted by the columns in the order defined by the Sort Order values (where the number already exists, any sort order equal or greater than the number entered will automatically be increased by one). Sort Order 1 is the highest.
Hide Column if you don't want the edited column to appear on the exported document but still have its criteria used for the filtering purposes.
If you desire to export the results in a PDF format, the PDF Column Length (cm) box can be used to specify the width of the given column on the generated document. When left blank it will default to 3 cm.
Select the criteria you want to use to filter the data. These are entered in pairs and are joined together with a connecting word "Or" or "And". For example, if you select a column "Pay Period" and you want to filter it for the period August 2009 you would select "Equals" and enter "200905". If you also want include data for July 2009 you would also select "Or", "Equals" and enter "200904"
Click Save.

Note:

When using the Like operator, the criteria text must make correct use of the "%" sign to define wildcards. E.G. %123 to filter Account Numbers ending in 123, 123% for Account Numbers that begin with 123 or %123% for Account Numbers that contain 123.

To delete a row from the list select the row in the Format Columns grid and click Delete in the Column Details section.

Changing the Display Order

The columns are exported in the order they appear in the Format Columns grid. This order may be changed by dragging and dropping the required Format Column to its new position. Once the Format Column is dropped, its new position will be automatically saved.

Adding All Columns

The first option that shows in the column list is [Add All Columns]. Selecting this option and then clicking the Save button will add all columns for the selected table that are not currently in the export format. This can be useful when creating a full pay summary report or to add any new pay elements that have been added since the export was created.

Creating Formulas

After a table has been selected, a formula may be specified for a column instead of a column by clicking the Formula button. This will bring up the Formula Editor.

A formula may be entered by using the list of available columns (clicking the Add button will add the column to the text box) and operators or by entering the formula into the formula text box.

A formula may contain numbers, brackets, and spaces in addition to the available columns and operators.

Some examples:

([column name] * [column name]) / 2.5
100
[column name] + [column name]

Copying Export Formats

Export formats may be copied by selecting the format name in the Format Details section and clicking the Copy button. The new format will have the same name as the original, suffixed with (copy {number of copy}).

Other Company Formats

Other companies export formats may be run (exported) for the current company by selecting the required company from the company list in the Format Details section.

When running other companies formats, they cannot be edited. They may be copied to the current company by selecting the format in the format list and then clicking the Copy button. Once copied they can be edited by selecting Current Company from the company list.

Generating Data From an Export Format

Select the format you require in the Export Format box and click the Export button. The following modal will be displayed.

The Payroll dropdown allows you to specify the payroll the exported data will be extracted from, while the Exclude Leavers gives you the option to remove the leaver employees from the selection.

The row of icons underneath 'Download Export Data' will generate a document in either CSV, XLSX, or PDF format. The last icon in the row allows sending the data to the Document Centre page of the Client Portal. Clicking on it will open a mini modal, offering the option to select the subfolder the data should be sent to, as well as the format of the generated file. Sending to the Client Portal is only possible after specifying the payroll in the Payroll dropdown.

The data may take some time to be created.

Once a file has been generated for download, the format selection icons will be disabled. Wait for the file to download to prevent any errors. To run another export once the file has been downloaded, close and reopen the Export Data modal by clicking on the Export button in the Format Details section again.

Note:

Once you have downloaded the data it will no longer be under the security of the payroll system. You will need to ensure that sensitive data is dealt with in accordance with your company security procedures.
The maximum number of records that may be exported is 10,000.
Data from some tables which return multiple rows for an employee - examples include SSP and Employee Pay Balances - but are unrelated, can not be selected under the same export. In these cases a message will be displayed when the export column is saved.

Ability to lock created exports

You can now lock any export formats you have built, stopping them from being deleted in error. Within the export screen, select the export format you wish to secure and click lock.

Once locked the delete button will be greyed out. You can unlock the export if you no longer require it, and the delete button will be selectable.

We have also added an additional step when deleting to help protect your data. When clicking delete you will see a message advising you this cannot be undone, you must click delete within 12 secs to go ahead with the deletion.

Recovering deleted exports

Currently deleting an export only performs a soft delete. While you cannot see the export yourself, support can retrieve this. You will need to know the name of the export deleted in error, and support will organise the export to be reinstated for you.