Display recorded values on one row in your reports

Although you can configure your Helm CONNECT reports to display the actual values recorded in a checklist, audit, or form, they'll appear in separate rows which may not be the format you want. This article will show you how to use Microsoft Excel to transform your data so the values appear on one row for each individual audit, form, or checklist.

You can use this procedure to transform exported reports for the following Helm CONNECT data sources:

• Audits
• Forms
• Inspection Checklists
• Inventory Checklists
• Maintenance Checklists

Generate and export the required report

The first thing you'll do is create a report that includes your key fields, and then export it from Helm CONNECT.

To generate and export the required report

1. In Reports > Reports, click New Report or select an existing report.
2. If you create a new report, in the Name field, enter a name.
3. Set the required Date Range for the report.
4. Use the filters to narrow the report to display only the information you need.
5. From the Data Source list, select the required report type.

   

6. From the list on the left, select the fields you want to include in the report. See the "Tips for making your report" section below.
7. Click Save Changes.
8. Copy the CSV connection string value or click Export to CSV to export the report information.

Tips for making your report

For clarity, we recommend that you limit the number of columns in your report and arrange the columns in the following order, from left to right:

• Asset Name
• Date (whichever one you want)
• External Number (if used)
• Any other items from the main section of the list that makes sense for the report
• Item Values fields

We also suggest that, in the Item Values group, you select only the items indicated below. If you select other items, the exported report may show more "null" values and require additional filtering.

Transform the exported report 

Once you've generated and exported your report, you'll use the Power Query tool in Microsoft Excel to transform the data in the exported report.

To transform the exported report 

1. In Microsoft Excel, click the Data menu.

   

2. Select one of the following options:
   • If you exported the report from Helm CONNECT to a CSV file, click From Text/CSV, locate the exported file, then click Import. When the preview window appears, click Transform Data.

     

   • If you're using the CSV connection string, click From Web. In the window that appears, paste the CSV connection string value, then click OK.

     

3. In the Power Query Editor window, click the Description header (the label for this header might be something different).
4. On the Transform toolbar, click Pivot Column.
5. In the Pivot Column window do the following:
   1. In the Value Column list, select the Value field (for Inventory Checklists this will be the Count field).
   2. Expand Advanced options. From the Aggregate Value Function list, select Don't Aggregate.
   3. Click OK.

      

      Each row now displays the information for one completed item (checklist, audit, or form) for one asset.

6. On the Home toolbar, click Close and Load to display the transformed file in Excel.

   

7. (Optional) Use Excel's filters to further refine the display of information.
8. Save the transformed file.