User attributes (previously known as custom fields) allow you to segment and filter reporting data by your organisation's unique attributes such as region, department, or job role. However, because user attributes are structured as key-value pairs, they require specific handling when adding them to reports to avoid row duplication.

This guide explains how user attributes behave in reports and provides solutions for working with them effectively.

Problem

User attributes are currently handled as what is known as a "key-value pair." This behaviour creates two columns:

• The field key — e.g. Region, Department, Job Role

• The value — e.g. North West, Sales, Senior Sales Representative

This means that when you add these user attribute 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:

• User 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 user 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 user attributes, but due to the complexity of customers all having varying numbers and names of user attributes, this is the current solution for all customers to segment data by any business field.

The dashboards are pre-filtered to the users relevant to you, so including all user attribute columns should not usually be necessary. However, in circumstances where you need to include user attributes, 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" unselected (e.g. Country).

   

   

2. Via Explore > Add, bring in Additional Field Key and Additional Field Value.

   

3. You'll now see every user and their assigned learning remain on single rows, but including their country across 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, bring in Additional Field Key and Additional Field Value.

   

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

Export all user attributes and pivot them into columns via Excel

1. On the table you wish to contain your user attributes, via Explore > Add, bring in Additional Field Key and Additional Field Value.

   

2. The table now contains all user attributes for all users.

   

3. Using the three dots in the top-right corner, download as XLSX (CSV cannot be pivoted).

   

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 user attributes row by row, including users with no user attributes.

    

    Note:

    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.