If you want to export report data in a format other than CSV you do this using a Custom export format.
The format allows you to enter:
- the names of the fields you want to export
- the order of the fields
- any extra text or field delimiters
- padding on the fields
Being able to control the format is essential when using systems like SAP as they require very specific file formats.
Firstly, you will need to create a custom report by going to the Reports tab, then clicking the ‘Create a report’ button. Choose your report, specify your report criteria and click ‘Run this report’.
Once the report has been generated, click on the ‘Schedule email for this report’ link on the right.
Now, change the ‘Report Format’ drop-down from CSV to ‘Custom’ and enter your custom string format in the box (see below for available fields and how to enter them correctly).
What are fields are available
- [CourseName] – The title of a course
- [CourseCode] – The reference code of a course
- [StartDate] – The date that a course or module was started (in the format DDMMYYYY)
- [FinishDate] – The date that a course or module was completed (in the format DDMMYYYY)
- [CompliantTillDate] – The date that a learner is compliant until for a course (in the format YYYYMMDD)
- [Compliant]- If the learner is compliant for that course
- [Completed] – True/False indicator if a course or module is complete
- [PercentageComplete] – The percentange of the modules in a course that are complete
- [UpToDate] – If the course is complete this indicates if the learner has completed the latest version of the course
- [FirstName] – Learners first name
- [LastName] – Learners last name
- [FullName] – Learners first and last name
- [Username] – Learners username
- [CompanyName] – The users company
- [JobTitle] – The users job title
- [TeamName] – The name of a team (see note below)
- [CustomField1] – Custom field data
- [CustomField2] – Custom field data
- [CustomField3] – Custom field data
- [CustomField4] – Custom field data
- [CustomField5] – Custom field data
- [CustomField6] – Custom field data
- [CustomField7] – Custom field data
- [CustomField8] – Custom field data
- [CustomField9] – Custom field data
- [CustomField10] – Custom field data
- [CourseCustomField1] – Course Custom field data
- [CourseCustomField2] – Course Custom field data
- [CourseCustomField3] – Course Custom field data
- [CourseCustomField4] – Course Custom field data
- [CourseCustomField5] – Course Custom field data
- [CourseCustomField6] – Course Custom field data
- [CourseCustomField7] – Course Custom field data
- [CourseCustomField8] – Course Custom field data
- [CourseCustomField9] – Course Custom field data
- [CourseCustomField10] – Course Custom field data
Note: The fields are all case sensitive and must include the [ ] brackets.
Note: Depending on the report ran, not all fields will populate. For example, if the report is a User results report, [TeamName] will not populate due to the unlimited amount of data that this field may potentially contain.
Basic sample format
This is a sample format for exporting the data from a course report and we have delimited the fields using a $.
The result would be
John Doefirstname.lastname@example.org$Sample Course 101$True$2011-01-25
Adding field padding to the export
If you system requires fields to be padded with a particular character and to a certain number of spaces you can use an advanced export format.
eg. The following would pad the CourseCode field to 8 places prefixed with 0′s
So if the course code was 555 it would be exported as 00000555
Creating a Custom csv file
Like with the Basic sample format above, this is a sample format for exporting the data from a course report. However, to end up with a csv you will need to to delimit the fields using commas (,) instead of $.
The result would be:
John Doe,email@example.com,Sample Course 101,True,20110125
The file will be downloaded as a .txt file. If the file type must be a .csv, open the file in Notepad, select Save As…, rename the file with .csv at the end and click save.