How would i create a report which displays the issues created (count) after sprint start date and before sprint end date for all the sprints

Questions & Answers
1

How would i create a report which displays the issues created (count) after sprint start date and before sprint end date for all the sprints…

1 Like
2

Hi Team, There is no update on this request… Is this an active community where i can get my queries resolved if not could you please suggest which community should i use to post my querires …

3

Hi,

Thank you for the reminder and sorry that we can not always respond as soon as we would wish to.

The solution is to create a calculated measure iterating over the Issue dimension and counting the issues created within the period of sprint time frame and added to the sprint:

nonzero(count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween(
      [Measures].[Issue created date],
      [Measures].[Sprint start date], 
      [Measures].[Sprint end date]
    ) and
    [Measures].[Sprint issues added]>0
  )
))

You can use the measure with the Sprint dimension in reports to count the number of issues created during the sprint and added to the sprint.

Kindly,
Janis, eazyBI Support

1 Like
4

Thankyou janis for sharing the solution to the above query… there is another query which is still open for long time.

5

Hi Janis,

i want to create a calculated member which gives the Reopened ‘count’ which is the aggregation of all the reopens which includes multiple times each issue got reopened and single reopen…
Please note i wanted to use user defined calculated members rather than selected members…

6

Hi - this looks great; how would I change the measure to only filter on specific issue type

i tried to modify the 2nd line to show Filter(Descendants([Issue].CurrentMember,[Issue Type].[Bug]),
but it did not work.

7

Davor,

there might be several ways to do this change in formula. Perhaps, the most efficient way is to add one more condition to the filter expression, selecting only the issues of the type you wish:

nonzero(count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween(
      [Measures].[Issue created date],
      [Measures].[Sprint start date], 
      [Measures].[Sprint end date]
    ) and
    [Measures].[Issue type]="Bug" and
    [Measures].[Sprint issues added]>0
  )
))

Another solution is to use the tuple with the “Sprint issues added” like this:

nonzero(count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween(
      [Measures].[Issue created date],
      [Measures].[Sprint start date], 
      [Measures].[Sprint end date]
    ) and
    ([Measures].[Sprint issues added],
     [Issue type].[Bug])>0
  )
))

Kindly,
Janis, eazyBI support

8

Hi Janis

I used both formulas to create a measure and they are returning different results. I also tried to change Bug to Defect Sub-task and on one measure i am not getting any results and another I am getting just 1 result for a sprint even thought i checked manually and there should be more results.

11%20PM
Screen Shot 2018-07-17 at 1.32.11 PM.png732×898 19.4 KB

9

Hi @janis.plume,

Using the below Formula, I was able to get the number of Bugs Created during the Sprint but getting “No Data found” when drilling through issue. Can you please assist me.

nonzero(count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween(
      [Measures].[Issue created date],
      [Measures].[Sprint start date], 
      [Measures].[Sprint end date]
    ) and
    [Measures].[Issue type]="Bug"
  )
))

Thanks,
Bhavikaa

10

Hi, @Bhavikaa,

Please add the following line in the formula to let the drill-through issues work:

-- annotations.drill_through_non_empty = false

One more comment is that your formula currently relies on issue properties only. It is recommended to use at least one “real” measure:

-- annotations.drill_through_non_empty = false
nonzero(count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween(
      [Measures].[Issue created date],
      [Measures].[Sprint start date], 
      [Measures].[Sprint end date]
    ) and
    ([Measures].[Issues created],
    [Issue type].[bug],
    [Sprint].DefaultMember)>0
  )
))

Kindly,
Janis, eazyBI support

11

Hi @janis.plume,

Thank you so much. Drill through issues worked by adding that line you provided.

Regarding the real measure thing you mentioned, I want to provide you few details about our requirement. In our case, Bugs will not be the part of Sprint, they will just be in backlog and all other issue types will go into sprints. So we wanted to get the no. of bugs created during the particular sprint duration.

Please let me know if this is not the right approach/formula to achieve this.

Thanks,
Bhavikaa