briancunning 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
Suggestion
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:
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:
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