In this article we look at how to correctly export EC data from the system for the purpose of building import files, using the Person and Employment Export (P&E Export) Ad Hoc report.
Some things to note -:
- This is the ONLY report that should be considered for Exporting data from EC with the purposed of building import files
- This report is the ONLY report that should be cosidered when performing Data Consistency checks on employee data. No other report is helpful when troubleshooting Data Issues
- The P&E Export report does not respect Role-Based Permission or Target Population assignments, meaning this report should only be permissioned to System Administrators and not the general population. This is by design - expected behaviour
- The data returned by the P&E Export report will be in an import friendly format, meaning it can be directly used to create an import file to insert/update/delete employee's data
- You should NEVER mix column sets together using this report. This report is provided to give System Administrators access to the raw data, in order to Export for Improt purposes, it is not to be used for Metric reporting. The idea is that you create 1 report per column set to export data for that specific element/table, meaning 1 report for Job Info column set, 1 report for Compensation Info colum set and so on.
- This report does not contain Audit information - there is a separate report "Person and Employment Audit" Ad Hoc report which is used for Audit reporting
Employee Central 2.0
1. You can find the report in Analytics > Reporting > Ad Hoc Reports. To create a new Export report, click "Create New Report" -:
2. Then from the "Report Definition Type" menu, select "Person and Employment Export" and then click "Create" -:
3. Make sure to set the "Report Name" to reflect the area in which you are exporting data for, as you can re-use this report in the future. Also add a description if needed (though not required) -:
4. Then click on the "Columns" pill to enter the next step - and click on "Select Columns" to be able to select the column set you wish to export.
5. Our example here will be based on Job Information data. So we will select the column set "Employee Job Info" as we want to export Job Info data. To do this, we will select Employee Job Info column set in the left panel, and then in the middle panel, click "Select All". Then click "Done" -:
6. Next step is to click "Save" and finally click "Generate" to start start the report -:
7. The report will run in the UI. Once completed (when data is returned in the pop-up UI), then choose CSV format, which will then download the report file in CSV format -:
8. It is important that you do NOT click "Open", and instead Save the file. This is because clicking Open will open the file in a Spreadsheet application such as Excel. This will automatically clip off any leading 0's you have in any of the reported columns, which would make imports using this data a problem. To carefully open a CSV file where leading 0's are present, please follow the attached KBA - 2087490
9. Once the file is downloaded you can then go an download the related import template from Admin Center > Import Employee Data > Download Templates, and start copying the data from the P&E Export report to the related data import template, in this case Job History (Job Information).
Instead of selecting all fields in a column set when creating the report, you could select on the fields only mentioned in the import template. For example, we reported on Job Information, but this column set returns all available fields for the Job Info element - whether they are enabled in the configuration or not. You could select the fields for the Job Info column set 1 by 1 when creating the report, to ensure the Export report is in the exact same format as the import template. You can then actually import the Job Info data using the Export report, without the need for the import template.
Any saved reports may become outdated over time as your configuration evolves - be sure to update your Export reports when ever new fields are added to the configuration.
This feature can be used to check Picklist values vs their related Picklist OptionID. If you click "Configuration" after selecting the column set/fields, and then click on "Show Value ID configuration" -:
You will then be able to select when the repot will return the "Label" or the "OptionID" of the picklist value -:
You can use the Filters pill to restrict the report to return data on specific field criteria, but being an Export report you would not typically want to filter on any fields other than "user-id". This is because you would normally want to Export data for specific employee's. Click on the "Filters" option and then click "Refine Criteria" -:
Please note that when you filter on a particular column set, like we are in our example Job Information export, you would want to filter on the "user-id" field that is provided in the column set. So as per the example below, we use the user-id field in the Employee Job Info column set, and NOT the one in the Employee Information column set -:
As soon as you tick the "user-id" box the following pop-up will appear, where you can manually select 1 or more specific UserID's. Click "By My Selection" and then you can search for user-id's to filter on by typing them into the box provided. Once all of the users you wish to export have been selected, click "Done" -:
You will then see all users selected in the "Selected filters" column in the pop-up you are returned to. Click "Done" to save the filters -:
Make sure to click "Save" before clicking "Generate". Then run the report to Export the data for the specific users
Person and Employment Export P&E Export Employee central Ad Hoc Report , KBA , LOD-SF-EC , Employee Central , LOD-SF-EC-REP , Reporting Data , Problem