User defined consolidations in a slice

Post Reply
NPTM1
Posts: 13
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

User defined consolidations in a slice

Post by NPTM1 »

Hi

I am looking to find a way to create a temporary consolidation based on cells in Excel. I've seen that Active Forms have the functionality (where the TM1RPTROW formula references a hidden worksheet called {AR01} and the user can add elements to the list). However I've tried to do something similar in a slice but can't get it to work.

I need something that works similar to a rollup, but where I can define which elements go into that rollup from Excel.

Thanks in advance!
User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: User defined consolidations in a slice

Post by rafaewolie »

Hi NPTM1,

If I understood, You want to make consolidations in a slice perspectives?

The rows are static at slice option, so You include or exclude every row that You want. Them You have to make a formula to that Consolidation that Doesn't exist as a dimension element to have a value in that data point!

I made an example attached below.

PS.: This only works in rows, I don't know If there is a rollup available to Subnm for example!

Best Regards
Attachments
Rollup.png
Rollup.png (3.39 KiB) Viewed 3498 times
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: User defined consolidations in a slice

Post by tomok »

What you're asking for can only be done using the Server Explorer/Architect interface. When you are in Excel only there is no functionality to modify a user-defined consolidation. There is an undocumented feature in TM1 where you can reference a subset name, in place of a member name, in a DBRW formula, and it TM1 will return the sum of the members in the subset. However, you'd still have to use Perspectives to modify the subset, which basically means not doing it in Excel.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: User defined consolidations in a slice

Post by Wim Gielis »

Hello

Why don't you use a simple TI process to update the hierarchy? It can be executed with an Action button in Excel and TM1Web.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
NPTM1
Posts: 13
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Re: User defined consolidations in a slice

Post by NPTM1 »

Thanks all.

As I suspected this can't be done without a TI process, which i'll take a look at.

Thanks all!
Post Reply