Extract Min and Max Sprint Values from Issue Sprints

Questions & Answers
1

Given an entry, a comma separated string from Issue Sprints, on the Story level), I want to extract the minimum and then the max Sprint numeric values from the Issue Sprints “string”.

Example of Issue Sprints value: Dev + Test Iteration 124,Dev + Test Iteration 125,Dev + Test Iteration 126

In this case, the Minimum would be 124 and the Max would be 126.

What I am trying to achieve here are 2 additional columns in the report; where the 1st indicates the, extracted, Minimum Sprint Value (124 in this case) and the 2nd indicates the Maximum Sprint Value (126 in this case).

The following Case Statement (for the Min is this case) does not work, apparently as Issue Sprints is not imported as a measure, but it does convey the logic of what I am trying to achieve:
CASE
WHEN
[Measures].[Issue Sprints] = ’ Dev + Test Iteration 124’
THEN ‘124’
WHEN
[Measures].[Issue Sprints] = ’ Dev + Test Iteration 125’
THEN ‘125’
WHEN
[Measures].[Issue Sprints] = ’ Dev + Test Iteration 126’
THEN ‘126’
END

Your help is appreciated.

2

Hi, @willmax

Welcome back to the eazyBI community.

I hope I understand correctly: you are looking for the first and last Sprint’s “number” which is in the name.

We have the Issue Sprint’s property, which has the list of all sprints, but also we have Sprint’s ID stored, I’m suggesting using that, to get the List and then, take the first item (using Item(0)) and return the Name.
First sprint formula should look something like this:

[Sprint].[Sprint].getMembersByKeys(
  [Issue].CurrentHierarchyMember.get('Sprint IDs')
).Item(0).Name

For the last sprint, I’m suggesting using the Tail function:

Tail (
[Sprint].[Sprint].getMembersByKeys(
  [Issue].CurrentHierarchyMember.get('Sprint IDs')
),
1
).Item(0).Name

To get the number out of the names, please use the ExtractSting and correct regular expression. To make the value a numeric value - Cast as numeric might help.

The final formulas should look something like this:
For Min:

Cast (
ExtractString(
[Sprint].[Sprint].getMembersByKeys(
  [Issue].CurrentHierarchyMember.get('Sprint IDs')
).Item(0).Name
,
".*(\d+)",
  1
)
as NUMERIC )

For Max:

Cast (
ExtractString(
Tail (
[Sprint].[Sprint].getMembersByKeys(
  [Issue].CurrentHierarchyMember.get('Sprint IDs')
),
1
).Item(0).Name
,
".*(\d+)",
  1
)
as NUMERIC )

Kindly,
Ilze M

3

Hello @ilze.mezite. This Worked well. Thank you for breaking down your approach as you did. This helped greatly with my understanding!

When I ran, as is, just the final number of the Iteration was presented.
Example: Dev + Test Iteration 159 yielded 9

Adding the text that comes right before the numeric resulted in the presentation of the entire numeric value.

For the 1st Sprint:

Cast (
ExtractString([Sprint].[Sprint].getMembersByKeys([Issue].CurrentHierarchyMember.get(‘Sprint IDs’)
).Item(0).Name
, ‘.Iteration (\d+).’, 1)
as NUMERIC )

Thank you so much @ilze.mezite. This is very helpful.

May I ask further . . . What would be an efficient approach to account for Sprints from 2 separate projects, where the Sprint naming conventions are different?

Sprint Naming Convention 1: Dev + Test Iteration ###
Sprint Naming Convention 2: ATeam-###

4

This works, but perhaps is not the most efficient?

CASE
WHEN
Cast (
ExtractString([Sprint].[Sprint].getMembersByKeys([Issue].CurrentHierarchyMember.get(‘Sprint IDs’)
).Item(0).Name
, ‘.Iteration (\d+).’, 1)
as NUMERIC ) > 0
THEN
Cast (
ExtractString([Sprint].[Sprint].getMembersByKeys([Issue].CurrentHierarchyMember.get(‘Sprint IDs’)
).Item(0).Name
, ‘.Iteration (\d+).’, 1)
as NUMERIC )
WHEN
Cast (
ExtractString([Sprint].[Sprint].getMembersByKeys([Issue].CurrentHierarchyMember.get(‘Sprint IDs’)
).Item(0).Name
, ‘.ATeam-(\d+).’, 1)
as NUMERIC ) > 0
THEN
Cast (
ExtractString([Sprint].[Sprint].getMembersByKeys([Issue].CurrentHierarchyMember.get(‘Sprint IDs’)
).Item(0).Name
, ‘.ATeam-(\d+).’, 1)
as NUMERIC )
END