ChildrenSet not taking pages into account

My report is set up as follows:

  • Issues on rows (nonempty = true)
  • Issue Type, Status on pages
  • Measures on columns

In the Issue dimension I have 3 different aggregate calculated members selected. There is no overlap between them, and all children are at the [Issue].[Issue] level.

This is the calculated measure I’m having trouble with:

Sum(
  ChildrenSet([Issue].CurrentHierarchyMember),
  [Measures].[Issue Impact Weight]
)

I want to see the sum of the Impact Weight property across all of the aggregate member’s children, and if I drill down into the aggregate member’s children I want to see the individual values for each child. Basically I want this to work how it would have, if Impact Weight were a Measure and not a property.

This calculated measure appears to work, but if I change the page selections to filter out some of the children, the totals do not change. If I drill down into the child issues I can see that children are being excluded, but the aggregated sum still reflects the unfiltered set of children.

If I use SetToStr to see what members are coming back from the ChildrenSet call, it appears that the page selections are not influencing the result of ChildrenSet at all, which explains why the sums weren’t changing even though the child rows were. I guess this might be intended behavior for this function, but it’s a little surprising to me. I would have thought ChildrenSet would be affected by the overall context but I guess not.

Is there any other way for me to achieve what I want? I managed to hack something together with a bunch of NonEmptyCrossjoins joining every dimension to the ChildrenSet, but that feels like the wrong way to do it.

Another issue I’m running into with this report, which may or may not be related - if I enable “Total of rows” the Totals row acts strangely. It works if everything is collapsed, but if I expand one of the aggregate members, the total becomes incorrect:
image

You are using the issue property [Measures].[Issue Impact Weight] for your calculation. The property value will show up in the report any time you use an issue in the report or formula, no matter if the issue should be pulled in by other dimension page selections or not.

eazyBI, by default, allows importing any numeric custom field as a measure. Please check if your custom field Impact Weight is represented in section Measures>Predefined>Custom fields. There could be several measures, for example, Impact Weight created, Impact Weight resolved, etc. You would like to use one of them for this report. They will sum up values by default and will take into account page selections.

Here is my report example. I disabled the Nonempty option to show all issues ignoring Pages selections. I used the numeric custom field Story Points as an example.
Calculation SP uses the same formula as you shared with Issue Story Points property. Property Issue Story Points shows values for unrelated projects and unselected priorities. Therefore, this property gives an incorrect sum for SP. The default measure Story Points created will automatically sum up values and filter by pages.

The total calculation might work differently for different formulas. The total for Calculated measure, in my case, SP and Issue Story Points will sum values of visible rows and will remove values for grouping members to avoid double counting. Therefore, you see values from expanded issues only. You would like to expand all or collapse to see correct total values for any metric based on issue properties.

The total for default measure Story Points created is calculated differently and will show you the correct values always:

Daina /support@eazybi.com

Thanks for the tip, Daina. Switching to [Measures].[Impact Weight created] indeed gave me the rollup behavior I was looking for. Once things started to get so complicated, I knew I must have been missing something simple. :slight_smile:

I still ended up needing to use ChildrenSet for a couple of my other calculated measures (not pictured in my examples) - I wanted to aggregate some other measures/properties in several different ways on this chart, and the way MDX aggregates measures just wasn’t working for me on some of them. (e.g. Min([Measures].[A], [Measures].[B]) would aggregate to a min-of-sums instead of a sum-of-mins like I wanted) So I did end up finding a relatively straightforward way to get ChildrenSet to generate a “context-limited” set of children. (i.e. the children you might expect to see if this was a “real” hierarchy instead of a bunch of Aggregate(...)-based calculated members)

This is what I used, in case anyone else finds themselves in a similar situation:

Filter(
  ChildrenSet([Issue].CurrentHierarchyMember),
  [Measures].[Issues created] > 0
)

This just uses the existing Issues created measure to do all the heavy lifting of excluding anything that doesn’t belong in the current page/column/row. Of course, if anyone has an even better/simpler solution, I’m all ears.

As far as the totals go - I understand excluding the expanded grouping members to avoid double-counting, but I would still expect un-expanded grouping members to be included in the total. (They are included when no grouping members are expanded, after all…) The rules seem to just suddenly change once a grouping member is expanded, and that doesn’t really make any sense to me.