# Is there a way to count number of reopens in a sprint and how many times each ticket got reopened

Hello,

Is there a way to count number of reopens in a given sprint and how many times each ticket / issue got reopened.
I have used transition status and transition status count but when i check the issue in Jira i see a mismatch in the count what is shown in Eazy bi vs Jira under transition

1 Like

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 …

Hi,

This community is still young and growing; sometimes answers are not provided as quickly as you would hope.

I understand that you would like to get only those transitions which happened during dates while a sprint was active. Measures Transitions to status and Transitions to status issues count are associated with Sprint dimension through issues within a sprint. Therefore measures may return transitions an issue had even before a sprint had started.

This is what you may do to get an accurate amount of transitions during a sprint.

1. Define a new calculated measure to get only measure Transition to status values only for a period while a selected sprint was active. A formula may look like this:

``````    ([Measures].[Transitions to status],
[Sprint Status].[Active])``````
2. Define another calculated measure to return count of issues which transitioned while a selected sprint was active. A formula may look like this:

``````    ([Measures].[Transitions to status issues count],
[Sprint Status].[Active])``````
3. I recommend using Transition dimension to select particular reopening transitions you are interested in.

Best,
Zane Baranovska / support@eazybi.com

1 Like

Thankyou Zane for sharing the solution. Could you please let me know how would i get percentage of reopens in a given sprint which includes multiple reopens of an issue + issues reopened once

Any update Zane on the above request…

From your question, it is not clear on what measure would you like to calculate the rate of active sprint reopens.
However, the approach for the rate calculation is the same: measure with specified context divide by another measure with more broad context, and measure formatting is set to return value as a percentage.

For example, to calculate the rate of transitions to reopen while a sprint was active against transition to reopens regardless of sprint status during transitions, you may use a formula like this:

``````-- transitions while sprint was active
([Measures].[Transitions to status],
[Sprint Status].[Active])
/
-- transitions regardless of sprint status
([Measures].[Transitions to status],
[Sprint Status].DefaultMember)
``````

Set measure formatting to “##% Integer percentage” or “##.##% Decimal percentage”.

I recommend defining both arguments for rate calculation as separate measures first so that you may validate what they return. And then construct the formula for the rate calculation.

Best,
Zane Baranovska / support@eazybi.com

[quote=“zane.baranovska, post:6, topic:340”]
([Measures].[Transitions to status],
[Sprint Status].[Active])
[/quote

Sorry for the ambiguity in the earlier query… here is what i was looking for

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…

Hi,

To count issue reopenings you could first ensure that issue change history is selected and imported via eazyBI import settings.
Next, create a calculated member in “Transition” dimension to aggregate all transitions which you define as ‘reopenings’

``````Aggregate({
[Transition].[Done => In Progress],
[Transition].[Done => To Do],
[Transition].[Done => Done],
[Transition].[Done => In Review]
})
``````

Now you could use this member to create a new calcualated measure with tuples code

``````(
[Measures].[Transitions to status],
[Transition Status].[<new calculated member with aggregation>]
)
``````

Best regards,
Martins / eazyBI team

I am trying to do something similar and keep getting Syntax errors. I am trying to get a count of the number of transitions from one status (Testing) to another (Open). Here is what I have:

Aggregate({[Transition].[Testing => Open]})

I get the following error:

Failed to parse query, try to make query simpler.
Or maybe saved report uses deleted calculated member.
Error message:
All arguments to function ‘{}’ must have same hierarchy.

Can you provide the correct syntax?

Hi,

This seems like a correct syntax. Do you see the same error if you select the measure “Transitions to status” in columns as the only measure in the report?

Anyway, please save your report after this error, then export and share the report definition
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Exportandimportreportdefinitions

Martins / eazyBI support

Hello all,
I have a similar request: is it possible to see the number of re-opened tickets in a certain component, and how many times a specific ticket was re-opened?
I don’t mean in the last sprint, but rather for the entire history of the Project.
Thank you,

-Noam

@Gnome

There can be differences between the “reopening” definition.
What defines the reopening in your Jira instance? Do you use some specific status when you reopen the issue or do you simply remove the resolution each time when issue is reopened?

Martins / eazyBI

@martins.vanags

Hi Martins,
In our JIRA instance there is an attribute called “reopened” in the “status” field. I would like to see all the tickets which their status is reopened, and if possible the number of times a specific ticket’s status was changed to reopened.

@Gnome

Then you can use the “Status” dimension as a page filter and select the “Reopened” value,
Next, filter the report to show only rows that belong to this status using the filter on column “Issues created” (issues created > 0)
After, that create a new calculated measure using tuples
This formula will count the number of times the issue reached the status Reopened:

``````(
[Measures].[Transitions to status],
[Transition Status].[Reopened]
)
``````

Martins / eazyBI

Thanks @martins.vanags !
Will try your suggestion and let you know how it turned out.
Best,

-Noam

@martins.vanags
Hi,
I copied the formula you suggested, and received an error message.
Seems that the JIRA instance that I use doesn’t have the object: “[Transition Status].[Reopened]’”

Do you have a solution to this error?

Best,

-Noam

Hi,

Please make sure that you have status “Reopened” in Jira and that it is used for issues you imported in eazyBI.

Martins / eazyBI

Hi @martins.vanags ,
The “Reopened” status is in the JIRA instance, see pic below.

Hi,

Make sure that you have imported issue change history in your eazyBI account:
https://docs.eazybi.com/eazybijira/data-import/jira-issues-import/import-issue-change-history

If you did, check if you can find the “Transition Status” dimension available.
Then check members for this dimension when using it in rows and selecting the measure “Transitions to status”.
If reopened is one of Jira statuses, it should be created as member in “Transition Status” dimension after you import change history for issues.

Martins / eazyBI

Thanks a lot @martins.vanags , it works perfectly now.

-Noam