Show number of open issues according to whether they are in or out of the 5-working-day SLA

I’ve tried to build a report showing a weekly breakdown of the number of open issues in and out of our 5-working-day SLA using the built-in SLA Breached member. However there is a discrepancy between the number of this week’s current open issues within SLA shown by EazyBI (61) compared to a simple JQL query (43),

JQL query to show issues still within SLA:
project = MYPROJECT AND status in ("In Progress", New, "On Hold", "In Review") AND "SLA" != breached()

Please could someone provide some information as to why there might be a discrepancy and how to fix it?

If not, is there a way to create a calculated measure which could show issues which are not older than 5 working days, and those which are?

Thanks for your help!

Update: the discrepancy on EazyBI looks to relate to issues which have the status ‘New’ and are already out of the 5–working-day SLA. For some reason, they are still considered as ‘Not Breached’ on EazyBI until they are moved into ‘In Progress’; then they are considered as ‘Breached’.

Can this be resolved?

Hi @general.waste,

You have described the Jira side and JQL filter. Could you share the report definition of the eazyBI report you use for comparing data?
Here is how to get the report definition: Export and import report definitions. Note definition contains only information on report layout (which dimensions and Measures are picked for report) but does not contain report data you see in the report cells.

Best,
Zane / support@eazyBI.com

Hey @zane.baranovska, thanks for your reply!

The report definition is below.

In the meantime, I think I may have resolved this issue. In the ‘Source Data’ section, I now also import the progress cycle for any issues with the ‘New’ status category (as well as those In Progress). It seems that this now imports the changes to the SLA (ie. breached/not breached) even when the issue remains in the New status.

I would be grateful if you could confirm whether this is the solution!

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Open issues by SLA (Breached / Not breached)",
     "result_view": "bar_chart",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Open issues]"],"members":[]},{"name":"SLA Breached","selected_set":["[SLA Breached].[SLA Breached].Members"],"members":[],"bookmarked_members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[Last 12 weeks]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time.Weekly].[Week])","members":[],"bookmarked_members":[]}]},"pages":{"dimensions":[{"name":"Time","duplicate":true,"selected_set":["[Time.Weekly].[All Times]","[Time.Weekly].[Last 12 months]","[Time.Weekly].[Last 6 months]","[Time.Weekly].[Last 12 weeks]","[Time.Weekly].[Last 8 weeks]","[Time.Weekly].[Last 4 weeks]"],"members":[{"depth":0,"name":"Last 12 weeks","full_name":"[Time.Weekly].[Last 12 weeks]","annotations":{"group":"Default","predefined":"true"},"dimension_hierarchy":"Weekly","calculated":true,"drillable":true,"dimension":"Time"}],"bookmarked_members":[],"current_page_members":["[Time.Weekly].[Last 12 weeks]"]}]},"options":{"total":"columns"},"view":{"current":"bar_chart","maximized":false,"bar_chart":{"stacked":true,"vertical":true,"swap_axes":false,"data_labels":"values_percentage","series_options":{"Breached":{"color":"#B31238"},"Not breached":{"color":"#376BB7"},"$total;$total":{"separateAxis":1,"type":"text","color":"#5E6C84","dataLabelType":"top horizontal"},"Open issues;Breached":{"color":"#006F92","dataLabelType":"middle horizontal"},"Open issues;Not breached":{"color":"#86DBA6","dataLabelType":"middle horizontal"}}},"table":{}},"calculated_members":[]}
  } ],
  "calculated_members": [{"dimension":"Measures","name":"Open issues","format_string":"#,##0","formula":"CASE WHEN [Issue].CurrentMember.Level.Name \u003c\u003e 'Issue' THEN\n  Cache(\n    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n      Cache([Measures].[Issues created]\n          - [Measures].[Issues resolved])\n    ))\n    + [Measures].[Issues created]\n    - [Measures].[Issues resolved]\n  )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n  -- optimized formula for drill through Issue\n  NonZero(IIF(\n      DateBeforePeriodEnd(\n        [Issue].CurrentMember.get('Created at'),\n        [Time].CurrentHierarchyMember) AND\n      NOT DateBeforePeriodEnd(\n        [Issue].CurrentMember.get('Resolved at'),\n        [Time].CurrentHierarchyMember),\n    ([Time].CurrentHierarchy.DefaultMember,\n      [Measures].[Issues created]),\n    0\n  ))\nEND"},{"name":"Last 12 months","dimension":"Time","dimension_hierarchy":"Weekly","formula":"Aggregate(\n  [Time.Weekly].[Week].DateMembersBetween('1 year ago', 'today')\n)","format_string":""},{"dimension":"Time","name":"Last 4 weeks","format_string":"","formula":"Aggregate(\n  [Time.Weekly].[Week].DateMembersBetween('4 weeks ago', 'today')\n)","dimension_hierarchy":"Weekly"},{"name":"Last 12 weeks","dimension":"Time","dimension_hierarchy":"Weekly","formula":"Aggregate(\n  [Time.Weekly].[Week].DateMembersBetween('12 weeks ago', 'today')\n)","format_string":""},{"name":"Last 8 weeks","dimension":"Time","dimension_hierarchy":"Weekly","formula":"Aggregate(\n  [Time.Weekly].[Week].DateMembersBetween('8 weeks ago', 'today')\n)","format_string":""},{"name":"Last 6 months","dimension":"Time","dimension_hierarchy":"Weekly","formula":"Aggregate(\n  [Time.Weekly].[Week].DateMembersBetween('6 months ago', 'today')\n)","format_string":""}]
}

Hi @general.waste,

Thanks for the report definition to show the eazyBI side.
When transforming the JQL query to the eazyBI report, all filtering criteria should be covered in the report. In your case, project, specific statuses, and SLA field values should be on report pages or columns.

Measures “Open issues” represents the count of all imported issues that are not resolved (check the resolution date) at the end of the week, regardless of issue status at the time (here is a description of measure: Jira Core measures and dimensions).

If you would like to see issues that were in specific statuses (In Progress, New, On Hold, In Review) at the end of each week, consider another approach.

  1. Use “Issue cycles” and measure eazyBI generated for cycle analytics. You might want to define a new issues cycle and list all four statuses of interest. In the report, select cycle-specific measure “Issues in <cycle name.” to see how many issues were in mentioned statuses at the end of each period.
    More details are described in the documentation Issue cycles.

  2. Alternative, user issue change history.
    Select a combination of measure “Issues history” and dimension “Transition Status” to filter issues by statuses they were at the end of each week (see example here: Unresolved issues by statuses over time). Set the “Transition Status” dimension also on report pages and select statuses of interest: In Progress, New, On Hold, In Review

Best,
Zane / support@eazyBI.com