EazyBI - show Subtasks hours

[Emile Vianney-Liaud]

Hello,

I want to show an evolution of hours spent in an epic.

The problem is that the worklog are entered in Subtasks, and eazyBI doesn't go so far.

Here is the hierarchy: 

Project >> Epic (where we input the original estimated hours) >> User-Story (Where we only have a description of the user-story) >> Sub-task

And we input estimated hours, remaining estimated hours, and worklog our time in the sub-tasks.

As you can see in this image, only Remaining estimated hours history and original estimated hours are showed :

The « Hours spent with sub-tasks » aren't showed, because they're in the sub-tasks, and sub-tasks is not taken into account.

Further more, I tried this code in [Measures].[Sub-tasks of sub-tasks] (Calculated member that I created myself):

 

CASE WHEN [Measures].[Issue type] = 'Epic' THEN
 Sum(
  Descendants([Issue].CurrentMember, [Measures].[Hours spent with sub-tasks]),
  DefaultContext(( 
  [Epic Link].[Epic].Members.Item([Issue].CurrentMember.Name),
  [Time].CurrentMember,
  [Measures].[Hours Spent]
  ))
 )
 ELSE
  [Measures].[Hours spent with sub-tasks]
 END

But there is no way. It doesn't work.

Can you help me ?

I can give more information if you need to.

I'm absent until next week, Friday  06.10.2017. So I will read the answers at this moment.

Thank you already for answering me.

Good afternoon.

Emile

[Daina Tupule eazyBI · Accepted solution]

Hi Emilie,

You are using Issue default hierarchy for your report. You would l like to use Issue Epic hierarchy instead. Issue epic hierarchy will automatically sum up all data by Epics. 

Please select an option "All issues by epics" in "Select members" section of Issue dimension and use epics for selection.

[Emile Vianney-Liaud]

Thank you,

I've selected  "All issues by epics", and I've chosen my Epic: I can see the sub-tasks hours now.

But the "Remaining estimated hours history" and "Original estimated hours all time"* in Measures dimension are false now. They calculates the hours in epic AND in sub-tasks.

 

Do you know how to resolve this problem ?

Have a nice day,

Emile

*The original estimated hours all time is a user defined calculated members. I created it to have the Original estimated hours on all the graph. Here is the code if you need :

Cache(
 NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
 [Measures].[Original estimated hours]
 ))
 + [Measures].[Sub-tasks original estimated hours]
 )

[Emile Vianney-Liaud]

Hello,

I found !

In "Pages", I added the Issue Type dimension, and selected the filter "All but not Subtask", that is in calculated members*.

Now the hours are correct.

Thank you !

* Here is the code of the calculated member "All but not Subtask" ([Issue Type].[All but not Subtask]):

Aggregate(Filter(
 [Issue type].[Issue type].Members,
 NOT [Issue type].CurrentMember.GetBoolean('Subtask')
))

[Emile Vianney-Liaud]

Hello,

Can you help me ? Is there a solution for what I've asked ?

In advance, thank you for your answer.

Emile

[eazyBI Support]

Emile,

The Hours spent with sub-tasks measure should be displayed for the Epic level and should collect the hours down to the subtask level.

You could probably check if you have selected the Issue dimension in the report pages (not the Epic link).

If it still does not show up, please send to the eazyBI support the report definition. Please, also send the advanced settings if you have built custom issue hierarchy.

[Emile Vianney-Liaud]

Good morning,

The Issue dimension is already in the report pages, but maybe I haven't selected the right member ? 

Here is the report definition.

{
 "cube_name": "myEPIC",
 "cube_reports": [ {
 "name": "Evolution des heures",
 "folder_name": "Evolution heures",
 "result_view": "line_chart",
 "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Cumulative hours spent with sub-tasks]","[Measures].[Remaining estimated hours history]","[Measures].[Original estimated hours with sub-tasks]","[Measures].[Hours spent with sub-tasks]","[Measures].[Hours spent with sub-tasks with history]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[Month].Members"],"members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":"[Time].CurrentHierarchyMember","operator":"between","value":"Jun 1 2015 and Dec 31 2017","value_type":"date"}],"others":false}},"pages":{"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},{"depth":1,"name":"myEPIC","full_name":"[Issue].[myEPIC]","drillable":true,"key":"myEPIC","expanded":true,"drilled_into":false,"parent_full_name":"[Issue].[All Issues]"},{"depth":2,"name":"myEPIC-4455- incident PC - description","full_name":"[Issue].[myEPIC].[myEPIC-4455- incident PC - description]","key":"myEPIC-4455","parent_full_name":"[Issue].[myEPIC]"},{"depth":2,"name":"myEPIC-4466 User-story description","full_name":"[Issue].[myEPIC].[myEPIC-4466 User-story description]","key":"myEPIC-4466","parent_full_name":"[Issue].[myEPIC]"}],"bookmarked_members":[],"current_page_members":["[Issue].[myEPIC].[myEPIC-4455- incident PC - description]","[Issue].[myEPIC].[myEPIC-4466 User-story description]"]}]},"options":{},"view":{"current":"line_chart","maximized":false,"line_chart":{"area":true,"stacked":false,"swap_axes":false,"data_labels":false,"series_options":{}}}}
 } ],
 "calculated_members": [{"dimension":"Measures","name":"Original estimated hours with sub-tasks","format_string":"#,##0.00","formula":"[Measures].[Original estimated hours] +\n[Measures].[Sub-tasks original estimated hours]","annotations":{"group":"Default"}},{"dimension":"Measures","name":"Hours spent with sub-tasks","format_string":"#,##0.00","formula":"[Measures].[Hours spent] +\n[Measures].[Sub-tasks hours spent]","annotations":{"group":"Default"}},{"dimension":"Measures","name":"Remaining estimated hours history","format_string":"#,##0.00","formula":"Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n [Measures].[Remaining estimated hours change]\n ))\n + [Measures].[Remaining estimated hours change]\n)","annotations":{"group":"Default"}},{"name":"Cumulative hours spent with sub-tasks","dimension":"Measures","formula":"CASE WHEN [Measures].[Hours spent with sub-tasks] > 0 THEN\nSum(\n {\n PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember\n },\n [Measures].[Hours spent with sub-tasks]\n)\nEND","format_string":""},{"name":"Hours spent with sub-tasks with history","dimension":"Measures","formula":"Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n ([Measures].[Sub-tasks hours spent])\n ))\n + ([Measures].[Sub-tasks hours spent])\n)\n+\nCache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n ([Measures].[Hours spent])\n ))\n + ([Measures].[Hours spent])\n)","format_string":""}]
}

Do you need more information ?

Thank you for your help.

Have a nice day,

Emile