Hi, I am trying to get a report where I show total hours spent for each Fix Version, broken down by certain fields in sub-tasks.
We book time only in subtasks, and subtasks have a custom field “Work Category”. Additionally, we have userstories/bugs that have the standard Fix Version field. Subtasks don’t have that field and I would like to not add a field to them just for the sake of reporting.
Basically I am trying to get a report showing how much time was spent on each Fix Version (User story level), broken down by Work Category field (at subtask level), and I am hitting the wall here, trying to combined the dimensions.
When there are different fields used at the different levels of issue hierarchy and the report needs to combine some parents and sub-tasks dimension values, a specific measure for each case should be created. The idea of the measure is to create the sum over the set of issues by ignoring some dimensions for some children and some for the parents.
First, let us create a calculated member in the Issue Type dimension which collects all the issue types except the sub-tasks. The calculated member would look like this (“Standard”):
Aggregate(Filter(
[Issue type].[Issue type].Members,
NOT [Issue type].CurrentMember.GetBoolean('Subtask')
))
Then we can create the measure showing the effort by work type for the fix versions in the case when hours spent are in the subtasks but fix version is specified for the parents. Then the calculation would look like this:
Sum(
Filter (Descendants ([Issue.Sub-task].CurrentHierarchyMember,
[Issue.Sub-task].[Sub-task]), --iterate over all sub-tasks
([Measures].[Issues created],
[Issue].CurrentHierarchyMember.Parent,
[Work category].DefaultMember, --ignore work category for parent
[Issue type].[Standard])>0
),
([Measures].[Hours spent],
[Fix version].CurrentHierarchy.DefaultMember) --ignore version for subtask
)
Then you can build the report you are looking for:
