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

Question

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 · Answer

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?

Oceania · Answer

@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

Ajay Verma_xabu0g · Answer

@Oceania

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