developer , reviewer 1, reviewer 2 and tester

[CESAR DALDOSSO]

I have an automation that feeds some multi picker user fields : developer, reviewer 1,reviewer 2 and tester . These fields are automatically fed by an automation , that feeds this fields depending on the user that changed the status.

Now I want to have some measures like who many items an user worked on (develop, test, reviews) .

These fields (developer, reviewer 1, reviewer2 , tester) collects the accound id , so how can I show their names  ?

I know I would be able to use the case, but I would prefer to have it this being done automatically

[Mathew Lederman]

Your question isn't super clear, but to count how many times an item was worked on, your best bet would be to simply increment a counter at the same time as you update the field

{{#increment}}{{issue.customfield_XXXXX}}{{/}}

 To get the users name, use displayName as documented here: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-users/#--Custom-field-- or like this {{issue.customfield_XXXXX.displayName}}

[CESAR DALDOSSO]

The automation is ready , I was able to implement it.

Now I want on Atlassian Analytics the data. 

The problem is that the data returns like 

And I need to change them to the name.

For instance , on this case , I find that the developer is a name

However on AA it will return something like "712020:9c552167-7648-4b84-ba3b-ce0ae9201512b

I want the data on AA to return the name, not the account id . I tryed including the Name from the Account Table however as you can see this does not works, it is retrieving the data wrong

and also, 

[CESAR DALDOSSO]

This is what I did

WITH `KSO Fields` AS (

    SELECT *

    FROM (

        SELECT 

            `jira_issue`.`issue_key` AS `Issue key`,

            `jira_issue_field`.`Name`,

            `jira_issue_field`.`Value`

        FROM `jira_issue`

        INNER JOIN `jira_issue_field` 

            ON `jira_issue_field`.`issue_id` = `jira_issue`.`issue_id`

        WHERE `jira_issue_field`.`Name` IN (

            'Testing Defect Leakage Reason (parent)',

            'Testing Defect Leakage Reason (child)',

            'Incident Identified During DL (parent)',

            'Incident Identified During DL (child)',

            'Original Ticket Number',

            'Developer',

            'Reviewer 1',

            'Reviewer 2',

            'Tester',

            'Pair Developer'

        )

    ) PIVOT (

        MAX(`Value`) FOR `Name` IN (

            'Testing Defect Leakage Reason (parent)',

            'Testing Defect Leakage Reason (child)',

            'Incident Identified During DL (parent)',

            'Incident Identified During DL (child)',

            'Original Ticket Number',

            'Developer',

            'Reviewer 1',

            'Reviewer 2',

            'Tester',

            'Pair Developer'

        )

    )

)

SELECT 

    `Account`.`name` AS `Name`,

    COUNT(DISTINCT `Work item`.`issue_key`) AS `Total Work Items`

FROM `KSO Fields`

INNER JOIN `jira_issue` AS `Work item` 

    ON `KSO Fields`.`Issue key` = `Work item`.`issue_key`

INNER JOIN `atlassian_team` AS `Team` 

    ON `Work item`.`team_id` = `Team`.`team_id`

INNER JOIN `jira_issue_live` 

    ON `jira_issue_live`.`team_id` = `Team`.`team_id`

INNER JOIN `jira_project` AS `Project` 

    ON `Work item`.`project_id` = `Project`.`project_id`

INNER JOIN `account` AS `Account`

    ON `KSO Fields`.`Developer` = `Account`.`account_id`

WHERE 

    {KSO_ID.IN('`Project`.`project_id`')}

    AND `jira_issue_live`.`status_category` = 'Done'

    AND `jira_issue_live`.`status` != 'Cancelled'

    AND {TEAM_ANALYTCS.IN('`Team`.`name`')}

    AND `Work item`.`resolution_at` > {CALENDAR.START}

AND {ISSUE_TYPES.IN('`Work item`.`issue_type`')}

    AND `KSO Fields`.`Developer` IS NOT NULL

GROUP BY `Account`.`name`

ORDER BY `Total Work Items` ASC, `Account`.`name` ASC

LIMIT 1000;

and this was my result , (of course I've removed the complete names on the prinscreen)

what I would like to do is just be able to connect the account id to the Names

In resume:

1. I have a table that connects work itens with the fields developer, reviewer 1, reviewer 2 and tester

2. The values of these fields came with the same value from account id

3. There's a table (account ) where I have it the account id and the name

4. I was able to generate this report, but what I really want is to be able to do this using query visual mode on Atlassian analytics