I am trying to convert the below JQL filters into calculated field:
project = ABC AND “Technology Environment” in (Distributed,“Distributed,Mainframe”,“Mainframe,Distributed”) and status != Cancelled and “Life Cycle Status” != exit
project = TCAP AND “Technology Environment” in (Distributed,“Distributed,Mainframe”,“Mainframe,Distributed”) and status != Cancelled and “Life Cycle Status” !~ exit and “Company Initiative” in (Arena,Hilton,Jungle,Klover,Marriott,Pluto)
project = TCAP AND “Technology Environment” in (Distributed,“Distributed,Mainframe”,“Mainframe,Distributed”) and status != Cancelled and “Life Cycle Status” !~ exit and “Company Initiative” in (Arena,Hilton,Jungle,Klover,Marriott,Pluto) and “Distributed Build” in(“In Use”)
This would likely be easier if you placed these dimensions into Pages to create complex filters rather than trying to create a custom calculated member. It would also end up faster for you.
I’m assuming you would have issues on rows and some calculated measure in columns from the measures dimension.
Then you could put Project, 'Technology Environment", Status, “Life Cycle Status”, “Company Initiative”, and “Distributed Build” into Pages and then select the different combinations to get the results you’re looking for.
I tried that way but it conflicts with other calculated fields I defined so that is the reason I will have to convert these jql’s to calculated fields to match the report.
In the below report each column is a different calculated field.
Ok if that’s the case it seems that using Aggregate and Filter functions would be the best path forward to get a count
Ex:
Aggregate(
Filter([Issue].[Issue].Members,
[Project].CurrentHierarchyMember.Name = “ABC” AND ([Technology Environment].CurrentHierarchyMember.Name = “Distributed” OR [Technology Environment].CurrentHierarchyMember.Name = “Distributed,Mainframe” OR [Technology Environment].CurrentHierarchyMember.Name = “Mainframe,Distributed”) AND [Measures].[Issue status] != “Cancelled” AND [Life Cycle Status].CurrentHierarchyMember.Name != “exit”)
)
Assumptions here are that Technology Environment is a dimension as well as Life Cycle Status. If they are merely properties of an issue then you can use the get() or getProperty() function to pull out those values and aggregate them accordingly.
Thanks again for your quick response.
Just fyi, I had to change not equal to symbol (!=) to <> in the query.
However, I am getting the below 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.
Is there any specific property I should add this as calculated field ?
Try running that query without all of the pages that you have in place, and building it up one condition at a time. Whenever I’ve run into these issues I’ve always tried to focus on simplifying first and figuring out where things break before continuing forward.
Tried all the different ways and also by simplifying the query but I still get the error.
I will let you know if I find the correct one, thanks for your help.
In this case, you could try using as many dimensions in page filter as possible.
For dimensions where you expect to exclude some specific member, you could use aggregate & except functions when creating a calculated member in this specific dimension.
For example, status except Canceled
Similar one you could create for Life Cycle Status dimension.
And please make sure that all of your fields that you use in JQL are imported as dimensions in eazyBI.
What I was trying to say is you could create similar calculated members in any of your dimensions - (Technology Evnironment, Status, Lifce Cycle Status or Company Initiative) and then use these dimensions as page filters (like in my previous attachment)
Your original question was how to convert JQL to eazyBI report.
Usually the result of JQL is a list of issues therefore I shared a picture of report where “Issue” dimension is in columns and measure “Isues created” is used to show issues which belong to filters in page section.
I believe, in this case, you could create a new calculated member in “Measures” dimension using the following code example:
NonZero(
Count(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Issue].CurrentHierarchyMember.Parent.Name = "Tool Chain Automation Program"
AND
(
[Measures].[Issue technology Environment] = "Distributed"
OR
[Measures].[Issue technology Environment] = "Distributed,Mainframe"
OR
[Measures].[Issue technology Environment] = "Mainframe,Distributed"
)
AND
[Measures].[Issue status] <> "Cancelled"
AND
[Measures].[Issue Life Cycle Status] <> "exit"
AND
[Measures].[Issues created]>0
)
)
)
But before you use this code, please ensure that all your necessary custom fields are selected and imported via import options.
That should return the counter of issues which belong to the requirement and you can use this measure in your reports now.
If that is not the case, please reach out to eazyBI support and share some sample of the layout for your expected report.
What dimensions would you use in rows/columns and page filters for your final report?
If you use “Time” dimension then results would be calculated based on issues that were created in the selected timeframe because it is tied to “Issues created” > 0 (see the code above).
You could change that measure in code to “isssues resolved” > 0 then report would show results based on issues which were resolved in selected timeframe.
