Thank you @daina.tupule for tips creating this. It is working fine, but slow in my case.
I have very similar use case where I need three different sprint reports in a single dashboard:
- Sprint Burn-down of last closed sprint
- All issues details of last closed sprint
- All issues details in active sprint
Sprint board is in the common filter section and sets the tone for the three reports.
I’ve attached the export of #2 report. I have ~20 different sprint boards with >4 years of historical sprint data, so queries are slow or throwing exception. Any tips making it more efficient?
FYI: I’m trying to avoid using time dimension to filter down the data set, as Time is used in other dashboard’s reports. It may undesirably affect other reports behaviour.
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Last Sprint",
"folder_name": "PI Status",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Committed or added]","[Measures].[Sprint Story Points at closing]","[Measures].[Sprint issue status at closing]","[Measures].[Issue type]","[Measures].[Epic name]","[Measures].[Issue fix versions]"],"members":[]}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[AP Scrum]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Sprint].[Sprint])","members":[],"bookmarked_members":[]},{"name":"Issue","selected_set":["[Issue].[Issue].Members"],"members":[],"bookmarked_members":[]}],"order_by":{"expression":["[Measures].[Closed sprint issue sorting]"],"order":"DESC"},"filter_by":{"conditions":[{"expression":["[Measures].[Sprint issues at closing]"],"operator":"\u003e","value":"0"},{"expression":["[Measures].[Issue type]"],"operator":"\u003c\u003e","value":"Sub-task"},{"expression":["[Measures].[Last closed sprints by boards]"],"operator":"=","value":"1"},{"expression":["[Measures].[Issue type]"],"operator":"\u003c\u003e","value":"Bug"}],"others":false},"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Sprint","duplicate":true,"selected_set":["[Sprint].[SS Scrum]","[Sprint].[SE Boards]","[Sprint].[All Sprints]"],"members":[{"depth":0,"name":"SE Boards","full_name":"[Sprint].[SE 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","expanded":true,"drilled_into":false},{"depth":1,"name":"AP Scrum","full_name":"[Sprint].[AP Scrum]","drillable":true,"parent_full_name":"[Sprint].[All Sprints]"}],"bookmarked_members":[],"current_page_members":["[Sprint].[AP Scrum]"]}]},"options":{"nonempty":true},"view":{"current":"table","maximized":false,"table":{"cell_formatting":{"[Measures].[Sprint issue status at closing]":{"type":"range","whole_row":true,"rules":[{"min":"Closed","background_color":"#34AD70","max":"Closed"},{"min":"In Progress","background_color":"#FFD04D","max":"In Progress"},{"min":"In Review","background_color":"#FFD04D","max":"In Review"},{"min":"In QA","background_color":"#FFD04D","max":"In QA"},{"min":"Acceptance","background_color":"#FFD04D","max":"Acceptance"},{"min":"QA In Progress","max":"QA In Progress","background_color":"#FFD04D"},{"min":"Blocked","background_color":"#D4153B","max":"Blocked"},{"min":"Ready","background_color":"#D4153B","max":"Ready"},{"background_color":"#D4153B","min":"Backlog","max":"Backlog"},{"min":"Open","max":"Open","background_color":"#D4153B"},{"min":"On Hold","max":"On Hold","background_color":"#D4153B"},{"min":"Elaboration","max":"Elaboration","background_color":"#D4153B"}]}}}},"calculated_members":[]}
} ],
"calculated_members": [{"dimension":"Measures","name":"Issue type","format_string":"","formula":"[Issue Type].[Issue Type].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get('Issue type ID')\n)"},{"dimension":"Measures","name":"Issue fix versions","format_string":"","formula":"[Fix Version].[Version].getMemberNamesByKeys(\n [Issue].CurrentHierarchyMember.get('Fix version IDs')\n)"},{"name":"Issue Epic Link","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('Epic Link')","format_string":""},{"name":"Issue Epic Name","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('Epic Name')","format_string":""},{"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 issues committed","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n -- An issue was in a sprint at a sprint start time\n [Issue Sprint Status Change].[Future =\u003e Active]\n)\n"},{"name":"Sprint issues added","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n -- An issue was added or created in an active sprint\n [Issue Sprint Status Change].[(none) =\u003e Active]\n)\n"},{"name":"Sprint issues at closing","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n -- An issue was in a sprint at closing\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint issue status at closing","dimension":"Measures","formula":"Generate(\n Filter([Transition status].[Transition status].Members, \n [Measures].[Sprint issues at closing]\u003e0),\n [Transition status].CurrentMember.Name\n )","format_string":""},{"name":"Committed or added","dimension":"Measures","formula":"CASE WHEN [Measures].[Sprint issues committed] \u003e 0\n THEN \"Committed\"\n ELSE \n CASE WHEN [Measures].[Sprint issues added] \u003e 0 \n THEN \"Added\"\n END\nEND","format_string":""},{"name":"Closed sprint issue sorting","dimension":"Measures","formula":"[Measures].[Committed or added] || \" \" || Format([Measures].[Sprint Story Points at closing], '000.0')","format_string":""},{"name":"Epic name","dimension":"Measures","formula":"CASE WHEN [Measures].[Issue type] = \"Epic\"\nTHEN --for epics return Epic Name\n CoalesceEmpty([Measures].[Issue Epic Name],\"\")\nELSE -- for other issues return linked Epic Name\n [Issue].[Issue].GetMemberByKey(\n [Measures].[Issue Epic Link]\n ).Get('Epic Name')\nEND","format_string":""},{"name":"SE Boards","dimension":"Sprint","formula":"Aggregate({\n [Sprint].[SS Scrum],\n [Sprint].[PL Scrum],\n [Sprint].[ME Scrum],\n [Sprint].[AP Scrum],\n [Sprint].[WI Scrum],\n [Sprint].[WC Scrum],\n [Sprint].[DS Scrum],\n [Sprint].[JX Scrum],\n [Sprint].[JS Scrum Board],\n [Sprint].[MO Scrum],\n [Sprint].[EM Scrum],\n [Sprint].[JA Scrum],\n [Sprint].[CO Scrum],\n [Sprint].[CT Scrum]\n})","format_string":""}]
}
Thanks in advance.
