Hi,
We want to understand how much users are logging time in different issues on same time during a month. So for example when ticket 1 logging lasted from 10:00 to 11:00 and ticket 2 from 10:30 to 11:30, then the duplicated logged time was 30 minutes. The report should look like this:
Is this kind report possible?
Br,
Toomas Tähe
Hi @toomastahe
Do you use native Jira worklogs or Tempo app in your organization?
Also, what version of Jira do you use? If Data Center, what version of Jira and what version of Tempo (if tempo)
Depending on these answers, there may be slightly different possibilities and approaches for retrieving details about user worklogs for further calculations.
Martins / eazyBI support
We are using native Jira worklogs. Currently we have Jira DC version 10.3.12.
Br,
Toomas
Thanks @toomastahe
I tried to create something similar but I face a challenge to understand how to calculate the overlapping minutes, if overlapping time is in more than 2 worklogs.
See the picture below.
What would be the expected result in overlapping minutes, assuming all 3 worklogs are overlapping for the period 10:30 to 11:00?
And there were another 12 minutes (from 10:18 to 10:30) where it overlapped between just two worklogs and another 30 minutes (from 11:00 to 11:30) where it overlapped between just two worklogs
In case of 3 or more overlapping worklogs it should sum up the overlaping parts. If we take the pictured 3 workflows as examples, then linear worked time is 10:00-12:18 (138min), but there is logged 4h (240min) of work. So the overlapping part together is 102min. 60min from worklog 39001 as it overlaps fully with worklog 39100. Plus 42min from worklog 39100, which overlaps also with worklog 39000 from 10:18 to 11:00 or vice versa.
Hope this clears up a bit the need.
Basically one cannot work longer than is the actual time span of the given period.
Br,
Toomas
So far, I have found a way to calculate the overlapping time on “All issues” or “Project” level, but it is not possible to drill down to worklog or issue level to validate by tickets or worklogs, Which means it won’t be possible to drill through issue or worklog to check the that time by issue, as it calculates across issues.
Perhaps you can validate report using measure “Issues with overlapping worklog counter”.
And it is very complex calculation, it can take a lot of time to run it for many users and many periods in your report.
To make it work I had to add custom hierarchy in “Worklog” dimension to group worklogs by property “Time Spent Seconds”.
Please check this approach by importing my report definition:
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "test1",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Hours spent]","[Measures].[worklog started date]","[Measures].[worklog timespent ]","[Measures].[Worklog ended on]","[Measures].[Issues with overlapping worklog counter]","[Measures].[calculated overlapping time (minutes)]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[Last 6 months]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time].[Month])","members":[],"bookmarked_members":[{"depth":3,"name":"Jan 2026","full_name":"[Time].[2026].[Q1 2026].[Jan 2026]","drillable":true},{"depth":3,"name":"Feb 2026","full_name":"[Time].[2026].[Q1 2026].[Feb 2026]","drillable":true}]},{"name":"Logged by","selected_set":["[Logged by].[User].Members"],"members":[],"bookmarked_members":[]},{"name":"Worklog","selected_set":["[Worklog].[All Worklogs]"],"members":[],"bookmarked_members":[]},{"name":"Issue","selected_set":["[Issue].[All Issues]"],"members":[],"bookmarked_members":[]}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Project].[All Projects]"]},{"name":"Priority","selected_set":["[Priority].[All Priorities]"],"members":[{"depth":0,"name":"All Priorities","full_name":"[Priority].[All Priorities]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Priority].[All Priorities]"]},{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[{"depth":0,"name":"All Issue Types","full_name":"[Issue Type].[All Issue Types]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Issue Type].[All Issue Types]"]},{"name":"Time","duplicate":true,"selected_set":["[Time].[All Times]","[Time].[Last 12 months]","[Time].[Last 6 months]"],"members":[{"depth":0,"name":"Last 6 months","full_name":"[Time].[Last 6 months]","calculated":true,"drillable":true,"dimension":"Time"}],"bookmarked_members":[{"depth":3,"name":"Jan 2026","full_name":"[Time].[2026].[Q1 2026].[Jan 2026]","drillable":true},{"depth":3,"name":"Feb 2026","full_name":"[Time].[2026].[Q1 2026].[Feb 2026]","drillable":true}],"current_page_members":["[Time].[Last 6 months]"]}]},"options":{"nonempty":true},"view":{"current":"table","maximized":false,"table":{"show_key":["Issue"]}},"calculated_members":[]}
} ],
"calculated_members": [{"dimension":"Time","name":"Last 12 months","format_string":"","formula":"Aggregate(\n [Time].[Month].DateMembersBetween('12 months ago', 'today')\n)"},{"name":"Last 6 months","dimension":"Time","formula":"Aggregate(\n [Time].[Month].DateMembersBetween('6 months ago', 'today')\n)","format_string":""},{"name":"worklog started date","dimension":"Measures","formula":"[Worklog].CurrentHierarchyMember.get('Started at')","format_string":"yyyy-mm-dd hh:nn:ss"},{"name":"worklog timespent ","dimension":"Measures","formula":"[Worklog].CurrentHierarchyMember.get('Time Spent Seconds')/60","format_string":"DaysHoursMinutesFormatter"},{"name":"Worklog started on timestamp","dimension":"Measures","formula":"Cache(CASE WHEN\nNot IsEmpty([Worklog].CurrentHierarchyMember.get('Started at'))\nTHEN\nDateToTimestamp(\n [Worklog].CurrentHierarchyMember.get('Started at')\n)\nEND)\n","format_string":"StringFormatter"},{"name":"Worklog ended on timestamp","dimension":"Measures","formula":"Cache(CASE WHEN\nNot IsEmpty([Measures].[worklog started date])\nTHEN\nDatetoTimestamp(\n\nDateAdd(\n'n', \n[Worklog].CurrentHierarchyMember.get('Time Spent Seconds')/60,\nDateParse([Worklog].CurrentHierarchyMember.get('Started at')))\n)\nEND\n)","format_string":"StringFormatter"},{"name":"Worklog ended on","dimension":"Measures","formula":"DateAdd(\n'n', \n[Worklog].CurrentHierarchyMember.get('Time Spent Seconds')/60,\nDateParse([Worklog].CurrentHierarchyMember.get('Started at'))\n)","format_string":"yyyy-mm-dd hh:nn:ss"},{"name":"generate of all worklogs","dimension":"Measures","formula":"Cache(\n Generate(\n Order(\n Filter(\n [Worklog].[Worklog].Members,\n \n-- Descendants(\n-- [Worklog].CurrentMember,\n-- [Worklog].[Worklog]\n-- ),\n DateInPeriod(\n [Worklog].CurrentMember.get('Started at'),\n [Time].CurrentHierarchyMember\n )\n AND\n (\n [Measures].[Hours spent],\n [Worklog.Time Spent Seconds].DefaultMember,\n [Issue].DefaultMember\n )\u003e0\n ),\n [Measures].[Worklog started on timestamp],\n BASC\n ),\n Cast([Worklog].CurrentHierarchyMember.key as string),\n \",\"\n )\n)","format_string":""},{"name":"Overlapping worklog counter","dimension":"Measures","formula":"Cache(Sum(\n [Worklog.Time Spent Seconds].[Worklog].GetMembersByKeys(\n [Measures].[generate of all worklogs]\n ),\n Sum(\n Filter(\n [Worklog].[Worklog].GetMembersByKeys(\n [Measures].[generate of all worklogs]\n ),\n DateToTimestamp([Worklog].CurrentMember.get('Started at'))\n \u003e\n DateToTimestamp([Worklog.Time Spent Seconds].CurrentMember.get('Started at'))\n AND\n DatetoTimestamp(\n DateAdd(\n 'n', \n [Worklog].CurrentMember.get('Time Spent Seconds')/60,\n DateParse([Worklog].CurrentMember.get('Started at')))\n )\n \u003e\n DateToTimestamp([Worklog.Time Spent Seconds].CurrentMember.get('Started at'))\n AND\n (\n [Measures].[Hours spent],\n [Worklog].DefaultMember--,\n --[Issue].DefaultMember\n )\u003e0\n ),\n 1\n )\n))\n\n","format_string":"#,##0"},{"name":"calculated overlapping time (minutes)","dimension":"Measures","formula":"-- annotations.disable_drill_through=true \n\nSUM(\n Filter(\n DescendantsSet(\n [Time].CurrentMember,\n [Time].[Day]\n ),\n [Measures].[Hours spent]\u003e0\n AND\n [Measures].[Issues with overlapping worklog counter]\u003e0\n ),\n CASE WHEN\n [Issue].CurrentMember.level.name \u003c\u003e \"Issue\"\n AND\n [Worklog].CurrentMember.level.name \u003c\u003e \"Worklog\"\n AND\n [Measures].[Issues with overlapping worklog counter]\u003e0\n AND\n DateDiffMinutes(\n [Measures].[earliest worklog start time],\n [Measures].[latest worklog end date]\n )\u003e0\n THEN\n Sum(\n Filter(\n Descendants(\n [Worklog].CurrentMember,\n [Worklog].[Worklog]\n ),\n [Measures].[Hours spent]\u003e0\n ),\n [Measures].[Hours spent]*60\n )\n -\n DateDiffMinutes(\n [Measures].[earliest worklog start time],\n [Measures].[latest worklog end date]\n )\n\n END\n\n)","format_string":"DaysHoursMinutesFormatter"},{"name":"latest worklog end date","dimension":"Measures","formula":"Cache(\n Timestamptodate((\n Order(\n Filter(\n [Worklog].[Worklog].Members,\n DateInPeriod(\n [Worklog].CurrentMember.get('Started at'),\n [Time].CurrentHierarchyMember\n )\n AND\n (\n [Measures].[Hours spent],\n [Worklog.Time Spent Seconds].DefaultMember\n )\u003e0\n ),\n [Measures].[Worklog ended on timestamp],\n BDESC\n ).item(0),\n [Measures].[Worklog ended on timestamp]\n ))\n\n)","format_string":"yyyy-mm-dd hh:nn:ss"},{"name":"earliest worklog start time","dimension":"Measures","formula":"Cache(\n Timestamptodate((\n Order(\n Filter(\n [Worklog].[Worklog].Members,\n DateInPeriod(\n [Worklog].CurrentMember.get('Started at'),\n [Time].CurrentHierarchyMember\n )\n AND\n (\n [Measures].[Hours spent],\n [Worklog.Time Spent Seconds].DefaultMember\n )\u003e0\n ),\n [Measures].[Worklog started on timestamp],\n BASC\n ).item(0),\n [Measures].[Worklog started on timestamp]\n ))\n\n)","format_string":"yyyy-mm-dd hh:nn:ss"},{"name":"Issues with overlapping worklog counter","dimension":"Measures","formula":"Cache(CASE WHEN\n[Measures].[Hours spent]\u003e0\nTHEN\nSum(\n Descendants(\n [Issue].CurrentMember,\n [Issue].[Issue]\n ),\n CASE WHEN\n [Measures].[Hours spent]\u003e0\n AND\n ([Measures].[Overlapping worklog counter],\n [Issue].DefaultMember\n )\u003e0\n THEN\n 1\n END\n)\nEND\n)","format_string":"#,##0"}]
}
Martins / eazyBI support