User defined consolidations in a slice

Post Reply
NPTM1
Posts: 4
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 » Tue Sep 12, 2017 3:24 pm

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 » Tue Sep 12, 2017 4:25 pm

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 125 times

User avatar
tomok
MVP
Posts: 2318
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 » Tue Sep 12, 2017 4:47 pm

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.

Wim Gielis
MVP
Posts: 1522
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: User defined consolidations in a slice

Post by Wim Gielis » Tue Sep 12, 2017 11:56 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

NPTM1
Posts: 4
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 » Wed Sep 13, 2017 11:12 am

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