Here’s an interesting problem. A user can select multiple items from a tree prompt, from any level. The report shows all of the selected members, but the total should only include items from the lowest members.

Let’s take a look at what happens if we select the all member and the first level. In this case, the retailers hierarchy.

By simply doing total(currentMeasure within set [Retailers]) we’re doubling the value. Which makes sense, the Retailers member contains the five regions. It’s exactly the same as saying, “I want the total for all of the retailers plus the total for all five regions”.

Instead we need a way of saying, “I want to total only those members which don’t have any descendants in the set”.
First let’s build the set expression to get those members.

filter(
    [Retailers]
  , count(member(1) within set 
    intersect(
      descendants(
          currentMember(hierarchy([Retailers]))
        , 0
        , after
      )
    , [Retailers]
  )
  )=0
)

A bit complicated so let’s walk through it.

First we go to the Retailers set. For each member ( currentMember() ), we find all of it’s descendants ( descendants(.., after), we then intersect that on the original set, and count the members in common. Finally we can then say we only want those retailers where the count of descendants is 0. Important note. This is probably not the most performant expression in the world. For each member it’s going down to the leaf level and comparing that against the original set. Good for a hierarchy containing a few thousand members. Not so good when your hierarchy has millions of members.

We can then wrap that expression in total:

total(currentMeasure within set filter([Retailers],count(member(1) within set intersect(
descendants(currentMember(hierarchy([Retailers])),0,after),
[Retailers]
))=0)

And let’s see how it looks.

Now what happens if we want the opposite. We want the top members summed. If we have the all member, only that. The question then becomes, “I want to total only those members which don’t have any descendants in the set”. The solution is very similar to the first problem. The only problem is Cognos doesn’t support the MDX standard “ascendants” function.

This I will leave as an exercise for the reader. If you get stumped, feel free to see the solution in the attached report.
summing-leaf-members.txt (372 downloads)