Exclude certain status AND labels from query

[AnthonyB]

Hi, I’m looking to create a table with multiple results from different Jira projects. I’ve imported everything fine and that side of things work, but what I’m needing to do is have it all on the one report in eazyBI. So for instance, I have imported projects IMT, PMT, SQM. These all have multiple issue types within them and I need to report on those too. In Jira, I can run the JQL:

project = IMT AND issuetype = Incident AND status in (Open, “Add to triage queue”, Triaging, “More Information Needed”, “Awaiting Final Closure”, “Awaiting Change Control”, “Awaiting Vendor/3rd Party support”, “Infrastructure Ops”, Software, DBA, IT, Operations, Networks, Sales, Customer, Billing, Services, MIS, “Pending Triage”, “24/7”, Mobile, “Pending RBM ASG”, “RBM ASG”) AND priority in (P3, P4, P5) AND created >= 2018-04-01 AND created <= 2020-11-09 AND labels not in (billingCI) OR project = IMT AND issuetype = Incident AND status in (Open, “Add to triage queue”, Triaging, “More Information Needed”, “Awaiting Final Closure”, “Awaiting Change Control”, “Awaiting Vendor/3rd Party support”, “Infrastructure Ops”, Software, DBA, IT, Operations, Networks, Sales, Customer, Billing, Services, MIS, “Pending Triage”, “24/7”, Mobile, “Pending RBM ASG”, “RBM ASG”) AND priority in (P3, P4, P5) AND created >= 2018-04-01 AND created <= 2020-11-09 AND labels is EMPTY

This will bring back all incident issuetype in the correct status and with the correct label on. In eazyBI, I’m struggling to be able to tell it to bring back the right issuetype, in the right status and with only the label I want on it. I’m looking to exclude some as you can see from the JQL above.

I also need to show issuetype Incident, WITH the label ‘billingCI’ on the same table in eazyBI without having to create sepereate reports for all 10 things I need to report on. I know this could be done all seperately and then shown as a dashboard, but I’m after that consolidated view.

Any help that anyone could give me would be greatly appreciated.

Thanks,

[daina.tupule]

The JQL query pulls in issues for one project and Issue type created in a particular period in one of specified statuses and priorities with an empty Label or label that is no billingCI.

One of the options is using all those dimensions on the Pages of the report for filtering values. Multiple-values matches will work similarly like in. For example, for priority in (P3, P4, P5), you can use a dimension Priority on Pages and use selection with multiple values - P3, P4, and P5. You can do the same for any other - Project, Issue type, and Status.

There are more complex filters for Time (created >= 2018-04-01 AND created <= 2020-11-09). You can use Time dimension in the report and explicitly select - Q2, Q3, Q4 of the year 2018, and year 2020. I assume created <= 2020-11-09, in this case, might indicate created until today (the day when you posted the topic). Therefore, any created issue until now for year 2020 will work.

Label is more tricky. You can the OR option here where you would like to exclude issues with one particular label and include issues with any other label or empty label.
I would suggest creating a new user defined calculated measure (or report specific measure) with this formula:

([Measures].[Issues created],
[Label].[All Labels])
- 
([Measures].[Issues created],
[Label].[billingCI])

All Labels will include any label including empty label (none). Therefore, you can subtract issues with the label billingCI to get issue with any other label including empty label.

The formula above uses measures Issues created it will count any issue with the issue created date in a selected period as well.

Here is a report example with this approach:

The suggested approach using selections on Pages will work well for overview reports. For example. In my case, I used a report to see an overview by Fix versions.

If you would like to see a list of issues matching those selections, the Pages selections with multiple choices (Priority, Status) might impact the performance for a report with issues. You can use a bit different approach for the Issue report.
Create a report on Issues on Rows and use issue properties to show - Status, Priority, Labels, etc.
You can use a report list of issues report from our demo account as a basis.

For filtering, use only issue type and project on Pages. Add the row filters for Issue created date, Issue labels, Issue Status.
Here are some options you can use to filters rows. Click on a header of the column and select filter rows and then select a filtering option, for example:
matches to match values, for example, Issue priorities matches P3|P4|P5
not matches to exclude some values, for example, Issue labels not matches billingCI
between to include some time period, for example, issue created date between 01 APR 2019 and today

See my report example on a list of issues with rows filters combined with some page filters:

Daina / support@eazybi.com