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.
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.
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.
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.
Another subset editor window will pop-up in front and will automatically have the Default subset selected in the 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.
To add the other subsets, required for our reporting, follow the same steps 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.
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.
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.
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.
The above reports can be published in the Application folders and consumed in TM1 Web or Perspectives.
