export the control chart in JIRA ?

[sn]

hi all,

we use JIRA Software7.3 server. 

we use control charts and we want to see the underlying data in excel or any other readable format and not manually go through each point on chart.

we are looking to extract most of the underlying information like history, task, times ect. our goal is to see the wait time Vs work time when issues move through certain statuses.

is there a way to achevie this with a JQL or any other plugin. 

thanks,

[Emre Toptancı _OBSS_]

Hello @sn , @Salvatore ,

This is an old post but since you are following this one...

There is still no export functionality for Control Chart but our team at OBSS built Time in Status app for this need with greater details and flexibility. It is available for Jira Server, Cloud, and Data Center.

Time in Status has many report types:

• Status Duration and Assignee Duration reports allow you to see how much time each issue spent on each status or assigned to each assignee. You can also combine statuses into consolidated columns to see metrics like Ticket Age, Cycle Time, or Lead Time. You can even get SLA-like metrics this way. You can calculate averages and sums of those durations grouped by issue fields you select. (For example see the total InProgress time per Epic, or average Resolution Time per issuetype).
• Status Count or Transition Count reports show how many times each status or each assignee was used. You can calculate averages and sums of those counts grouped by issue fields you select. (For example, average reopen count per issuetype).
• 4 types of Date Reports show you the first/last transition dates from/to each status.

Time in Status can display its reports on its own reporting page, issue view pages, and dashboard gadgets (as data or charts in all).

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well. It works with both Company Managed and Team Managed (next-gen) projects.

Using Time in Status you can:

• See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
• Calculate averages and sums of those durations grouped by issue fields you select. (For example, see average InProgress time per project and per issuetype.)
• Export your data as XLS, XLSX, or CSV.
• Access data via REST API. (for integrations)
• Visualize data with various chart types.
• See Time in Status reports on Jira Dashboard gadgets and on issue view pages.

https://marketplace.atlassian.com/1211756

EmreT

[Laura Ramirez]

Hi @Emre Toptancı _OBSS_ is it possible to get Standard deviation for cycle days in this app?

Best,

Laura

[Emre Toptancı _OBSS_]

Hi @Laura Ramirez

Yes, all numeric report types support average, sum, median, and standard deviation calculations.

Even better, you can group those calculations by one or more issue fields (including custom fields). For example, instead of getting an overall average, you can get averages grouped by issue type, or standard deviation grouped by component, etc. 

The following tutorial video shows an example of average calculation and grouping. It was prepared before the introduction of median and standard deviation calculation so no mention of them in the video but they are now supported too.

https://www.youtube.com/watch?v=Fv1WfZVVsPQ&list=PLozsWwplnns2J6mhEqLga7nE6VcTrwO7m

EmreT

[somethingblue]

Hi,

There is a feature request for this at JSWSERVER-4288.  Please vote on it to add impact.  Once you vote on the issue and add any relavent comments for the developers you will be automatically notified when there is an update to the ticket.

Cheers,

Branden

[sn]

thanks branden. can you suggest any plug-ins with this capability?

[Salvatore]

Any update on this one?

[Aleksandr Bogush]

I just put the instruction for export in the comments here: https://jira.atlassian.com/browse/JSWSERVER-4288

Will repeat it here:

Brian Cunningham added a comment - 02/Oct/2020 9:23 AM
People have been crying out for this for years. The opportunity to unlock a huge amount of value for it's users is being missed. The control chart is nice and all but it hides information, especially when it groups issues that were resolved on the same day.

You can hack the data from the table in Chrome by right clicking on the table, clicking Inspect. This should open up the developer tool on the righthand side. Click on the Network tab. Refresh the page. This lists all the data elements retrieved on the left hand side of the developer tools window. Scroll down to controlChart.json and you should be able to view it's contents on the preview pane on the right.

I've been able to copy this data into software that can format the Json such as Notepad ++ and extract the information I need but it is painful.

After you copy the contents of the json, do the following:

1) Open https://www.db-fiddle.com/

2) Choose Postgres 15 as a database.

3) In Schema SQL put this:

Create table chart as select  $$ 
-- put your json here.
$$::json 

IMPORTANT! I don't know if db-fiddle saves the data that it processes. You have ticket keys and summaries within that json, so be aware of that

4) Then in Query SQL put this:

with fieldnames as 
(
  select json_array_elements(json -> 'columns')->> 'name' as name from chart
),
fields as (
  select row_number() over () as number, name from fieldnames
),
issuesjson as (
  select json_array_elements(json -> 'issues') as issue from chart
),
issuesflat as (
  select issue ->> 'key' as key,
         json_array_elements_text(issue -> 'totalTime')::numeric as totalTime,
         json_array_elements_text(issue -> 'workingTime')::numeric as workingTime,
         json_array_elements_text(issue -> 'leaveTimes')::numeric as leaveTime
  from issuesjson
),


issues as (
  select *,
         row_number() over (partition by key) as number
  from issuesflat
)


select i.key as "Key", 
       f.name as "Status",
       round(i.totaltime / 60 / 60 / 1000,3) as "Total Time, Hours",
       round(i.workingtime / 60 / 60 / 1000,3) as "Working Time, Hours",
       coalesce(TO_CHAR(to_timestamp(nullif(i.leavetime,-1) / 1000), 'YYYY-MM-DD HH:MI:SS'),'') as "Last Leave Time"
  from issues i
  join fields f on f.number = i.number


order by i.key, i.number 

5) Click "Run"

6) Then open your DevTools/Inspector and find the table tag

7) Copy its contents as HTML

8) Paste it to MS Excel On your Desktop