How to display Person custom field values as names (not values)

[Tschilar, Stacey]

Hello - brand new to Atlassian Analytics. I'm sure this has been asked before but I'm probably not searching for the right terms.

I have a custom field ("-Escalation", I did not come up with that) that is a People field that allows multiple entries. I would like to display some kind of chart with this information but listing the People names, not this value/ID from the background. How can I do that? 

[Jessie Turpin · Accepted solution]

Hi there! If your organization is a managed account then the Account table of the Atlassian Data Lake schema would hold the names of the users selected in your People custom field. 

To add those names to your query, you would need to add a second visual mode query with the Account ID and Name columns from the Account table. Then join your two queries together on the Value and Account ID columns. 

Note that since the users account IDs are stored in brackets in the Value column, and the account ID in the Account table is not in brackets, you'll need to remove the brackets in the Value column. For any rows in the Value column that have multiple account IDs selected, you can use an approach similar to the above Knowledge Base article to extract each account ID. 

If you'd like one of our support team to help you dig into this further please don't hesitate to open a support request!

[Tschilar, Stacey]

Hi, I've tried doing this on my own and haven't been successful, do you have an example of the queries or SQL you can post that might work?  I came up with a formula to remove the brackets, but when I try to join on the Account ID, I am just getting a blank result query.

[Jessie Turpin]

Hey Stacey! If I had to take a guess about why you're getting no results after merging the two queries together, I'd say it's probably due to the rows where two or more users have been selected in your field. This would be because the Account ID column from the Account table is expecting only one account ID, but some rows in the first query have multiple IDs.

It'll be hard to determine if that's the exact cause though, so if you could create a support ticket and select 'Technical issues and bugs' and 'Atlassian Analytics' as the affected product, then someone from my team would be able to help you further!