Hi,
I’m looking to create a report in EazyBI to track the number of tickets that transitioned to status D from statuses A, B, and C on a daily basis. Additionally, I have other statuses that I need to include in the report.
I want the report to show:
Can anyone suggest a formula or method to achieve this?
Hi @Sanjay
Try using tuples in calculated measures.
For example, this formula example would let you count issues that transitioned to status D from one of three other statuses A, B and C
Sum(
{
[Transition].[A => D],
[Transition].[B => D],
[Transition].[C => D]
},
[Measures].[Transitions to status issues count]
)
This calculated measure should let you filter or group results by Time dimension.
Martins / eazyBI team
Hi @martins.vanags
Thank you for the reply and yes its working but just wanted to confirm how can i confirm that the records are distinct and does not contain duplicate ?
Is there any function which can be used here?
Hi,
The measure “Transitions to status issues count” counts each issue once in the period, if the issue made the transition one or multiple times.
Martins / eazyBI
Thank you for confirming.
Hi @martins.vanags
I think we have a bug here. I am using “Transitions to status issues count” but still able to see duplicate entries when it returns the record. In my case, here is the formula used
In report, it is showing a total count of 10 but in actual there were only 9 tickets which were moved to QA UAT and 1 ticket was moved twice to UAT on same day.
@Sanjay
Please share more details about the report.
What other dimensions do you use in report?
Martins / eazyBI
Sure @martins.vanags . Here’s the screenshot of the report
Other columns are just like QA UAT status. Labels contains the team names which i have added as a filter.
It looks like you are filtering the report by calculated member “All teams” in “Label” dimension.
Likely this is an aggregate of multiple labels.
That creates the multi-value behavior in the report query.
In those cases, issue can be duplicated in results if it has more than 1 label in Jira.
For example, there is an Issue ABC-10, which has three labels in jira “orange”,“apple”,“raspberry”
Next, you create a calculated member “Favorite fruits” in “Label” dimension with this code:
Aggregate(
{
[Label].[orange],
[Label].[apple]
}
)
As you can see, it aggregates two labels that the issue has.
When you select the “Favorite Fruits” from “Label” dimension page filter, issue ABC-10 would be duplicated in report results (for all measures except distinct measures), because it has both orange and apple from the “Favorite Fruits” - it is counted for each of it’s
That would give you result 2 for the issue ABC-10 in report even if you use the measure “Transitions to status issues count”.
There are four distinct measures in eazyBI. They are “Issues created count”, “Issues resolved count”, “Issues closed count” and “Issues due count” and would return issue just once per label set in the aggregate, but there isn’t one that you can use for transition count, unfortunately,
The only workaround if you need to aggregate multiple labels in the Label dimension in report is creating a more complex formula in the calculated measures.
Sum(
DescendantsSet(
[Issue].CurrentMember,
[Issue].[Issue]
),
CASE
WHEN
Sum(
{
[Transition].[In Development => In QA (UAT)],
[Transition].[Code Review => In QA (UAT)],
[Transition].[Ready to verify => In QA (UAT)],
[Transition].[In QA (Stage) => In QA (UAT)],
[Transition].[Complete (Dev/QA) => In QA (UAT)],
[Transition].[Backlog => In QA (UAT)]
},
CoalesceEmpty([Measures].[Transitions to status issues count], 0)
) > 0
THEN
1
END
)
Martins / eazyBI
Thanks @martins.vanags but after using it on all statuses my report started crashing. It was giving timeout error. So now i have switched back to the older query.
Hi @Sanjay
Timeout error might point to a complexity in the report query when iterating through all issues multiple times.
I see you have multiple measures. I wonder what formulas do you use for each of these measures in your report.
If you iterate through all issues with the DescendantsSet function in each of the measures, that adds significant complexity to your report performance, and likely, it is not enough with the default 60-second timeout to run the report.
Please export and share the definition of your report where you noticed the timeouts with new formula. Perhaps there is a better way to calculate the distinct count of issues in each of columns.
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports/export-and-import-report-definitions
Another way would be to introduce and use a Select list (single-choice) custom field for Teams in Jira. Then, your previous formula should work as expected, if there are no aggregates from multi-value field dimension members (such as “Label”).
Martins / eazyBI
Hi @martins.vanags
Here’s the export of my report :-
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “QA Transition Metrics”,
“folder_name”: “AutoEntry By Sage - QA”,
“result_view”: “bar_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[QA Stage]”,“[Measures].[Code Review]”,“[Measures].[In Development(Staging)]”,“[Measures].[Unclear(Staging)]”,“[Measures].[QA UAT]”,“[Measures].[Ready to deploy]”,“[Measures].[In Development(UAT)]”,“[Measures].[Unclear(UAT)]”],“members”:}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time].[Today]”],“members”:,“bookmarked_members”:}]},“pages”:{“dimensions”:[{“name”:“Label”,“selected_set”:[“[Label].[All Teams]”,“[Label].[Prometheus(AE)]”,“[Label].[Atlas(AE)]”,“[Label].[Kronos(AE)]”,“[Label].[Cosmos(AE)]”,“[Label].[Customer Support(AE)]”],“members”:[{“depth”:0,“name”:“All Teams”,“full_name”:“[Label].[All Teams]”,“calculated”:true,“drillable”:true,“dimension”:“Label”}],“bookmarked_members”:,“current_page_members”:[“[Label].[All Teams]”]},{“name”:“Time”,“duplicate”:true,“selected_set”:[“[Time].[Today]”,“[Time].[Yesterday]”,“[Time].[Current month]”,“[Time].[Previous month]”],“members”:[{“depth”:0,“name”:“Today”,“full_name”:“[Time].[Today]”,“calculated”:true,“drillable”:true,“id”:“[Time].[Today]”,“format_string”:“”}],“bookmarked_members”:,“current_page_members”:[“[Time].[Today]”]}]},“options”:{},“view”:{“current”:“bar_chart”,“maximized”:false,“bar_chart”:{“stacked”:false,“vertical”:true,“swap_axes”:true,“data_labels”:true,“series_options”:{}},“table”:{}},“calculated_members”:}
} ],
“calculated_members”: [{“dimension”:“Time”,“name”:“Current month”,“format_string”:“”,“formula”:“Aggregate({\n [Time].[Month].CurrentDateMember\n})”},{“dimension”:“Time”,“name”:“Previous month”,“format_string”:“”,“formula”:“Aggregate({\n [Time].[Month].CurrentDateMember.PrevMember\n})”},{“name”:“Prometheus(AE)”,“dimension”:“Label”,“formula”:“Aggregate(\n Filter(\n [Label].Members,\n [Label].CurrentMember.Name = "backend"\n AND \n NOT ([Label].CurrentMember.Name = "CSBug")\n )\n)”,“format_string”:“”},{“name”:“Atlas(AE)”,“dimension”:“Label”,“formula”:“[Label].[XPE]”,“format_string”:“”},{“name”:“Kronos(AE)”,“dimension”:“Label”,“formula”:“[Label].[SFA]”,“format_string”:“”},{“name”:“Cosmos(AE)”,“dimension”:“Label”,“formula”:“[Label].[productboostteam]”,“format_string”:“”},{“name”:“Customer Support(AE)”,“dimension”:“Label”,“formula”:“Aggregate(\n Filter(\n [Label].Members,\n [Label].CurrentMember.Name = "CSBug"\n AND \n NOT ([Label].CurrentMember.Name = "SFA")\n AND\n NOT ([Label].CurrentMember.Name = "XPE")\n AND\n NOT ([Label].CurrentMember.Name = "productboostteam")\n AND\n NOT ([Label].CurrentMember.Name = "xpe")\n )\n)”,“format_string”:“”},{“name”:“All Teams”,“dimension”:“Label”,“formula”:“Aggregate(\n Filter(\n [Label].Members,\n [Label].CurrentMember.Name = "productboostteam"\n OR \n [Label].CurrentMember.Name = "CSBug"\n OR \n [Label].CurrentMember.Name = "backend"\n OR \n [Label].CurrentMember.Name = "XPE"\n OR \n [Label].CurrentMember.Name = "xpe"\n OR \n [Label].CurrentMember.Name = "sfa"\n )\n)”,“format_string”:“”},{“name”:“Today”,“dimension”:“Time”,“formula”:“Aggregate({\n [Time].[Day].CurrentDateMember\n})”,“format_string”:“”},{“name”:“Yesterday”,“dimension”:“Time”,“formula”:“Aggregate(\n {\n [Time].[Day].CurrentDateMember.PrevMember\n }\n)”,“format_string”:“”},{“name”:“QA Stage”,“dimension”:“Measures”,“formula”:“Sum(\n {\n [Transition].[In Development =\u003e In QA (Stage)],\n [Transition].[Code Review =\u003e In QA (Stage)],\n [Transition].[Ready to verify =\u003e In QA (Stage)],\n [Transition].[In QA (UAT) =\u003e In QA (Stage)],\n [Transition].[Complete (Dev/QA) =\u003e In QA (Stage)],\n [Transition].[Backlog =\u003e In QA (Stage)]\n },\n CoalesceEmpty([Measures].[Transitions to status issues count], 0)\n)”,“format_string”:“”},{“name”:“In Development(Staging)”,“dimension”:“Measures”,“formula”:“Sum(\n {\n [Transition].[In QA (Stage) =\u003e In Development]\n },\n CoalesceEmpty([Measures].[Transitions to status issues count], 0)\n)”,“format_string”:“”},{“name”:“Code Review”,“dimension”:“Measures”,“formula”:“Sum(\n {\n [Transition].[In QA (Stage) =\u003e Ready to verify],\n [Transition].[In QA (Stage) =\u003e Code Review]\n },\n CoalesceEmpty([Measures].[Transitions to status issues count], 0)\n)”,“format_string”:“”},{“name”:“Unclear(Staging)”,“dimension”:“Measures”,“formula”:“Sum(\n {\n [Transition].[In QA (Stage) =\u003e Unclear]\n },\n CoalesceEmpty([Measures].[Transitions to status issues count], 0)\n)”,“format_string”:“”},{“name”:“QA UAT”,“dimension”:“Measures”,“formula”:“Sum(\n {\n [Transition].[In Development =\u003e In QA (UAT)],\n [Transition].[Code Review =\u003e In QA (UAT)],\n [Transition].[Ready to verify =\u003e In QA (UAT)],\n [Transition].[In QA (Stage) =\u003e In QA (UAT)],\n [Transition].[Complete (Dev/QA) =\u003e In QA (UAT)],\n [Transition].[Backlog =\u003e In QA (UAT)]\n },\n CoalesceEmpty([Measures].[Transitions to status issues count], 0)\n)”,“format_string”:“”},{“name”:“In Development(UAT)”,“dimension”:“Measures”,“formula”:“Sum(\n {\n [Transition].[In QA (UAT) =\u003e In Development]\n },\n CoalesceEmpty([Measures].[Transitions to status issues count], 0)\n)”,“format_string”:“”},{“name”:“Ready to deploy”,“dimension”:“Measures”,“formula”:“Sum(\n {\n [Transition].[In QA (Stage) =\u003e Ready to Deploy],\n [Transition].[Code Review =\u003e Ready to Deploy],\n [Transition].[Ready to verify =\u003e Ready to Deploy],\n [Transition].[In QA (UAT) =\u003e Ready to Deploy],\n [Transition].[In QA (Stage) =\u003e Passed UAT],\n [Transition].[Code Review =\u003e Passed UAT],\n [Transition].[Ready to verify =\u003e Passed UAT],\n [Transition].[In QA (UAT) =\u003e Passed UAT]\n },\n CoalesceEmpty([Measures].[Transitions to status issues count], 0)\n)”,“format_string”:“”},{“name”:“Unclear(UAT)”,“dimension”:“Measures”,“formula”:“Sum(\n {\n [Transition].[In QA (UAT) =\u003e Unclear]\n },\n CoalesceEmpty([Measures].[Transitions to status issues count], 0)\n)”,“format_string”:“”}]
}
@martins.vanags I have also one more question regarding duplicates. I have created another calculated measure named as “Outflow” in different report in which i want to show the sum of measures used in this report like “Ready to deploy”, “In development(UAT)”, “Unclear(UAT)”.
Is there a way to avoid duplicates here as well in “Outflow” measure?
The approach is the same, you need to iterate through “Issue” dimension at issue level (using Descendants or DescendantsSet function) to count each issue just once per set.
That usually takes time and makes reports significantly slower.
I would recommend introducing single-choice fields to use on issues for teams.
Or run the report for one team at a time in page filter (if you stick to the “Label” field) - that would let you keep your current formulas and avoid duplications.
Martins / eazyBI
@martins.vanags We have introduced the single -choice fields for teams but i am unable to see it in available dimension list. Any suggestions on how to locate new single choice fields?
@Sanjay
Can you confirm that the field is already present to issue screen in Jira and at least one issue has value for the new Select list (single-choice) field?
Martins / eazyBI
@martins.vanags Yes, i can confirm that atleast 1 issue has the value for new select list.
@Sanjay
You could reach out to eazyBI support and provide screenshots from the
Martins / eazyBI