An approach for fixing delimiter issues in LMS CSV reports using Plateau Report Designer

Overview:

Currently comma separated values files (CSV) in the Learning Management System (LMS) can cause issues and require additional formatting for countries that do not use a comma as their list separator. Many countries outside the US use a semicolon (;) as their list separator. This means that when the CSV is initially interpreted by their version of excel, with the regional settings of their computer, the best case is users have to go through an extra step of doing a text to column with a comma delimiter to use the report. The worst case is an unusable report because of a character that causes the text to columns to malfunction, which misaligns all the data. It is the case of the unusable report that led to the research and eventual solution found for this post.

Items Needed for Solution:

• Access to Export and Import Reports from your LMS instance.
• Latest version of Plateau Report Designer (PRD) and a basic working knowledge of the program.

Note: You do not need to be an expert at PRD to apply this fix to reports. There is no SQL involved in the solution. You not need JDBC access to the database you are fixing since there is no SQL to test. You will simply be able to import the .ZIP PRD file and run the report to verify the solution works as intended. If you need some basic information or want to become familiar with the Plateau Report Designer, there is an article here.

Actions:

Choose the CSV report you want to export from your instance. We are going to export the Learning History (CSV) for our example. You will navigate to the Learning Admin -> Reports and find the report you want to export. For the purposes of the example of this solution the Learning History (CSV) report will be used.

Note: Make sure you have changed the default file name to something new before you open in PRD. This will help prevent your from importing your changes over the standard report in your instance.

If you are familiar with custom reporting I will outline the steps broadly here. You may not need the more in-depth walk through provided in this section below and you can skip to the examples after reviewing these steps:

1. Export the report, rename it and load into PRD.
2. Insert a header row above the first row of labels.
3. Merge all of the header row into one large box/column.
4. Insert a label into the new large header box and name it sep=
5. Save your report and import it as a new custom report for testing.

The first step is covered above with the choice and picture of the Learning History (CSV) report chosen for the example and the export button next to it is highlighted.

Next you will want to open the report in your PRD tool. Go into the outline view (red 1 below) and click inside a cell so you can see all of the table row selectors appear (red 2 below.)

You are going to select the first rows’ selector to highlight the entire row. Then you will left click and go down to Insert -> Row -> Above (pictured below.

This will add a header row above the row you selected. Make sure it is a header row and not a detail row. It should look like the following image:

Next you will want to select the first box of the header row and slide over all the way to the last column of the header row and hold shift and select the last box. Once this is done the row will be highlighted in blue (Note: This is not the same if you try to use the entire row selector. You must select the first box and last while holding shift before you click on the last box.) If you have done the selection correctly you will see a merge option (not grayed out/pictured below) when you right click in the boxes you have highlighted, you want all of these boxes merged into one large box.

Once you have the single header box, right click inside it and go to Insert -> Label.

You then need to enter the name of the label which should be sep=