Neglected Functionality – Custom Rollups & Subset Consolidations

In this blog segment, we’ll look at value add features of the IBM Business Analytics software which are often overlooked and underused. In this article I’m going to explain Custom Rollups and Subset Consolidations within IBM Cognos TM1.

Custom rollups are used to create a temporary user-defined consolidation element. It is a very useful, but somewhat underused functionality. With custom rollups, you won’t be able to save the subset but it is very useful if you ever need to create a consolidated element while browsing a cube, creating a report or reconciling child elements that do not share the same parent element.

Subset Consolidations are a more permanent solution for custom element aggregation which doesn’t require an administrator to build. These are elements which represent a subset and consist of the sum of all elements within that subset.

Below, I’ll guide you through how to use each of these pieces of functionality.

Custom rollups

The custom rollup function is only available when you open the subset editor from a cube view. As shown below, if we’d like to quickly view the total stationery sales for the entry-level product (those with code ending “1001”), we can accomplish this with custom rollups.

First, filter the elements with the code “1001” in it.

Then, click on the “Roll Up” button

And here is the result

Of course, this should be done only for ad-hoc, one time off report. Otherwise, it will be more efficient to save these elements under a consolidated element.

Subset Consolidations

Let’s say aside from reporting the the entry level products, we need to also show the sales figure of each product group from the Product Group subset.

This can be easily accomplished with the “Insert Subset” item. First, click on Edit and select Insert Subset.

Then select the subset you’ll like to show underneath the Rollup elements and click OK.

Result shown below.

 

 

If you like this post, please spread the love…

3 comments on “Neglected Functionality – Custom Rollups & Subset Consolidations

  1. Bob Hamilton says:

    Thanks for the tips. I need to upgrade TM1 and was wondering if version 10.2 was going to be released soon.
    Thanks

  2. Ryan Clapp says:

    Just an FYI you don’t even need to do a “Subset Consolidation” in most cases. Here is a little known trick for excel reporting. Replace an element name in a DBRW formula with the name of a subset in the same dimension (can not be identical to an element name or alias), TM1 will automatically return to you the sum of everything in the subset. This includes consolidations so you can end up with double counting if you are not careful

    1. Thanks, Ryan. That’s definitely a useful one!

This site uses Akismet to reduce spam. Learn how your comment data is processed.

/* THIS WAS THE CODE USED FOR EMBEDDING A SURVEYMONKEY POPUP */