Issues with 'Nonempty' Option in Sprint Velocity Report

Questions & Answers
1

Hello community!,

I’m working on a report to measure sprint velocity, and I’ve configured it as follows:

Rows: “Sprint”
Pages: “Project,” “Sprint,” “Issuetype”
Measures: “Sprint Story Points completed,” “Running Story Points velocity for 5 closed sprints” (both measures provided by default in Eazybi)

I’m also using the “Nonempty” option in the Pages to ensure that when I filter by a project, only the boards related to that project appear in the “Sprint” filter. However, this is not working, and here’s my issue. When I filter by a project, all boards, whether related to that project or not, appear.

I’ve made several attempts and discovered that when I don’t use the “Running Story Points velocity for 5 closed sprints” measure, the “Nonempty” option works perfectly. But the problem arises when I introduce this measure; at that point, “Nonempty” ceases to function.

I really need to find a way to make “Nonempty” work for this report. Your help would be greatly appreciated.

Best regards,
Miqueas Milanesio

2

Hi @Miqueas_Milanesio,

The calculated measure “Running Story Points velocity for 5 closed sprints” is indeed at fault here. It is created to work for as many use cases as possible. Because of that, it could interfere with the “Nonempty” option in the pages.

Please export and share the definition of the report so I can see if we can tailor the calculation of “Running Story Points velocity for 5 closed sprints” for the particular report - Export and import report definitions.

Best,
Roberts // support@eazybi.com

4

I’m sorry @roberts.cacus , I’m pasting the export again because the previous one didn’t work:

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Velocidad del Sprint",
     "result_view": "bar_chart",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Story Points completados]","[Measures].[Velocidad últimos 5 sprints cerrados]"],"members":[]}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[All Sprints]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Sprint].[Sprint])","members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":["[Measures].[Last closed sprints by boards]"],"operator":"\u003c=","value":"10"}]}},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Project].[All Projects]"]},{"name":"Sprint","duplicate":true,"selected_set":["[Sprint].[Boards]","[Sprint].[All Sprints]"],"members":[{"depth":0,"name":"Boards","full_name":"[Sprint].[Boards]","calculated":true,"drillable":true,"dimension":"Sprint","expanded":true,"drilled_into":false,"removed":true},{"depth":0,"name":"All Sprints","full_name":"[Sprint].[All Sprints]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Sprint].[All Sprints]"]},{"name":"Issue Type","selected_set":["[Issue Type.By type].[All Issue Types by type]"],"members":[{"depth":0,"name":"All Issue Types by type","full_name":"[Issue Type.By type].[All Issue Types by type]","drillable":true,"type":"all","expanded":true},{"depth":1,"name":"Standard","full_name":"[Issue Type.By type].[Standard]","drillable":true,"parent_full_name":"[Issue Type.By type].[All Issue Types by type]"}],"bookmarked_members":[],"current_page_members":["[Issue Type.By type].[Standard]"]}],"nonempty_crossjoin":true},"options":{"nonempty":"rows"},"view":{"current":"bar_chart","maximized":false,"bar_chart":{"stacked":true,"vertical":true,"swap_axes":false,"data_labels":false,"series_options":{"Running Story Points velocity for 5 closed sprints":{"type":"line-Dash","color":"#86DBA6"},"Sprint Story Points completed":{"color":"#00875A"},"velocidad ultimos 5 sprints cerrados":{"type":"line-Dash","color":"#5DC48B"},"Story Points agregados":{"color":"#00C7E5","stacked":1},"Story Points comprometidos":{"stacked":1},"Story Points completados":{"stacked":2,"color":"#34AD70"},"Velocidad ultimos 5 sprints cerrados":{"type":"line-Dash","color":"#D4153B"},"Velocidad últimos 5 sprints cerrados":{"type":"spline","color":"#FFBE60","dataLabelType":"top rotated"},"Velocity last 5 sprints closed":{"type":"spline"}},"show_full_caption":["Measures"]},"table":{}},"calculated_members":[],"description":"## Velocidad Sprint: Promedio de Story points completados en los últimos 5 sprints cerrados."}
  } ],
  "calculated_members": [{"name":"Last closed sprints by boards","dimension":"Measures","format_string":"","formula":"CASE\nWHEN\n  ([Measures].[Issues created],\n  [Sprint].CurrentHierarchyMember.Parent) \u003e 0\nTHEN\n  NonZero(\n    Rank(\n      [Sprint].CurrentHierarchyMember,\n      Order(\n        Filter(\n          [Sprint].CurrentHierarchyMember.Parent.Children,\n          NOT isEmpty([Sprint].CurrentHierarchyMember.Get('Start date'))\n          AND [Sprint].CurrentHierarchyMember.GetBoolean('Closed')\n        ),\n      [Sprint].CurrentHierarchyMember.Get('Start date'), DESC)\n    )\n  )\nEND\n"},{"name":"Sprint Story Points at closing","dimension":"Measures","format_string":"#,##0.00","formula":"-- Story points from issues when a sprint was closed\n( [Measures].[Story Points added],\n  [Transition Field].[Sprint status],\n  [Sprint Status].[Closed],\n  [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint Story Points completed","dimension":"Measures","format_string":"#,##0.00","formula":"(\n  [Measures].[Sprint Story Points at closing],\n  [Transition Status.Category].[Listo]\n)\n"},{"name":"Running Story Points velocity for 5 closed sprints","dimension":"Measures","format_string":"#,##0.00","formula":"CASE\n  WHEN\n    [Sprint].CurrentHierarchyMember.Level.Name = 'Sprint' AND\n    [Sprint].CurrentHierarchyMember.GetBoolean('Closed')\n  THEN\n  AVG(\n    Tail(\n      Filter(\n        -- filter last 5 previous sprints in a board starting from current sprint\n        [Sprint].CurrentHierarchyMember.FirstSibling:\n        [Sprint].CurrentHierarchyMember,\n        [Sprint].CurrentHierarchyMember.GetBoolean('Closed')\n      ),\n      -- set the count of last closed sprints for running velocity\n      5 ) ,\n    [Measures].[Sprint Story Points completed]\n  )\nEND\n"},{"name":"Story Points completados","dimension":"Measures","formula":"[Measures].[Sprint Story Points completed]","format_string":""},{"name":"Velocidad últimos 5 sprints cerrados","dimension":"Measures","formula":"[Measures].[Running Story Points velocity for 5 closed sprints]","format_string":""},{"name":"Boards","dimension":"Sprint","formula":"Aggregate(\n   [Sprint].[All Sprints].Children\n)\n","format_string":""}]
}
5

Hi @Miqueas_Milanesio,

Unfortunately, the “Nonempty” option in report pages doesn’t like calculated measures traversing through multiple levels of a dimension in pages. Finding the first Sprint of a particular board causes the undesired effect of “Nonempty” stopping to work. As a workaround, you can try the calculated measure formula below:

CASE WHEN
  [Sprint].CurrentHierarchyMember.Level.Name = 'Sprint' AND
  [Sprint].CurrentHierarchyMember.GetBoolean('Closed')
THEN
  Avg(
    Filter(
      {[Sprint].CurrentMember.Lag(4):
      [Sprint].CurrentMember},
      [Sprint].CurrentMember.GetBoolean('Closed')
    ),[Measures].[Sprint Story Points completed]
  )
END

With this calculated measure, the “Nonempty” pages option will work. But it has a drawback. It looks at the current Sprint dimension member in rows and considers a set of Sprints that includes the current Sprint and four before it. Because of that, it doesn’t consider the “Board” hierarchy, and with the first four Sprints of a Board in the report rows, it could return at least one Sprint from another Board.

It would be a good idea to compare the results of both calculations and determine if the suggested workaround suits your requirements.

Best,
Roberts // support@eazybi.com

6

Thank you very much, @roberts.cacus !

With this new formula, I’m achieving what I was looking for. Now, the “nonempty” is working in the pages, and the velocity values I obtain are correct.

1 Like