Need help in exporting a report to have a column with constant values and then compared with other columns in value changes and change %

Hi,

Need help in exporting reports based on below requirement:

  1. Release Metrics:
  • Baseline (otherwise named for committed story points) is the constant value taken after our project planning session.
  • This baseline count is gradually compared with later stages of project progress to track:
    o Burning of story points
    o Story points newly added – count and percentage
  1. Release Planned vs Actual:
  • This graph shows the burning of story points across the 5 sprints.
  • Each sprint is expected to have 20% completion.
  • Per the completion progress, what is the percentage remaining to complete till end of Sprint 5.

Here I built one report example based on some assumptions. I assume you set the scope for sprints on this planning session. However, story points added to scope might change over time.

The report uses a set of measures:
Planned on time: The measure will count Story Points added to Sprint at the end of a particular date (I used either Fix version start date, or some fixed date - 01 JAN 2020).

Total Story Points: The measure counts Story Points at the end (completion date of any Sprint) or for Today for active or future sprints

Planned burn-down by sprints: The measure will give you planned points at the end of any Sprint, it should show 0 for the last of the sprint where it was planned.

Actual burn-down by sprints: The measure will give you remaining story points at the end of any sprint (from Total story points and resolved in this and any previous sprint).

I used default measure Story Points resolved to represent the resolved progress.

You can use the report definition added in this section:
{
"calculated_members": [
    {
        "annotations": {
            "group": "Agile",
            "predefined": true
        },
        "dimension": "Measures",
        "format_string": "#,##0.00",
        "formula": "Cache(\n  NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n    [Measures].[Story Points change]\n  ))\n  + [Measures].[Story Points change]\n)",
        "name": "Story Points history"
    },
    {
        "annotations": {
            "group": "Epic burn-down"
        },
        "dimension": "Measures",
        "format_string": "",
        "formula": "Case When\n  [Sprint].CurrentMember.GetBoolean('Closed')\nThen\n NonZero(([Measures].[Story Points history],\n   [Time].CurrentHierarchy.Levels('Day').DateMember( \n    [Sprint].Currentmember.Get('Complete date')),\n  [Sprint].DefaultMember))\nElse\n  NonZero(([Measures].[Story Points history],\n   [Time].CurrentHierarchy.Levels('Day').CurrentDateMember,\n  [Sprint].DefaultMember))\nEnd",
        "name": "Total story points"
    },
    {
        "dimension": "Measures",
        "format_string": "",
        "formula": "-- planned in Sprints at Version start date or on particular date\nCASE WHEN [Measures].[Story Points created] > 0\nTHEN\nNonZero(([Measures].[Story Points history],\n [Time].CurrentHierarchy.Levels(\"Day\").DateMember(\n    CASE WHEN NOT IsEmpty([Fix Version].CurrentHierarchyMember.Get(\"Start date\"))\n    THEN [Fix Version].CurrentHierarchyMember.GetDate(\"Start date\")\n    ELSE DateParse(\"20 JAN 2015\")\n    END  \n  )\n))\nEND",
        "name": "Planned on date"
    },
    {
        "dimension": "Measures",
        "format_string": "",
        "formula": "SUM(VisibleRowsSet(), [Measures].[Planned on date])\n-\nSum(\n  Head(VisibleRowsSet(), Rank(CurrentTuple(VisibleRowsSet()), VisibleRowsSet())),\n  [Measures].[Planned on date]\n)",
        "name": "Planned burn-down by Sprints"
    },
    {
        "dimension": "Measures",
        "format_string": "",
        "formula": "[Measures].[Total story points]\n-\nSum(\n  Head(VisibleRowsSet(), Rank(CurrentTuple(VisibleRowsSet()), VisibleRowsSet())),\n  [Measures].[Story Points resolved]\n)",
        "name": "Actual burn-down by Sprints"
    }
],
"cube_name": "Issues",
"cube_reports": [
    {
        "definition": {
            "columns": {
                "dimensions": [
                    {
                        "members": null,
                        "name": "Measures",
                        "selected_set": [
                            "[Measures].[Planned on date]",
                            "[Measures].[Planned burn-down by Sprints]",
                            "[Measures].[Actual burn-down by Sprints]",
                            "[Measures].[Story Points resolved]"
                        ]
                    }
                ]
            },
            "options": {
                "nonempty": "rows"
            },
            "pages": {
                "dimensions": [
                    {
                        "bookmarked_members": null,
                        "current_page_members": [
                            "[Fix Version].[All Fix Versions]"
                        ],
                        "members": [
                            {
                                "depth": 0,
                                "drillable": true,
                                "full_name": "[Fix Version].[All Fix Versions]",
                                "name": "All Fix Versions",
                                "type": "all"
                            }
                        ],
                        "name": "Fix Version",
                        "selected_set": [
                            "[Fix Version].[All Fix Versions]"
                        ]
                    }
                ]
            },
            "rows": {
                "dimensions": [
                    {
                        "bookmarked_members": null,
                        "members": [
                            {
                                "depth": 2,
                                "full_name": "[Sprint].[(no board)].[(no sprint)]",
                                "name": "(no sprint)",
                                "removed": true,
                                "url_id": -1,
                                "url_sub_id": -1
                            }
                        ],
                        "name": "Sprint",
                        "selected_set": [
                            "[Sprint].[Sprint].Members"
                        ]
                    }
                ],
                "nonempty_crossjoin": true
            },
            "view": {
                "bar_chart": {
                    "data_labels": false,
                    "series_options": {
                        "Actual burn-down by Sprints": {
                            "color": "#80699B",
                            "type": "spline"
                        },
                        "Planned burn-down by Sprints": {
                            "color": "#4572A7",
                            "type": "line"
                        },
                        "Planned in version": {
                            "color": "#9043C2"
                        },
                        "Planned on date": {
                            "color": "#4572A7"
                        },
                        "Story Points resolved": {
                            "color": "#89A54E"
                        },
                        "Total story points": {
                            "type": "line"
                        }
                    },
                    "stacked": false,
                    "swap_axes": false,
                    "vertical": true
                },
                "current": "bar_chart",
                "maximized": false,
                "table": {}
            }
        },
        "folder_name": "Version Reports",
        "name": "Release burn-down by Sprints",
        "result_view": "bar_chart"
    }
]

}

Daina / support@eazybi.com