How to change order of columns AND show issues from ACTIVE SPRINT and PAST SPRINT in the report?

Questions & Answers
1

Hello Ilze and EasyBI support team.
How can I order the issues DONE in the current sprint “Active sprint” and DONE in the past sprints?

Aggregate({
  [Status].[Backlog],
  [Status].[To Do] -- Active sprint --
  [Status].[To Do] -- Past sprints --
  [Status].[In Progress],
  [Status].[In Review],
  [Status].[Done] -- Active sprint --
  [Status].[Done] -- Past sprints --
})
2

Hi,
The solution could be created by combining the calculated members in the Sprint dimension in the report. I tried the following report which seems to contain the needed information:

You can find how to create calculated members in the Sprint dimension from our Demo account.
https://eazybi.com/accounts/1000/cubes/Issues/

Kindly,
Janis, eazyBI support

3

@janis.plume i tried what you have above and i have sprints that are repeating. any suggestions?

image
image2804×1288 255 KB

{

“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Innovation Rate”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Sprint”,“selected_set”:["[Sprint].[All Sprints]","[Sprint].[Closed, Active, Future (ordered)]","[Sprint].[All closed sprints]"],“members”:[{“depth”:0,“name”:“All Sprints”,“full_name”:"[Sprint].[All Sprints]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:"(no board)",“full_name”:"[Sprint].[(no board)]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Dev Nexus Scrum Board”,“full_name”:"[Sprint].[Dev Nexus Scrum Board]",“drillable”:true,“expanded”:true,“drilled_into”:false,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“MAS Scrum Board”,“full_name”:"[Sprint].[MAS Scrum Board]",“drillable”:true,“expanded”:true,“drilled_into”:false,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Mobile Team Scrum Board”,“full_name”:"[Sprint].[Mobile Team Scrum Board]",“drillable”:true,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:1,“name”:“Web Team Scrum Board”,“full_name”:"[Sprint].[Web Team Scrum Board]",“drillable”:true,“expanded”:true,“drilled_into”:false,“removed”:true,“parent_full_name”:"[Sprint].[All Sprints]"},{“depth”:0,“name”:“Closed, Active, Future (ordered)”,“full_name”:"[Sprint].[Closed, Active, Future (ordered)]",“calculated”:true,“drillable”:true,“expanded”:true,“drilled_into”:false,“removed”:false,“id”:"[Sprint].[Closed, Active, Future (ordered)]"},{“depth”:2,“name”:"(no sprint)",“full_name”:"[Sprint].[(no board)].[(no sprint)]",“url_id”:-1,“url_sub_id”:-1,“removed”:true,“parent_full_name”:"[Sprint].[Closed, Active, Future (ordered)]"},{“depth”:2,“name”:“MAS Sprint 6”,“full_name”:"[Sprint].[MAS Scrum Board].[MAS Sprint 6]",“url_id”:6,“url_sub_id”:21,“removed”:true,“parent_full_name”:"[Sprint].[Closed, Active, Future (ordered)]"},{“depth”:2,“name”:“October Commitments: 9/23-10/8”,“full_name”:"[Sprint].[Web Team Scrum Board].[October Commitments: 9/23-10/8]",“url_id”:19,“url_sub_id”:36,“removed”:true,“parent_full_name”:"[Sprint].[Closed, Active, Future (ordered)]"},{“depth”:0,“name”:“All closed sprints”,“full_name”:"[Sprint].[All closed sprints]",“annotations”:{“predefined”:“true”},“calculated”:true,“drillable”:true,“expanded”:true,“drilled_into”:false,“removed”:true}],“bookmarked_members”:null},{“name”:“Measures”,“selected_set”:["[Measures].[Issues created]"],“members”:null}]},“rows”:{“dimensions”:[{“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”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:“Epic”,“full_name”:"[Issue Type].[Epic]",“removed”:true,“parent_full_name”:"[Issue Type].[All Issue Types]"},{“depth”:1,“name”:“Task”,“full_name”:"[Issue Type].[Task]",“removed”:true,“parent_full_name”:"[Issue Type].[All Issue Types]"},{“depth”:1,“name”:“Sub-task”,“full_name”:"[Issue Type].[Sub-task]",“removed”:true,“parent_full_name”:"[Issue Type].[All Issue Types]"},{“depth”:1,“name”:“Feature Request”,“full_name”:"[Issue Type].[Feature Request]",“removed”:true,“parent_full_name”:"[Issue Type].[All Issue Types]"},{“depth”:1,“name”:“Anonymous Bug”,“full_name”:"[Issue Type].[Anonymous Bug]",“removed”:true,“parent_full_name”:"[Issue Type].[All Issue Types]"},{“depth”:1,“name”:“Idea”,“full_name”:"[Issue Type].[Idea]",“removed”:true,“parent_full_name”:"[Issue Type].[All Issue Types]"},{“depth”:1,“name”:“Initiative”,“full_name”:"[Issue Type].[Initiative]",“removed”:true,“parent_full_name”:"[Issue Type].[All Issue Types]"}],“bookmarked_members”:null}]},“pages”:{“dimensions”:[{“name”:“Team”,“selected_set”:["[Team].[All Teams]"],“members”:[{“depth”:0,“name”:“All Teams”,“full_name”:"[Team].[All Teams]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:null,“current_page_members”:["[Team].[All Teams]"]}]},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{}},“calculated_members”:null}
} ],
“calculated_members”: [{“name”:“All closed sprints”,“dimension”:“Sprint”,“format_string”:"",“formula”:“Aggregate(\n Order(\n Filter([Sprint].[Sprint].Members,\n [Sprint].CurrentMember.getBoolean(‘Closed’) AND\n NOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’))),\n [Sprint].CurrentMember.get(‘Start date’),\n BASC\n )\n)\n”},{“name”:“Closed, Active, Future (ordered)”,“dimension”:“Sprint”,“formula”:"–closed sprints are displayed first (by start date), then active sprints (by start date), then future sprints (in no particular order)\nAggregate(\nUnion(\n – set with last 2 closed sprints within the board\n Order(\n Filter(\n [Sprint].[Sprint].Members,\n [Sprint].CurrentMember.getBoolean(‘Closed’)\n and\n [Sprint].CurrentMember.Name \u003c\u003e ‘(no sprint)’\n ), \n – ordering by start date \n [Sprint].CurrentMember.get(‘Start date’), BASC\n ),\n-- set with all not closed sprints within the board\n Order \n (Filter(\n [Sprint].[Sprint].Members,\n NOt [Sprint].CurrentMember.getBoolean(‘Closed’)\n and\n [Sprint].CurrentMember.Name \u003c\u003e ‘(no sprint)’\n ),\n-- ordered by start date, for future sprints, May 08 3017 is used as start date\n Coalesceempty(\n Datetotimestamp([Sprint].CurrentMember.get(‘Start date’)),\n datetotimestamp(‘May 08 3017’)), BASC\n )\n))\n",“format_string”:""}]
}