Is there a way to have a dimension "has sub-tasks" ish?

Hello,

I need to distinguish stories and tasks with sub-tasks from the others without sub-tasks.

The main objective is to retreive issues (stories & tasks) without estimate neither at task nor at sub-task level.
I know how to do it by parsing all the issues (sum(filter(descendants(issues blabla))), but for performance reasons it doesn’t succeed.

I wonder if it is possible to do it while importing with a custom field so that it is much more efficient.

Do you have some advises ?

Cheers

Hello @RCi ,

There is a standard measure “Original estimated hours with sub-tasks”, couldn’t you use it for your needs?

Something like formatting your cell where this measure is 0:

Later edit: I am not sure if this works over multiple levels in issue hierarchy! In other words, I don’t know if (let’s say) time for sub-tasks is counted in epics if the hierarchy is Epic → Tasks → sub-task. But it worth trying.

Hope it helps!

Kind regards,
Vasile S.

Hello @VasileS

Thanks for the feedback. I knew this measure, I used it in a filter way but still filtering over the issue dimension is definitely not efficient.

You bring me a new idea but unfortunatelly for the time being it’s not successful yet. The main problem is that eazybi doesn’t allow to pass up the information but only to pass down in the hierrachy. It’s a side effect of how jira manages the link within a hierarchy and doesn’t index the children of a task, only the child know its parent…

The only way I see to do it efficiently would be to compute the estimate with subtasks at import time and not by summing in the report. But here, we have a limitation of eazybi that, from my understanding, is indexing the change at issue level and not its sub tasks while importing, so there could be kind of desynchronization (meaning, if you update the subtask, it will not update the task).

@VasileS mentioned the default measure Original estimated hours with sub-task summing up sub-task original estimates. While this measure does not count issues with original estimates in issues and sub-tasks, you can use it to define a new counter custom field. eazyBI allows defining custom fields using SQL expressions based on other imported measures. You would need to know how those measures are stored in the database, though. In this case, you would like to use column subtask_original_estimated_hours representing sub-task original estimates and original_estimated_hours for original estimates of issues themselves.

Here is how you can define a new custom field in eazyBI advanced settings using SQL expressions:

Column subtask_original_estimated_hours have values for both the parent issue and the sub-task. Parent issues will have sub-task original estimate value. However, sub-tasks will have the same value as the negative to avoid double counting. This expression will pull in issues with sub-task original estimates or if the issues themselves has original estimates that are not sub-task original estimates. It will not count sub-task with original estimate.

[jira.customfield_sub_oe_count]
name = "Issues with sub-task original estimate"
data_type = "integer"
measure = true
sql_expression = "CASE WHEN {{subtask_original_estimated_hours}} > 0 OR {{subtask_original_estimated_hours}} IS NULL AND {{original_estimated_hours}} IS NOT NULL THEN {{issue_id}} END"
measures_table_name = "jira_issues_measures"
aggregator = "distinct-count"

Here is a similar definition counting any issues with the original estimate in the issues themselves:

[jira.customfield_oe_count]
name = "Issues with original estimate"
data_type = "integer"
measure = true
sql_expression = "CASE WHEN {{original_estimated_hours}} IS NOT NULL THEN {{issue_id}} END"
measures_table_name = "jira_issues_measures"
aggregator = "distinct-count"

Please add the needed definition to eazyBI advanced settings. Then select the custom field for imports as a measure in the account where you would like to use it. Run an import.

Daina / support@eazybi.com

3 Likes

Thanks a lot ! I will try it.

You make my week ! :slight_smile:

1 Like

Thank you! I learn something new today! :slight_smile:

V.