Using Subsets in Excel Based TM1 Reports and Cube Views

Posted by: Tony Luongo

Dec 8, 2016 9:15:00 AM

This article describes how TM1 users can use Subsets in Excel based TM1 Reports (specifically referenced by DBRW and DBR formulae) and within cube views. 

Subsets in Cube Views

This functionality is very useful for the end user and can supersede the need to create numerous alternate hierarchies within dimensions to meet reporting requirements. 

In the following example we have a Sales Forecast cube with a Product dimension with one hierarchy based upon Product Type.


subset editor sales forecast cube with a product dimension


We are now required to provide reporting by the product introduction year. From our source system we already capture the Introduction Year by product in our Product dimension attributes.


cube viewer - product introduction year chart


How can we group this data usefully in a cube view without creating and maintaining an alternate hierarchy? We can use subsets.

In this example, we will make these subsets dynamic using MDX, this way we pick up any additions or changes to our metadata and have minimal maintenance. I can use the MDX recorder in the Subset Editor to create the expression below and name the subset “2016 Products”.

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Product].[Introduction Year] = "2016")}

I can simply then edit the Year value in the expression and save subsets for each year required. These subsets will need to be public subsets if the associated cube view is going to be public.

To utilize the subsets in a cube view, we can begin with a simple view with our Product dimension on the rows.


cube viewer - sales forcast - product dimension


To add our new subsets, click on the Product dimension to bring up the subset editor. Then collapse “All Products” and cut it so I have no elements in the left pane. Then select Edit from the menu bar and then select Insert Subset.


edit and insert subset screenshot


Another subset editor window will pop-up in front and will automatically have the Default subset selected in the subset selector drop-down.


subset selector drop-down


Simply select one of the subsets I created, in this example 2014 Products and then click OK in the subset editor. Now we can see the 2014 Products subset in the subset editor, as if it were an element.


products in the subset editor


To add the other subsets, required for our reporting, follow the same steps selecting the appropriate subsets.


selecting the appropriate subsets

I can reorder these just like elements and I can comingle them with actual elements and I can save them as a subset, as well. Now I click OK on the subset editor and we have the following view.


new view created after clicking ok on the subset editor


Notice that we can expand these “elements” just like any consolidated elements and we can save the view for public consumption. We can also save a public subset that includes our dynamic subsets so users can use it in other cube views as desired.


public subset with dynamic subsets


Subsets in Excel based TM1 Reports

Expanding on the prior section we can use subsets in DBRW and DBR formulas just like any other elements. The following slice was created using the same subsets, now on the column headers.

subsets in DBRW and DBR formulas


Notice the DBRW formula in the formula bar, cell C9 is referenced directly.

The following is a simple active form, where zeroes are suppressed and 2015 Products has been expanded.


simple active form with suppressions and expansions

The above reports can be published in the Application folders and consumed in TM1 Web or Perspectives.



Blog Search

Subscribe to Email Updates

Popular Posts

Recent Posts

Follow Me