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

[Jira-Viv]

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.

[Marc Straubinger]

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.

I only have one question. WHY?

Why is it not possible to extract this info from my JIRA which is displaying that information already. Why do we need to use plugins to get that?

I cannot use any plugins in my company as they refuse to install these. So how am I supposed to gather this crucial information for my report?
Is there no other way to get this info with an api call or a jql-query?

[Taranjeet Singh]

@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 !

[Jira-Viv]

Hi @Taranjeet Singh 

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!

[Ajay Verma]

@Oceania 

Hi ,

I need the same requirement to build the same kind of report in Qliksense . could you please guide me which are the tables of Jira required to fulfill the requirement . or appreciate if you can share the sql query .

Thanks

AJay 

[Ajay Verma]

@Oceania 

But I dont find changelog table in my Jira tables . could you please confirm about the changelog exact table name ?

[Oceania]

@Ajay Verma Not sure what the table name is, as I didn’t really have to know the table to use the api. What are the available options?

Have a look at this link https://community.atlassian.com/t5/Jira-Core-questions/In-which-database-table-we-can-find-the-history-of-an-JIRA-issue/qaq-p/120925?tempId=eyJvaWRjX2NvbnNlbnRfbGFuZ3VhZ2VfdmVyc2lvbiI6IjIuMCIsIm9pZGNfY29uc2VudF9ncmFudGVkX2F0IjoxNTg1ODYzODYzMDg5fQ%3D%3D