Hi.
I have a report that returns issues and their time spent in the “In Progress” transition, filtered by a selected time period (screenshot attached). The results are sorted in descending order to show which ticket has spent the most total time in “In Progress”.
Now, I need to enhance this report to display the data as a time series—specifically, I want to see for each day in the selected time period which single issue was in “In Progress” the longest on that specific day. Ideally, the report would show one issue per day (the one in “In Progress” the longest on that date), and let me view this over time.
I’ve tried several adjustments and approaches on my own, but haven’t been able to get the expected results, so I’m reaching out for help. I’m unsure how to structure the report to achieve this daily breakdown.
Any guidance on how to structure the report or MDX to support this use case would be appreciated!
Here’s the definition for my current report.
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Workdays in In Progress",
"result_view": "table",
"definition": {
"columns": {
"dimensions": [
{
"name": "Measures",
"selected_set": [
"[Measures].[Workdays in transition status]",
"[Measures].[Story Points]"
],
"members": []
}
]
},
"rows": {
"dimensions": [
{
"name": "Issue",
"selected_set": ["[Issue].[All Issues]"],
"members": [
{
"depth": 0,
"name": "All Issues",
"full_name": "[Issue].[All Issues]",
"drillable": true,
"type": "all",
"expanded": true,
"drilled_into": false,
"removed": true
},
{
"depth": 1,
"name": "Generic Project Name",
"full_name": "[Issue].[Generic Project Name]",
"drillable": true,
"key": "PROJECTKEY",
"expanded": true,
"drilled_into": false,
"removed": true,
"parent_full_name": "[Issue].[All Issues]"
}
],
"bookmarked_members": []
}
],
"order_by": {
"expression": ["[Measures].[Workdays in transition status]"],
"order": "DESC"
},
"nonempty_crossjoin": true
},
"pages": {
"dimensions": [
{
"name": "Issue Type",
"selected_set": ["[Issue Type.By type].[Issue Type].Members"],
"members": [
{
"depth": 2,
"name": "Bug",
"full_name": "[Issue Type.By type].[Standard].[Bug]"
},
{
"depth": 2,
"name": "Spike",
"full_name": "[Issue Type.By type].[Standard].[Spike]"
},
{
"depth": 2,
"name": "Story",
"full_name": "[Issue Type.By type].[Standard].[Story]"
},
{
"depth": 2,
"name": "Task",
"full_name": "[Issue Type.By type].[Standard].[Task]"
}
],
"bookmarked_members": [],
"current_page_members": [
"[Issue Type.By type].[Standard].[Bug]",
"[Issue Type.By type].[Standard].[Spike]",
"[Issue Type.By type].[Standard].[Story]",
"[Issue Type.By type].[Standard].[Task]"
]
},
{
"name": "Transition Status",
"selected_set": ["[Transition Status].[All Transition Statuses]"],
"members": [
{
"depth": 0,
"name": "All Transition Statuses",
"full_name": "[Transition Status].[All Transition Statuses]",
"drillable": true,
"type": "all",
"expanded": true,
"drilled_into": false
},
{
"depth": 1,
"name": "In Progress",
"full_name": "[Transition Status].[In Progress]",
"parent_full_name": "[Transition Status].[All Transition Statuses]"
}
],
"bookmarked_members": [],
"current_page_members": ["[Transition Status].[In Progress]"]
},
{
"name": "Time",
"selected_set": ["[Time].[All Times]"],
"members": [
{
"depth": 0,
"name": "All Times",
"full_name": "[Time].[All Times]",
"drillable": true,
"type": "all",
"expanded": true,
"drilled_into": false
},
{
"depth": 1,
"name": "2025",
"full_name": "[Time].[2025]",
"drillable": true,
"expanded": true,
"drilled_into": false,
"parent_full_name": "[Time].[All Times]"
},
{
"depth": 2,
"name": "Q1 2025",
"full_name": "[Time].[2025].[Q1 2025]",
"drillable": true,
"parent_full_name": "[Time].[2025]"
}
],
"bookmarked_members": [],
"current_page_members": ["[Time].[2025].[Q1 2025]"]
}
]
},
"options": {},
"view": {
"current": "table",
"maximized": false,
"table": {
"cell_formatting": {
"[Measures].[Story Points]": {
"type": "range",
"rules": [
{ "max": "1.2", "min": "0", "background_color": "#E8D1F6" },
{ "min": "1.2", "max": "2.2", "background_color": "#DCBCED" },
{ "min": "2.2", "max": "3.2", "background_color": "#C28AE0" },
{ "min": "3.2", "max": "5.2", "background_color": "#A857D3" },
{ "min": "5.2", "max": "8.2", "background_color": "#9043C2" },
{ "min": "8.2", "background_color": "#6F2DAA" }
]
},
"[Measures].[Workdays in transition status]": {
"type": "range",
"rules": [
{ "min": "1", "max": "5", "background_color": "#B3D4FF" },
{ "min": "5", "max": "10", "background_color": "#7CA7E5" },
{ "min": "10", "max": "25", "background_color": "#5A89CE" },
{ "min": "25", "max": "50", "background_color": "#376BB7" },
{ "min": "50", "background_color": "#D4153B" }
]
}
}
}
},
"calculated_members": []
}
}],
"calculated_members": [
{
"name": "Issue Story Points",
"dimension": "Measures",
"formula": "[Issue].CurrentHierarchyMember.get('Story Points')",
"format_string": "#,##0.00"
},
{
"name": "Story Points",
"dimension": "Measures",
"formula": "CoalesceEmpty([Measures].[Issue Story Points], 0)\n",
"format_string": ""
}
]
}