Filter query to filter only issues with sprint value(sprint is not empty) and status=“completed”
I have written one, but it is not correct.
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issue Sprint] <>"" and
[Measures].[Issue status] =“Completed”
)
Please help by nudging in right direction.
Hi Ajain,
Could you give some more details about the context of the report?
Lauma / support@eazybi.com
Is there a easy like writing a JQL(Jira Query Language) to quickly filter only those issues which I know I need.
My requirement is to get only those issues which are part of any sprint and in complete state.
There should simpler way for writing filters in eazybi.
Hi Ajain, Sorry for the late reply!
eazyBI is re-organizing JIRA data (for reporting needs), so it will not be possible to use JQL. In eazyBI, there are many ways of data filtering (using Page dimensions, filtering by name or value when you click on table headers or using MDX formulas), and I agree that it can take some effort to find the one that fits best for each report.
In the case you described, I imagine the best would be to add Status dimension to pages and select ‘Completed’. In such way, you will only show the issues in the Completed state.
Further, if you have issues on rows, you can add ‘Issue Sprint’ property and filter issues that do not match ‘(no sprint)’. See the screenshot
Note that the ‘Issue Sprint’ column can be removed after adding a filter, the filter remains.
Lauma / support@eazybi.com
What if you have a value that don’t show any values? I tried with NULL, Null, EMPTY, leave it blank. Any suggestions?
Hi @Daniel_Luevano!
To get all empty values, you can use the following regular expression: not matches .
This means that cell value does not match any symbol or is blank.
Lauma / support@eazybi.com
Hi Lauma,
How can I filter out all issues created before a specific date? It is possible to do this by limiting the data that are imported, but I would need it only in one report.
Can I only report on all issues with a specific status? Without adding it as a member. I don’t want to show it on the report.
best regards,
Henk
Hi @Henk!
If you are using Issues on rows, you can use the Issue properties on columns and by clicking on column name add the filters; see more about dynamic date filtering in the documentation (https://docs.eazybi.com/display/EAZYBI/Date+filters):
Lauma / support@eazybi.com
Hi Lauma,
OK, filtering on create date is clear now. Thank you.
But I’m having trouble to filter on the status. For our integration projects, I want to calculate the average time for a complete integration for those integrations that went into production that month. I don’t want to count the days we had to wait for client tests. So, I want a report with the sum of the average days in certain statuses for issues that reach a certain status that month.
I created a report with the “transition status” measure in columns for which I select only the appropriate statuses that I want to count for the total integration time. When I select the “average days in status” measure, I get the all the averages for all the statuses. I then select the “total for columns” option, which gives the sum of all the averages. Great.
Now I need to filter the issues that have reached certain statuses that month.
I don’t think I can use the status measure because I need the historical status, I assume.
Should I create an new calculated measure and use a case expression?
I would appreciate an example.
Thank you,
Henk
I ment “conditional statement”.
Hi @Henk!
I would suggest you filter all issues that have transitioned to the statuses in the period of time and then add the days in status for the status group you are interested in. A similar example is here Lead + cycle time to replace JIRA Control Chart.
Let me know if that helped or if you have further questions regarding this!
Lauma / support@eazybi.com
Hi Lauma,
I’m sorry for the late reply. No I did not solved it until now.
I’ve studied some MDX (-: and I’ve made some progress. I was afraid, that my use case wasn’t clear, so to be clear I’ve opened a new topic.
Regards,
Henk
hello as a complete newbie - i was happy to find this topic however i have tried unsuccessfully to apply a filter for NOT matched to Issue properties on columns issue labels. i am using issues on rows and applying the not matches issue labels by clicking on column name add the filter. i have tried upper case low case mixed case nothing is working to omit the issues with that specific label (bypasstest ). help please
if there is another way please provide details on how i can enter a filter to omit issues with a specific label from my report.
i tried Define new calculated member new for labels but could not get the syntax on the filter correct.
Hi @kovanp
It is difficult for me to comment on what did not work for you. Would you mind sending in more details with an exact example—a screenshot of the report highlighting which labels you are trying to filter out? Also, if you could send the report definition, that would be helpful (Export and import report definitions).
We can continue in the thread here, or you can reference this question with more details in an e-mail to support@eazybi.com.
Lauma / support@eazybi.com
Hi @lauma.cirule,
This is a lot, but in-measure filters will go a long way here:
Summary: I am building a report to measure O&M work across an enterprise, with various criteria and data hygiene quality for what constitutes O&M based on the project. For now, I am just working on lead and cycle time. The projects in the report need to be displayed individually
Case 1:
We only want tasks with a specific and distinct label within a project
Case 2:
Project has an operations component, but also has a few labels that aren’t in the component that are O&M work. One such label is used across many projects we don’t care about for this report.
So we are combining issues from a specific component, with a list of labels, 1 of which is used by another project in the report but isn’t relevant to the report for that project.
Case 3: There are two linked projects. One effectively acts as an intake project, and after an approval process, the issue from the intake project is closed and a new issue(which could be other issue types) is opened to fulfill the request and labelled.
This means average cycle time includes the created issues from the first project and the labelled issues from the second project, and lead includes the wait time between tickets closed in project 1 and created in project 2.
Since they don’t link issues, I understand I can only calculate overall averages in lead and cycle until work item hygiene improves.
I want to leave for the day at 1pm because of this one.
In individual reports, all of these cases are easy enough, but showing them all in a single report has proven to be vexing. I am attempting to create measures that aggregate the correct set of issues for each project. Is this even the right way?
Again, this is a lot. Any part of this you can help with would be appreciated.
Warm regards,
Marc
Hi @nubz!
Thanks for the detailed example!
I see some hidden logic here, though. I would consider creating a custom dimension for the additional classification criteria to make filtering easier. Meaning - you can do the ‘data cleanup’ on the the eazyBI side by creating new custom fields.
To do this, you would create a new dimension that returns, e.g., O&M work for issues that match the criteria (and maybe some other categories for others?). See more about the eazyBI calculated fields here: Account specific calculated fields.
Case 3 seems a bit harder if there are effectively two issues with no linking… How do you map them, then?
You can also check out this video from my colleague: Videos: data from Jira and apps.
Once you have such a dimension, filtering will be as easy as using this dimension as a page filter.
Lauma / support@eazybi.com
