It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to display JIRA "Time In Source Status" in PowerBI Dashboard Edited

Hi,

I was running a business request that we want to track how much time spent on each status for each issue .

In JIRA page, it actually will show "Time In Source Status" under "Transitions" tab (see below screenshot). However, I cannot find the "Time In Source Status" filed. Instead, JIRA data within PowerBI only shows "updated" and "statu.name". Those two variables are dynamic and the field value updates once status changes(See below screenshot).

Do you guys know what I can do to get each status time for each issue? I have to develop this in PowerBI instead of any Marketplace app. Ultimate goal is to break down the time for each status and look for business process improvement opportunity. I really appreciate your insights.

 

JIRA screenshot.PNG

PowerBI data source.PNG

 

 

 

 

1 answer

@Jira-Viv The "Time in Source Status" value comes from "Transitions" module of "JIRA Suite Utilities" plugin. It is not possible to extract and use this field in reports.

Please refer this similar community post for getting more information and suggestions on how you can get Time in Source Status data for issues, by using a 3rd party add-on for JIRA Cloud:  Time in Status for Jira Cloud

 

https://community.atlassian.com/t5/Jira-questions/Time-In-Source-status-to-be-displayed-in-report/qaq-p/215848

 

Hope it helps !

Hi @Taranjeet Singh -Cprime- 

I really appreciate your reply. Time in Status for Jira Cloud looks very nice however it has the cost and our organization is not going to pay. So I am thinking if there is a way to use PowerBI to solve this problem.

 

Please let me know if you come up with anything else. Thank you in advance!

Oceania I'm New Here Sep 17, 2019

@Jira-Viv Not sure if you've found an answer yet, but this worked for me:

  1. Expand the following column as such:
    • changelog > histories > expand to new rows > created, items
      • items > expand to new rows> field, fromString, toString > 
    • fields > created > status
      • status > name
  2. Filter the changelog.histories.items.field to only show the value "status"
  3. Remove duplicates in the whole table
  4. Change the data type of all dates to date formats
  5. Rename your fields as such:
    • changelog.histories.items.field to Changed Field
    • changelog.histories.created to Date Change Created
    • changelog.histories.items.fromString to From
    • changelog.histories.items.fromString to To
    • fields.created to Issue Created Date
    • fields.status to Current Status
  6. Click onMerge Queries as New
  7. Select your query/table as both tables to merge
  8. Choose the columns to match the table on and click OK
    • In the first table choose the Key and From columns
    • In the second table choose the Key and To columns
    • To Select multiple tables press Shift or Ctrl when you click
  9. A newly merged table should appear, alongside a column at the end with the name of your query/table e.g. Issues Table
  10. Expand the column at the end, choosing To and Date Change Created
  11. Rename yourquerytable.Date Change Created to Source Status
  12. Select the To and Date Change Created columns and right click to Remove Duplicates
  13. Add a custom column and write the formula:
    • [Date Change Created] - [yourquerytable.Date Change Created]
  14.  Rename the custom column - which is then the duration the issue is in the source status for

 

Here's another way to achieve a similar thing https://community.atlassian.com/t5/Jira-questions/Time-spend-in-a-Status-MSSQL-extract-for-PowerBi/qaq-p/580964

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

275 views 1 2
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you