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: