Get time spent for issues that were added later in the sprint

Questions & Answers
, , ,
1

Hello

I’d like to build an activity report with:

  • in rows : issues
  • in columns : days
  • in cells : the time spent on each issue each day

I want to filter tickets by sprint, so I put the sprint dimension as page. However, sometimes issues change sprints (after time spent have been declared), and it is important that the report shows issues that are currently in in the sprint filtered
(regardless of their sprint at the time the time spent was declared) , and that all times spent appear in the table (not just times reported when the issue was in the sprint).

I tried several modifications of my measure but I didn’t got what I wanted, here is my measure :

Sum(
  Filter(
     Descendants([Issue].DefaultMember,[Issue].[Issue]),
     [Measures].[Issue Sprint] = [Sprint].CurrentHierarchyMember.Name
  ),
    [Measures].[Hours spent]
)

Unfortunately, times spent that are declared while the ticket is not in the filtered sprint do not go up in this table.

Can you help me ?

Thanks

Thomas

2

@ThomasLegrand

Try this formula for your calculation:

Sum(
  Filter(
     Descendants([Issue].DefaultMember,[Issue].[Issue]),
     [Measures].[Issue Sprint] = [Sprint].CurrentHierarchyMember.Name
  ),
  CASE WHEN
  (
  [Measures].[Issues created],
  [Time].Currenthierarchy.Defaultmember
  )>0
  THEN
    Sum([Measures].[Hours spent],[Sprint].Currenthierarchy.Defaultmember)
  END
)

Martins / eazyBI

3

Thanks @martins.vanags for your answer, but it doesn’t work. It returns “No report data to show.” (I apply the filter “hide empty rows and columns”).

4

@ThomasLegrand

Is your report filtered by “SPrint” dimension and is just one sprint selected in page filter?

Martins / eazyBI

5

@martins.vanags , Yes my report is filtered by sprint, and just one is selected in page filter
(maybe in the future I would try to be able to filter over several sprints but I’m not there, currently I have only one filtered sprint)

However, I forgot to mention some information: in rows, I also have another dimension (logged by), and another filter (Time). And I see that by removing this logged by dimension in rows, and the Time filter, the proposed measure works.

I’m going to think about how to adapt the measurement to make it compatible with these two dimensions, if you have any suggestions I’m all ears.

6

@ThomasLegrand

It must be the “Logged by” then.

Try this formula:

Sum(
  Filter(
     Descendants([Issue].DefaultMember,[Issue].[Issue]),
     [Measures].[Issue Sprint] = [Sprint].CurrentHierarchyMember.Name
  ),
  CASE WHEN
  (
  [Measures].[Issues created],
  [Time].Currenthierarchy.Defaultmember,
  [Logged by].Currenthierarchy.Defaultmember
  )>0
  THEN
    Sum([Measures].[Hours spent],[Sprint].Currenthierarchy.Defaultmember)
  END
)

Martins

7

thank you @martins.vanags , I did some tests, and what is blocking is that I selected all the members of the dimension logged by at the “User” level. With this selection (and the measure you gave me), I miss some data in my table (the same as in my initial post: the times spent when the issue was not in the sprint), but if I delete it, then it seems correct.

However, I would like to keep it to simplify the reading.

8

@ThomasLegrand
what exactly do you mean “but If I delete it”?
What do you delete in this scenario?

Also, could you check if the missing hours are logged on issues that have “(no sprint)” property in eazyBI report (if you create a completely new report with issues in rows)

Martins

9

@martins.vanags
i will try to show you

If I remove the “logged by” dimension from the report, the table is complete (I’m looking at the red cell, whose time spent was entered before the issue was put into the “RUN Mars-Avril 2023” sprint).

image
image1271×710 58 KB

If I add the “logged by” dimension, it’s still OK (but I don’t have the “looged by” info):

image
image1295×795 91.4 KB

However, if I display the “users” level members of the dimension “logged by”, then the red cell disappears

image
image1291×662 80 KB

10

@ThomasLegrand
I believe the “Nonempty” rows filter (when enabled) is hiding the user-level rows.
Here is what you can try.

  1. remove (temoprarily) the dimension 'Temps" from the report columns and pages
  2. filter the report by the measure "Temps pase CRA (JH) as explained here (measure > 0)
    Create reports
  3. then disable the “Nonempty” rows filter
    Create reports
  4. add back “Temps” dimension to the report.

The report should show results also for user-level

Let me know if that helped!
Martins / eazyBI