How Do I Include My Custom Fields in Reporting?

Custom fields are also known as “additional fields”

Problem

Custom fields are currently handled as what is known as a “key value pair”. This behaviour creates 2 columns.

• The field key

  • e.g Region, department, jobrole

• The Value

  • e.g North West, Sales, Senior Sales Representative

This means that when you add these custom field values to a report all rows duplicate for each unique item.

For example if you have one user who has 5 items of assigned learning and 5 additional fields when you pull the columns into the table via explore these 5 rows become 25 rows

Example:

• Matthew King has 4 assigned learning items

• on explore “Add” I bring in additional field key and additional field value

  

• 4 rows now becomes 16

  

• This is because Matthew King has 4 additional custom fields via the platform

  • Location

  • Team

  • Department

  • Country

• You can see below each piece of learning is repeated 4 times

Resolutions

This is not our permanent structure for handling custom fields but due to the complexity of customers all having varying numbers and names of custom fields this is the current solution for all customers to be able to segment data by any business field.

The dashboards are pre filtered to the users relevant to you and therefore needing to include all custom field columns should not be necessary, however in circumstances you need to include custom fields here are the recommended resolutions.

Bring in all of a business area for example all countries

1. Filter to a single “key” but leave “value” as unselected

   1. e.g Country

   

   

2. Via explore “Add” additional field key and value

   

3. You will now see every user and their assigned learning remain on single rows but include their country including all countries

   

Isolate a single business area such as country “Australia”

1. Filter to a single “key” but leave “value” as unselected

   1. e.g Country

   

   

2. Via explore “Add” additional field key and value

   

3. You will now see every user and their assigned learning remain on single rows but include their country and only for the country you are keen to delve deeper into

Export all additional custom fields and pivot them into columns via Excel

1. On the table you wish to contain your additional custom fields, Via explore “Add” additional field key and value

   

2. Table now contains all additional custom fields for all users

   

3. Using the 3 dots in the top right corner, download as XLSX (this is important, CSV we cannot pivot)

   

4. Open the XLSX and click onto any cell containing your data, use CTRL and “T” to open box to turn the data into a table. Ensure you bring in the headers

   

5. Rename the table to something relevant and select “Summarize with PivotTable”

   

6. On the pop up ensure it is referencing your newly named table and tick the box at the bottom that says “Add this data to the data model”

   

7. On “Design” of pivot table in selection pane we want to do the following

   1. Report Layout “Tabular”

      

   2. Grand Totals “Off for rows and columns”

      

   3. Subtotals “Do not show subtitles”

      

8. Now we begin building our pivot table, for this dataset I will bring in User Name, Content Title, Status, Available Date and Due Date. Do this by dragging the fields to the rows box at the bottom

   

   1. Note, when doing this for dates excel defaults to bring the full hierarchy in, just drag year, quarter and month out of rows area to remove them

      

9. We now have our structure, we need to create one formula to bring in our custom fields

   1. Right click on your table in the “PivotTable Fields” window pane and select “Add Measure”

      

   2. Ensure the following is in your formula box: =CONCATENATEX(TABLENAME,[Additional Field Value],",") Name your measure.

      1. for example mine is =CONCATENATEX(Assignedlearning,[Additional Field Value],",") and I have named it “Custom Field”

         

10. Pull “Additional Field Key” into Columns and your new measure into Values

    

11. You will now have a list of all users and their custom field row by row, including if a user has no custom fields they can be located this way.

    

12. If you need this data in a csv it would be best to copy and paste the table to a new file and save as csv