How to Build a Progress Report Using MDX in EazyBI

[Behzad_Nazarbakhsh]

Hi EazyBI Community,

I’m trying to build a table (heatmap shape) in EazyBI using an MDX query, but I’m struggling with structuring the hierarchy and calculating progress based on issue statuses.

Use Case Overview:

We have a clear hierarchy of issues:

• Project
  • → Multiple OCM Workpackages
    • → Each OCM Workpackage has multiple OCM Epics
      • → Each OCM Epic has multiple OCM Tasks

Each level is an issue type, and there’s a parent-child relationship between them.

There’s also a custom field called “Selected in Activity” (a checkbox), which is set to Yes or No.

Goal:

For each Project , I want to show:

• Percentage of those OCM Epics where Status = Done
  The final table should look like this (example):
  
  

Any help would be greatly appreciated!
Thanks in advance!
Regards,
Behzad

[Elita.Kalane]

Hello @Behzad_Nazarbakhsh

Thanks for reaching out to us

Based on your requirement, I’d like to clarify a few points:

1. “Selected in Activity” field usage: Should the percentage calculation only consider OCM Epics where “Selected in Activity” = “Yes”, or should this field be used as a filter/display option?
1.a Is this field available for all issue types or is it used only in a specific issue type but should be applicable for child levels as well?
2. Progress calculation method: Are you looking for:

• Option A: percentage of OCM Epics with Status = “Done” (e.g., 3 out of 5 epics are done = 60%)
• Option B: Progress percentage based on OCM Tasks completion within each epic (e.g., Epic has 8 tasks, 6 are done = 75%)

3. Column display: Should all OCM Epics appear as columns, or do you want to filter/group them in some way?
4. Hierarchy setup: Do you have this hierarchy configured in eazyBI advanced settings, or are you using standard Jira parent-child links?

My first recommendation is to check if the formulas below are what you are looking for the Percentage of those OCM Epics where Status = Done.
Replace the reference to the Issue type in the measure below, if you are looking for the calculation for a different Issue type.
Add the Project dimension in Rows and define a new calculated measure with the formula below in the Measures dimension.

(
  [Measures].[Issues created],
  [Issue Type].[OCM Epics],
  [Status].[Done]
)
/
(
  [Measures].[Issues created],
  [Issue Type].[OCM Epics],
  [Status].CurrentHierarchy.Defaultmember
)

Best wishes,
Elita from support@eazybi.com

[Behzad_Nazarbakhsh]

Dear @Elita.Kalane,
Thanks for your help. Here are answers to your questions:
1 . “Selected in Activity” field usage: Should the percentage calculation only consider OCM Epics where “Selected in Activity” = “Yes”, or should this field be used as a filter/display option?
Answer 1: The percentage should be calculated based on OCM EPICs where ‘Selected in Activity’ is marked as ‘Yes’, as this reflects the proportion of OCM EPICs that are included in the PM’s plan. For example, if there is 1 OCM EPIC with 10 predefined activities in the plan and 6 of those are selected for execution, the percentage indicates how many of the planned activities have been addressed by the end of the planning phase.
1.a Is this field available for all issue types, or is it used only in a specific issue type, but should be applicable for child levels as well?
Answer 1. a: The OCM Workpage, OCM Epic, and OCM Activity are all predefined for the project and include a ‘Selected’ field, which indicates whether these items are included in the PM’s plan or not.
2. Progress calculation method: Are you looking for:

• Option A: percentage of OCM Epics with Status = “Done” (e.g., 3 out of 5 epics are done = 60%)
• Option B: Progress percentage based on OCM Task completion within each epic (e.g., Epic has 8 tasks, 6 are done = 75%)
  Answer 2: I am opting for Option 2 as it allows me to monitor the progress of activities that were planned by the PM.
  3. Column display: Should all OCM Epics appear as columns, or do you want to filter/group them in some way?
  Answer 3: I have ‘Stakeholder Recognition’ as an OCM EPIC in both Projects A and B. If this EPIC is selected by the PM in Project A, the progress percentage will be displayed, showing 0% if no activities have been completed. In Project B, if it is not selected, we display ‘Not Planned’. So we do not filter OCM Epic.
  4. Hierarchy setup: Do you have this hierarchy configured in eazyBI advanced settings, or are you using standard Jira parent-child links?
  Answer 4: I use Jira parent-child links in Jira using sheets.
  5.** Recommendation:** check if the formulas below are what you are looking for, the Percentage of those OCM Epics.
  Answer 5: Let me check. I’ll let you know.
  Best wishes
  Behzad

[Behzad_Nazarbakhsh]

Hi @Elita.Kalane ,

I’ve reviewed the solution you suggested – adding the Project dimension in Rows and creating a new calculated measure in the Measures dimension using the provided formula. However, the results are not displaying as expected.

To clarify, for Project A and Project B, there are several OCM Epics, including Stakeholder Recognition and Software Training, etc. These should appear as columns, with each showing the percentage of progress for that Epic under the respective project.

The percentage should be calculated as: (Number of completed OCM activities under each Epic) / (Total number of activities under that Epic)

The OCM Epic “Stakeholder Recognition” appears in multiple projects, but it has a unique identifier in each project since it is created separately for each one. Even though the name is the same, these are distinct instances per project.

Here is my report setup:

• ROWS: [Project]
• Page Filter: [Issue Type].[OCM Epic]
• COLUMNS:
  • [Measures].[Selected OCM Tasks]
  • [Measures].[Completed Selected OCM Tasks]
  • [Measures].[OCM Epic Progress %]
  • [Issue].[Stakeholder Recognition]

1. [Measures].[Selected OCM Tasks]

NonZero(Count(Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Issue Type].[Issue Type].CurrentMember.Name = “OCM Task”
AND [Measures].[Issue selected in activity] > 0 )) )

2. [Measures].[Completed Selected OCM Tasks]

NonZero(Count(Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Issue Type].[Issue Type].CurrentMember.Name = “OCM Task”
AND [Measures].[Issue selected in activity] > 0 AND [Status].[Status].CurrentMember.Name = “Done”)))

3. [Measures].[OCM Epic Progress %]

IIF([Measures].[Selected OCM Tasks] > 0, [Measures].[Completed Selected OCM Tasks] / [Measures].[Selected OCM Tasks], NULL )

4. [Issue].[Stakeholder Recognition]

Aggregate( Filter([Issue].[Issue].Members, [Issue].CurrentHierarchyMember.get(‘Summary’) = “Stakeholder Recognition”))

Despite setting up everything, I’m not getting any data in the results — all columns are blank.
Thanks,
Behzad

[Elita.Kalane]

Hello @Behzad_Nazarbakhsh

Thanks for the follow-up. It looks like the report may be overcomplicated.

You mentioned you are using the parent-child relation, but in one of the measures you shared today I am noticing that you are descending from the Standard Issue hierarchy. In fact, if you use the Issue Hierarchy, the iteration through Issue dimension would not be needed.

Based on your response, it is still not clear if the hierarchy is imported into eazyBI, you mentioned what you use in Jira, however, you should make sure that the hierarchy also shows up in eazyBI. Depending on whether you use Cloud or Data Center the set up is slightly different. But you should make sure that the Parent Link is selected for import. Please check out the documentation page here- Plans custom fields

Here is a screenshot from my test instance. Let’s assume your OCM Epic is my Epic level. When you expand the Issues dimension and you have imported your Parent Link, you should see the Parent hierarchy. If you select on any level, you will see the issues from that particular level and with measure “Issues created” you will see how many issues are under these issues - in my case - under each Epic.

If you require ti have Projects in Rows and Epics in Columns, move the Issue dimension to Columns but stay in the required hierarchy

For example purposes, I will only focus on 4 Epics

Since you don’t want to see all Issues created in particular OCM Epics, but rather OCM Tasks with particular statuses, you should then use different measures by using the Tuple functions

For example, this measure will return the count of OCM Tasks within the OCM Epics with Status Done.

(
  [Measures].[Issues created],
  [Issue Type].[OCM Task],
  [Status].[Done]
)

Your goal is to return the percentage of Done OCM Tasks, in that case, use the measure below

(
  [Measures].[Issues created],
  [Issue Type].[OCM Task],
  [Status].[Done]
)
/
(
  [Measures].[Issues created],
  [Issue Type].[OCM Task],
  [Status].CurrentHierarchy.Defaultmember
)

Here’s how my report looks with the calculation above

Another observation I have made from the measures you shared is that you mentioned that “selected in activity” field is a checkbox field. In the formula, you use a > 0. Your checkbox field will return a value - such as “Yes” and will not work with > operator, because your value is not an integer. If you are only interested for issues that have been set to “Yes” in this field “Selected for Development” then drag this dimension to Page Filters and filter your report by value “Yes”

See example below:

If you want to incorporate Selected in Activity" logic directly in the measure (rather than as a page filter), you can adjust the measure accordingly, by adding another layer in the Tuple function, like this

(
  [Measures].[Issues created],
  [Issue Type].[OCM Task],
  [Selected in Activity].[Yes],
  [Status].[Done]
)
/
(
  [Measures].[Issues created],
  [Issue Type].[OCM Task],
  [Selected in Activity].[Yes],
  [Status].CurrentHierarchy.Defaultmember
)

Best wishes,

Elita from support@eazybi.com

[Behzad_Nazarbakhsh]

Dear @Elita.Kalane,

Thank you for your assistance. I’m using Jira Server, and the issue hierarchy is structured as follows:
Here is the edit Issue page:

Here is a Jira Ticket and its link to a child issue Example:

But I’m currently unable to see the ‘Parent’ in the issue hierarchy level members in EazyBI.

What should I do to resolve this?

[Elita.Kalane]

Hello @Behzad_Nazarbakhsh

Indeed, there are differences between Cloud and Data Center. Based on the screenshot you shared, you are on the Data Center and the terminology is slightly different there. Your configured hierarchy is under Plans, and I can only assume that Project is where your OCM Epics are stored. You can verify that in the hierarchy configuration in your Jira settings or alternatively, you can select this level and check which issues show up

Best wishes,

Elita from support@eazybi.com