Summing values of numeric field(s) on sub-tasks with a filter

Questions & Answers
, ,
1

Hi,

We have a parent issue type with two types of sub-tasks. In each of the different sub-task issue types we have a separate set of numeric fields that are used to capture estimated time/costs for specific ‘Partners’ involved in projects. One sub-task of each type will end up being created for each partner involved in the project, but there can be multiple rounds of sub-tasks created and we only want to report on the sub-tasks with a value of ‘Yes’ in our ‘Include in Totals’ field. Additionally, we would like to segment our report by partner.

I think this is possible with a Calculated Member for each partner that sums up each numeric field from the sub-tasks. I believe this would require a filter for the Issue Type, a filter for the ‘Partner’ field value and a filter for only ‘Yes’ values in the ‘Include in Totals’ field. Then each of these calculated members would be added to the grid.

I believe if this can be solved for one of the fields the mdx can be slightly modified and used again for the other fields.

Example (see fields in yellow):

image
image.png962×54 3.14 KB

I’m not sure where to start with the MDX for this. Any help is greatly appreciated.

Thanks!
Brady

2

Hi Brady,

You can use Tuples to override the selected dimension levels (note that for dimensions that are not used in the report the ‘selected’ is the DefaultMember or All level).

If the Min Cost is a Measure, then example for Partner 1 Min Cost formula would be as follows

(
  [Partner].[Partner 1],
  [Include in Totals].[Yes],
  [Measures].[Min Cost]
)

Let me know if you have further questions! In such case, please also tell me more about what you would like to use on rows.
Lauma / support@eazybi.com

3

Hi Lauma,

We would like the rows to be the Issue dimension limited to only the parent issues. Most of the measures in the grid are from the parent issue, but the ones in question on this ticket would be a sum of the filtered ‘Min cost’ (and other numeric fields) on subtasks for that parent ticket filtered by specific values from the ‘Partner’ and ‘Include in Totals’ field. ‘Partner’, ‘Inlclude in Totals’ and the numeric fields such as ‘Min Cost’ are fields that only exist on the subtasks.

Thanks!
Brady

4

Hi Brady,

I see. If you would use the Issue dimension Parent level in the Sub-task hierarchy, you can add a Sum(…) function that would sum the value for its children (children of Parent issues are the sub-tasks in the Parent/sub-task hierarchy). The formula would be similar to following

Sum(
  ChildrenSet([Issue].CurrentHierarchyMember),
  DefaultContext((
    [Partner].[Partner 1],
    [Include in Totals].[Yes],
    [Measures].[Min Cost],
    [Issue].CurrentHierarchyMember
  ))
)

Let me know if this works as expected for you!
Lauma / support@eazybi.com

5

Hi Lauma,

Unfortunately this does not seem to be working as expected. The sum is including all values for the measure regardless of partner or include in totals values.

I have a few other questions:

One of the fields we are working with is actually a text field and will not work with the Sum() function. Is there a way to cast the text value to a number within the MDX or do we just need to change up the field?

What would be the syntax if I wanted to sum up multiple partners into a group (e.g. Partner 1 and Partner 3)?

Thanks!
Brady

6

Hi Brady,

Could you please provide screenshots of how you have implemented the report highlighting where the problem seems to be, as well as export of report definition. Please send this information to support@eazybi.com for further debugging.

Regarding your other question, it is possible to use Val(…) function to return number contained in a string.

Kind regards,
Lauma / support@eazybi.com

7

Hi Lauma,

I sent an email to support@eazybi.com with the requested information. Let me know if you need anything else from me at this time.

Thanks!
Brady

1 Like