Summing only the lowest members in a set

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.
wrongsum

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.
rightsum

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 (27 downloads)

Report Studio in Cognos 11.1 and making drilldown breadcrumbs

Report Studio in Cognos 11.1 and making drilldown breadcrumbs

I’ll admit it. Since I’ve started working at PMsquare, I’ve been a bit remiss on updating this blog. What’s even worse, I’ve started cheating on my own blog and I’ve been writing periodically for the PMsquare one!

My last two posts there are well worth the time to read.

The first is an overview of Report Studio. With Cognos 11.1 released for cloud customers, and an on-prem version being released imminentlyish (November or December maybe?) my article goes into the nitty gritty of the changes in Report Studio. With lots of nice animations of me fooling around with it. There are some really nice features, and one annoying change. Read it here: http://www.pmsquare.com/main/blog/cognos-11-1-reporting/

The second is a technique for making breadcrumbs with advanced drillthrough options. It works beautifully in every version of Cognos I’ve tested in, but only in non-interactive mode. Read it here: http://www.pmsquare.com/main/blog/drilldown-breadcrumbs-in-cognos-analytics