Sometimes we want to create a rollup of elements from a dimension that does not exist in the dimension structure itself. We have a choice. We can either go back to the developers and have them add a new rollup, or in Architect and Perspectives, we can create what is called a User Defined Consolidation. This UDC is completely created by the user and can be used in public reports and in TM1Web forms.
Note that this does NOT work in Planning Analytics for Excel or Planning Analytics Workspace when published from PAX. To achieve the same outcome, please see this post all about the DefineCalc function.
Please also note that it is not a User Defined Calculation, rather just a User Defined Consolidation.
Method to Create a User Defined Consolidation in TM1
Time needed: 5 minutes
To create a User Defined Consolidation in the old TM1, please follow these steps:
- Open the Subset Editor for the dimension you want to create the consolidation in.
In Nebula Fit, we will open the subset editor for the Branch dimension and we are going to consolidate Southern Region, Western Region and New Zealand. As you can see from the screenshot below, these are not normally rolled up in our Branch dimension.
- Select Edit, Insert Subset
You’ll need to have at least one element selected, then do Edit, Insert Subset. This will open a new Subset Editor and there will not be any aliases showing.
- Select the required elements
We are going to choose 200, 300 and 400 in the new subset editor.
- Select Edit, Keep or Right click Keep
Either way, end up with just 200, 300 and 400 in your subset.
- Save this new Subset with the Consolidation Name
Now save this new subset using the name you want to appear in the original subset as the new consolidation. We’re going to call this South West NZ – which yes, is a really silly name if you know the geography of this part of the world! I have also made it Public as I want to be able to use it in shared reports. Note as well that what you call this subset cannot be the same as any existing element name as otherwise it will conflict with the element.
- Click Ok to Close
We can now close this second subset and the original will now include our new User Defined Consolidation – South West NZ!
- Save the Original Subset
We can now save the original subset. I am going to call it something new, maybe “Total and SWNZ” once again as a Public subset. This will then be available throughout Architect, Perspectives and TM1Web for us to use (but not Planning Analytics Workspace when published from Planning Analytics for Excel).
- Use it in a View
Now we can open the Default view of our Gross Profit cube and put the Branch dimension into rows and instead of using the old rollup to Total Company, we can use our new South West NZ.
- Use the User Defined Consolidation in Perspectives
Take your view and Slice it into a Perspectives report and the User Defined Consolidation works perfectly.
- Open it in TM1 Web
Then save the Perspectives report and upload it to the TM1 server. Flip over the TM1Web and the UDC is immediately available there too.
- Test it in Planning Analytics Workspace
Interestingly the report was able to be used in PAW because I had created it originally with Perspectives. If I had opened the report using Planning Analytics for Excel, the UDC would have failed and therefore I would not have been able to use it in PAW via that route.
- Fail in Planning Analytics for Excel
You cannot use a User Defined Consolidation in Planning Analytics for Excel. The report will open but will display #VALUE all over until the UDC is removed.
UDC’s in Planning Analytics for Excel
User Defined Consolidations are not available in Planning Analytics for Excel. Even if the report was created in Perspectives and then opened in PAX, it will not include the UDC. To get around this IBM has created a new function called DefineCalc. We have a detailed post here that explains how to use DefineCalc to emulate the outcome of the UDC. Note it does not convert the UDC, but allows you to achieve the same outcome.
Do You Need Help With UDC’s or Conversion to PAX?
We have long experience with using User Defined Consolidations at some of the largest TM1 sites in the world. If you need some guidance on converting Perspectives worksheets to work with Planning Analytics for Excel, give us a yell. We are more than happy to share our experience.
