Hi @georgePTO ,
Welcome to the eazyBI community!
A similar question was raised in another community post here How to get the days spent by a developer for story in a particlar status? - #2 by martins.vanags , but that displays time in status when the issue has already moved on.
For the beginning, let us put the Assignee dimension in report rows.
Since there are two statuses and you are looking for the status and time within that - let us put these statuses into report columns.
You might do that by adding the dimension ‘Transition status’ to the report columns and selecting bookmarked members only to display the relevant ones.
The standard measures displaying days in statuses retrieve information once the issue transits away from the status. Therefore, we might create a new measure retrieving the timestamp when an issue entered the current status. You are looking for the time in the status with the current assignee.
That means we need the latest timestamps - assignee change or status change to calculate hours from that moment until now.
Each issue/status/assignee combination returns a different timestamp; therefore, the measure should iterate through the Issue dimension.
So the MDX expression could be as follows.
Sum(
--create set of all issues relevant for the Assignee/Status combination
Filter(
DescendantsSet([Issue].CurrentHierarchyMember,
[Issue].[Issue]),
IIF(
--check if the current assignee member is the actual for the issue
[Assignee].[User].GetMemberNameByKey([Issue].CurrentHierarchyMember.Get('Assignee name'))
=
[Assignee].CurrentHierarchyMember.Name,
--check if the current transition status member is the actual for the issue
IIF(
[Status].[Status].GetMemberNameByKey([Issue].CurrentHierarchyMember.Get('Status ID'))
=
[Transition Status].CurrentHierarchyMember.Name,
1,0),
0)=1),
--the actual spent hours - subtraction of last timestamp from now
DateDiffHours(
TimestampToDate(
--latest timestamp from assignee or status
MAX(
([Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember,
[Measures].[Transition to last timestamp]),
([Transition Field].[Status],
[Time].CurrentHierarchy.DefaultMember,
[Measures].[Transition to last timestamp]))),
'now')
)
That would create a report like this. You might drill through issues on each figure to see which issues constitute the total.
Still, if you have two statuses within the revision category and would like to calculate the total time spent within both of these statuses, please write to support@eazyBI.com with a detailed description of the workflow. If an issue might move between these statuses and is still considered “remaining in the swimlane” - the solution might require a different approach.
Kind regards,
Oskars / support@eazyBI.com