How to get Category Status of issue in Measures?

[tregubovav]

I need prepare reports from dozen Jira Projects which have different set of statuses and different workflows.

I would like to prepare user-defined measures based on [Category.Status] Dimension instead of calculating “Caregory” based Status Name.

Unfortunately I can’t find way how to extract Category status from Issue.

I will be much appreciated for any Ideas.

[tregubovav]

Looks like I found the answer in post: Story and Epic report and filters for updated date

P.S.
Product Documentation doesn’t allow to clearly understand all objects properties and objects relationship in the system.

[zane.baranovska]

@tregubovav,

In eazyBI information on status categories is already pulled in and there is no need to define calculated measures to group statuses by category. In dimensions Status and Transition Status, is hierarchy Category that groups all statuses by status categories To Do, In Progress and Done (see picture below).

Best,
Zane / support@eazyBI.com

[cybertachikoma]

@zane.baranovska
Could you describe the formula to get StatusCategory as Measure?
I believe, it should be something like:
[Issue].CurrentMember.Get()

Your answer is useful, but I try to understand EasyBI to be fammiar to use it.

[zane.baranovska]

@cybertachikoma,
The status category is imported only in the Status and Transition Status dimensions, not as a measure. A category is the property (attribute) of each status, and that property allows group statuses.

Each issue contains the identifier of the status but no other properties like status name or status category:

[Issue].CurrentHierarchyMember.get('Status ID')

If you would like to show the statute category for each issue, then you can use an expression like this:

--in Status dimension Category hierarchy, look for Status matching status ID assigned to issue
[Status.Category].[Status].getMemberByKey(
  [Issue].CurrentHierarchyMember.get('Status ID')
--for that status, get the parent (category) name
).Parent.Name

[Alsu_Zaynutdinova]

Hi @zane.baranovska ,

I am trying to make the same calculation field as you showed for Category, but for Sprints. I need to create a calc field for Sprint dimension that will show all sprints groups for one year.
Each sprint cycle contains a lot of sprints for different teams, so I need to be able select one year in Rows panel, then see something like:
All 2022 sprints / sprints by their number / groups within one sprint

Is it possible to create such custom field? So far I could only make All 2022 sprints / groups within one sprint

thank you in advance!
Alsu

[zane.baranovska]

@Alsu_Zaynutdinova grouping sprint by their parameters is a different use case than showing the statutes category name for each issue and requires another solution.

You might want to check out other Community topics related to Sprints and methods to group them by name or other properties:

Aggregate Sprint By Name

Hi @Gisela_Nogueira, You can add custom hierarchy that would group sprints by matching string or numeric properties (sprints with the same property value are grouped) in Sprint dimension. First, you might want to import criteria for grouping sprints as a property using additional data import: Additional data import into Jira Issues cube - eazyBI for Jira. For example, import planned start period as string, program increment, or some other attribute indicates which sprints should be grouped. …

Combining different teams sprints into one

With version 6.1 we added a new to create custom hierarchies in some dimensions, including Sprints. You would like to import custom properties for sprints to represent a potential grouping mechanism. For example, you can import a property cycle for Sprints with additional data import. Then you can use this custom property Cycle to build a new custom hierarchy in Sprint dimension and group sprints by cycles dynamically. Import additional property to Sprint based on Sprint naming pattern XX Spr…

Best,
Zane / support2eazyBI.com

[Alsu_Zaynutdinova]

Thank you @zane.baranovska for the reply.

[Ofer_Cohen]

Bringing back this subject. I used the above to get the Status Cateory as a measure and present it in a report (as Column). When I add this it breaks the filter. I have a filter to show specific issue type, but When I select to show this new Status Category measure, it shows other issue types
How can it be?
This is what I used:

[zane.baranovska]

@Ofer_Cohen the calcaultion is all right. The similar behaviour could be achieved using other issue properties that describes any issue that is on report rows.

You might want to add a predefined measure, such as “Issue created” to the report columns and enable option Nonempty on report rows, so eayzBI could correctly apply filter criteria (Create reports).

More details on report context and guidelines how to build report are described here:
https://docs.eazybi.com/eazybi/getting-started/main-concepts#Mainconcepts-Reportcontext
https://docs.eazybi.com/eazybi/analyze-and-visualize/best-practices-for-report-creation

Best,
Zane / support@eazyBI.com

[Ofer_Cohen]

Hi I have the non Empty set, and I still get other issue types (Bugs, Stories).
This is the Screen Shot:

And this is the report definition (Simplified Version):
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “ER list (CS) OLD”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Status ]”,“[Measures].[Issue type]”,“[Measures].[Status-Category]”,“[Measures].[Issues created]”],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Issue”,“selected_set”:[“[Issue].[Issue].Members”],“members”:[],“bookmarked_members”:[]}],“order_by”:{“expression”:[“[Measures].[Issue type]”],“order”:“ASC”},“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Issue Type”,“selected_set”:[“[Issue Type].[BRNGG R\u0026D Types]”,“[Issue Type].[All Issue Types]”],“members”:[{“depth”:0,“name”:“BRNGG R\u0026D Types”,“full_name”:“[Issue Type].[BRNGG R\u0026D Types]”,“calculated”:true,“drillable”:true,“expanded”:true,“removed”:true},{“depth”:1,“name”:“Task”,“full_name”:“[Issue Type].[Task]”,“parent_full_name”:“[Issue Type].[BRNGG R\u0026D Types]”}],“bookmarked_members”:[],“current_page_members”:[“[Issue Type].[Task]”]}],“nonempty_crossjoin”:true},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{“row_dimension_headers”:{“Issue”:“TASK”},“cell_formatting”:{},“freeze_header”:true}},“calculated_members”:[{“dimension”:“Measures”,“name”:“ER Status”,“formula”:“CASE WHEN (IsEmpty([Measures].[Related Issue type]))\nTHEN\n[Status].[Status].getMemberNameByKey(\n[Issue].[Issue].GetMemberByKey([Measures].[ER KEY]).get(‘Status ID’)\n)\nEND”,“format_string”:“StringFormatter”},{“dimension”:“Measures”,“name”:“ER Fix Version”,“formula”:“CASE WHEN (IsEmpty([Measures].[Related Issue type]))\nTHEN\n[Fix Version].[Version].getMemberNamesByKeys(\n [Issue].[Issue].GetMemberByKey([Measures].[ER KEY]).\n Get(‘Fix version IDs’), ’ ')\nEND”,“format_string”:“StringFormatter”},{“dimension”:“Measures”,“name”:“ER Customer Due Date”,“formula”:“CASE WHEN (IsEmpty([Measures].[Related Issue type]))\nTHEN\n[Issue].[Issue].GetMemberByKey([Measures].[ER KEY]).get(‘Customer Due Date’)\nEND”,“format_string”:“yyyy-mm-dd”},{“dimension”:“Measures”,“name”:“ER KEY”,“formula”:“[ERs-Related issues].CurrentMember.Name\n”,“format_string”:“MarkdownFormatter”},{“dimension”:“Measures”,“name”:“ER Old Status”,“formula”:“[Status].[Status].getMemberNameByKey(\n[Issue].[Issue].GetMemberByKey(\n[Issue].CurrentHierarchyMember.get(‘ER-IO Related issues’)).get(‘Status ID’)\n)\n”,“format_string”:“”},{“dimension”:“Measures”,“name”:“ER Link (Related)”,“formula”:“CASE WHEN (IsEmpty([Measures].[Related Issue type]))\nTHEN\n"["\n||Cast([Measures].[ER KEY] as string)\n||"]"\n||"(https://bringg.atlassian.net/browse/\”\n||Cast([Measures].[ER KEY] as string)\n||")"\nEND",“format_string”:“MarkdownFormatter”},{“dimension”:“Measures”,“name”:“Related Issue Estimate”,“formula”:“[Issue].CurrentHierarchyMember.get(‘High-level Estimation’)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“ER Link”,“formula”:“[Measures].[Issue Hyper Link]”,“format_string”:“MarkdownFormatter”},{“dimension”:“Measures”,“name”:“ER Product Domain”,“formula”:“CASE WHEN (IsEmpty([Measures].[Related Issue type]))\nTHEN\n[Issue].[Issue].GetMemberByKey([Measures].[ER KEY]).get(‘Product Domain’)\nEND”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Planned due date”,“formula”:“[Measures].[Issue due date]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Date 2”,“formula”:“[Measures].[Issue Target Release Date]”,“format_string”:“”}],“description”:“Presents ER list and its status”}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issue due date”,“format_string”:“mmm dd yyyy”,“formula”:“[Issue].CurrentHierarchyMember.get(‘Due date’)”},{“dimension”:“Measures”,“name”:“Issue type”,“format_string”:“”,“formula”:“[Issue Type].[Issue Type].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Issue type ID’)\n)”},{“dimension”:“Measures”,“name”:“Issue status”,“format_string”:“”,“formula”:“[Status].[Status].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Status ID’)\n)”},{“name”:“Issue Target Release Date”,“dimension”:“Measures”,“formula”:“[Issue].CurrentHierarchyMember.get(‘Target Release Date’)”,“format_string”:“mmm dd yyyy”},{“name”:“BRNGG R\u0026D Types”,“dimension”:“Issue Type”,“formula”:“Aggregate({\n [Issue Type].[Customer Bug],\n [Issue Type].[Enhancement Request],\n [Issue Type].[Story],\n [Issue Type].[Task],\n [Issue Type].[Bug],\n [Issue Type].[System Test]\n\n})”,“format_string”:“”},{“name”:“Related Issue Status”,“dimension”:“Measures”,“formula”:“[Status].[Status].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Status ID’)\n)”,“format_string”:“”},{“name”:“Related Issue type”,“dimension”:“Measures”,“formula”:“[Issue Type].[Issue Type].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Issue type ID’)\n)”,“format_string”:“”},{“name”:“Issue Hyper Link”,“dimension”:“Measures”,“formula”:“CASE WHEN (NOT IsEmpty([Measures].[Related Issue type]))\nTHEN\n"["\n||Cast([Issue].CurrentHierarchyMember.Key as string)\n||"]"\n||"(https://bringg.atlassian.net/browse/\”\n||Cast([Issue].CurrentHierarchyMember.Key as string)\n||")"\nEND\n",“format_string”:“MarkdownFormatter”},{“name”:"Status ",“dimension”:“Measures”,“formula”:“CASE WHEN [Measures].[Status-Category] = "To Do"\n Then "\u003cspan style=‘color:Black’\u003e" || [Measures].[Issue status] || "\u003c/span\u003e"\n WHEN [Measures].[Status-Category] = "In Progress"\n Then "\u003cspan style=‘color:Blue’\u003e" || [Measures].[Issue status] || "\u003c/span\u003e"\n WHEN [Measures].[Status-Category] = "Done"\n Then "\u003cspan style=‘color:Green;font-weight:bold’\u003e" || [Measures].[Issue status] || "\u003c/span\u003e"\n ELSE\n [Measures].[Issue status]\nEND\n “,“format_string”:“HTMLFormatter”},{“name”:“Status-Category”,“dimension”:“Measures”,“formula”:”[Status.Category].[Status].getMemberByKey(\n [Issue].CurrentHierarchyMember.get(‘Status ID’)\n–for that status, get the parent (category) name\n).Parent.Name”,“format_string”:“StringFormatter”}]
}

[Ofer_Cohen]

@zane.baranovska If I add the “Issues Created” measure AND I set a filter of “Issues Created >= 1” then I get the correct results. I have lots of reports like that and I do not want to go to every report and do that…

[Vegar_Hatlevik]

I get “#null” string on rows that represent project hierarchy. Say I have a report that shows issues by project and project category, and I want to show the status category of each issue. I use the formula

--in Status dimension Category hierarchy, look for Status matching status ID assigned to issue
[Status.Category].[Status].getMemberByKey(
  [Issue].CurrentHierarchyMember.get('Status ID')
--for that status, get the parent (category) name
).Parent.Name

It works for the issue rows, but for the summary rows for project and project category, it shows ‘#null’.

I presume this is because the .Parent.Name dereferences a null object. How do I add a check for null or filter out those object references?

The impact of the ‘#null’ is that it breaks the filtering on Project, showing all Projects instead of the desired ones.

[zane.baranovska]

@Vegar_Hatlevik, you are right; the projects do not have a status as issues, and therefore, the calculation returns null for them.

Add a logical condition to show the category only if the member in the report has the status property.

CASE WHEN --Issue dimension member on report rows has status
  NOT IsEmpty([Issue].CurrentHierarchyMember.get('Status ID'))
THEN
  --in Status dimension Category hierarchy, look for Status matching status ID assigned to issue
  [Status.Category].[Status].getMemberByKey(
    [Issue].CurrentHierarchyMember.get('Status ID')
  --for that status, get the parent (category) name
  ).Parent.Name
END

Here are more details on CASE statement.

Best,
Zane / support@eazyBI.com