Limit Report By AffectedVersion OR FixVersion

Hello

I am having a problem restricting a report using OR Clauses.

I want to create a report in EazyBI which replicates the following JQL:

project = Project1 AND statusCategory != Done AND (fixVersion = "Version1" OR affectedVersion = "Version1" AND (Severity in (High, Critical) OR priority in (High, Highest) OR "Customer Priority" in (Critical, High)))

I have added FixVersion and AffectedVersion in Pages and but by limiting the table using the dropdown will results in
fixVersion = “Version1” AND affectedVersion = “Version1”.

How do I restrict to either of these Fields?

Once I am able to limit the report to
fixVersion = "Version1" OR affectedVersion = "Version1"
I want to further restrict it for the criteria above, i.e I only want High/Critical Issues, by severity OR priority OR customer priority, where affected version = “Version 1”

Any ideas,

thanks

Hi @clarechawke!

You are correct - eazyBI Page filters work as AND. The OR conditions usually hide some business logic, and I would like to suggest creating a new calculated dimension to represent this additional filtering need. Here is a good presentation by my colleague about custom JavaScript fields: MDX vs JavaScript: Behind Complex Filters.
This approach with a new custom JavaScript calculated field might be especially useful for the second part of the JQL for the various priority and severity filters.

For the Fix Versions, there is another workaround involving some calculated field with Tuples - you would calculate the measure for each of the Fix or Affects version that is selected in pages (you can also add the filter for the column to be greater than zero):

Here is the Fix or Affects measure formula:

(
  [Measures].[Issues created],
  [Fix Version].CurrentHierarchyMember,
  [Affects Version].CurrentHierarchy.DefaultMember
) +
(
  [Measures].[Issues created],
  [Affects Version].CurrentHierarchyMember,
  [Fix Version].CurrentHierarchy.DefaultMember
) -
(
  [Measures].[Issues created],
  [Fix Version].CurrentHierarchyMember,
  [Affects Version].CurrentHierarchyMember
)

Lauma / support@eazybi.com

Thanks for your solution @lauma.cirule. This worked great for me. I have used a combination of MDX and Javascript to provide 2 solutions to the problem.

I have another, related question if that’s ok.
We have some large complex deliverables where the issues come from multiple teams and multiple projects. In Jira there are complex filters, which pull the results from the underlying team filters.

For example the team filters are like the following:
filter 1: project = Project1 AND component in (“Comp1”, “Comp2”, Comp10, “Comp22”)
filter 2: project = Project2 AND resolution = Unresolved AND component = “Certification1”
filter 3: project = Project3 AND resolution = Unresolved AND summary ~ “\[Deliverable2021\]”
filter 4: project = Project4 AND ( assignee = “peep1@temp.com” OR assignee = “peep2@temp.com” OR assignee = “peep3@temp.com” OR assignee = “peep4@temp.com” )

and the overall filter select all issues from all these filters using OR Clause:
filter in (“filter 1”, “filter 2”, “filter 3”, “filter 4”) ORDER BY fixVersion ASC

How do I replicate this setup in EazyBI. Does creating calculated measures for each team filter seem excessive?

Hi @clarechawke!

I’m sorry for not noticing your follow up question earlier!

In this case, I imagine you would create a “Deliverable” custom calculated field. Then you could select multiple “Deliverables” from the dimension drop down - the multiple selections from a single dimension works as OR condition.

Let me know if that helped or if you have further questions regarding this!
Lauma / support@eazybi.com