Our JIRA Issue has a company field indicating the companies affected by this issue. I would like to
a) Need help in creating a calculated metric - a count of these fields
example :
Issue ABC-123, company id: apple, microsoft, google , count = 3
Issue DEF-458, company id : null ( empty) , count = 0
issue XYZ-232, company id: eazybi , count = 1
b) Is it possible to include this as a column in the simple report of open issues
Issue | open | company affected count
First, make sure you imported the Jira custom field “company id” in eazyBI (at least as property).
Then create a new user-defined calculated measure (with integer format).
CASE WHEN
Not IsEmpty(
[Measures].[Issue company id]
)
THEN
Val(Len([Measures].[Issue company id]) - Len(Replace([Measures].[Issue company id], ',', ''))+1)
ELSE
0
END
It would count the number of values from the issue-level property
Finally, create a issue-level report with issues in rows, select “Issues created” in and the new measure in columns.
Then select “Status” dimension as page filter and filter only open statuses.
And make sure you enable “Nonempty” cross join for report rows, to show only related issues to report context.
Martins / eazyBI support
Great. thanks for the help.
Have one question though. How did u enable the Issue company id column to be displayed?
in the nice example you quoted( and thanks for highlights)
I see the column Issues Created | Issue status | Issue company id | count of companies affected.
How do i show the various fields of the issue as columns?, i.e, in this case, the issue company id, that is a good data to provide beside the count.
@vjn
Here is how to import custom fields in eazyBI cube:
https://docs.eazybi.com/eazybi/data-import/data-from-jira#DatafromJira-JiraCustomFields
Make sure you import all your fields as properties, then find them by prefix “Issue…” in the “Meaures” dimension and select for the report with issue-level details in rows.
Martins / eazyBI
thanks.
sorry . did not get you. my custom fields are all imported. dont have a problem with that.
i would like to know how and where can i add the extra columns to display on that report.
in the above example, you added company id into the report, how can i do that ?
@vjn
I selected the property “Issue Company ID” from the “Meaures” dimension and it appeared as new column in my report.
This property appeared after I imported the “Company ID” field as property.
Martins / eazyBI
Thanks. I did not see it earlier, it was nested in one of the measures.
appreciate your timely response and help. this can now be closed.
I’m trying to do something very similar on a custom field. I’ve got it mostly working, but some of my data has commas in it. this is making the count increase each time even though there is only one entry in the field. How can I escape these commas so that the entry is only counted once?
eg.
Customer “MyCompany, Inc” is currently being counted as 2 customers instead of 1, because of the comma in the name
Thanks!
In that case, make sure you import your custom field as dimension and then create a different formula to count customers:
Sum(
DescendantsSet([Customer].CurrentMember,[Customer].[Customer]),
CASE WHEN
[Measures].[Issues created]>0
THEN
1
END
)
Martins / eazyBI
Thanks, but unfortunately that is not working either. It correctly counts a single customer “MyCustomer,Inc” as one customer, but in the case when there are multiple customers associated with the issue, this formula counts them all as 1 as commas are also being used as the separator between values. Any way to not have that happen?
Try this formula now:
Count(
Filter(
DescendantsSet([Customer].CurrentMember,[Customer].[Customer]),
[Customer].Currentmember.name <> "(none)"
AND
[Measures].[Issues created]>0
)
)
Martins / eazyBI
thats giving me the same result as the previous formula - counts all entries as 1
If possible, please share a screenshot from one Jira issue key example with the Customer field values where multiple customers are entered.
Martins /eazyBI
thanks for your help with this!! Unfortunately I can not post any customer names and I don’t have a sandbox environment where i can setup dummy accounts.
I can tell you that the field being used is Parent Account Name and it is using structured data that it pulls from a database where names are stored corresponding to account IDs. To enter data in the field you must choose from the predefined list.
does that help at all?
thanks again
Have you imported the “Customer” dimension as a multi-value dimension from import options page?
Maybe that is the problem why it doesn’t now how to count them correctly.
Martins
Hi @martins.vanags Under the same example that you give, how could I add specific values between commas, that is, D11-107 count only the values that have the word app_1, that is, that it gives me 1 as a result.
@Marcelo_Ignacio_Cid1
There is no good regular expression to count specific strings using the first approach.
If the field “Customer” (multi-value field) is imported as dimension, you can try this formula:
Sum(
Filter(
DescendantsSet([Customer].CurrentMember,[Customer].[Customer]),
[Customer].Currentmember.name = "app_1"
),
CASE WHEN
(
[Time].Currenthierarchy.DefaultMember,
[Measures].[Issues created]
)>0
THEN
1
END
)
Martins