Using {AR01} functionality in a slice rather than Active Form

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

Using {AR01} functionality in a slice rather than Active Form

Post by NPTM1 » Tue Feb 20, 2018 11:39 am

Hi

Does anyone know if there's a way to roll up elements and reference them in a slice report? I know this can be done on an Active Form using the hidden tab with elements listed. I wanted to do something similar on a slice but couldn't get it to work.

Essentially, I've got a list of elements in a column and want to reference them bundled together, similar to how you use Roll Ups on perspectives.

User avatar
tomok
MVP
Posts: 2412
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: Using {AR01} functionality in a slice rather than Active Form

Post by tomok » Tue Feb 20, 2018 11:43 am

Create a subset of the elements you want and then reference them in a column using the SUBNM function going down the rows, incrementing the index parameter as you go.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

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

Re: Using {AR01} functionality in a slice rather than Active Form

Post by NPTM1 » Tue Feb 20, 2018 1:44 pm

tomok wrote:
Tue Feb 20, 2018 11:43 am
Create a subset of the elements you want and then reference them in a column using the SUBNM function going down the rows, incrementing the index parameter as you go.
Thanks for the reply. The issue I've got is that the subset needs to be quite flexible as the elements will keep changing, so I'm not sure if this approach will work.

User avatar
tomok
MVP
Posts: 2412
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: Using {AR01} functionality in a slice rather than Active Form

Post by tomok » Tue Feb 20, 2018 4:57 pm

What is it that you want? A dynamic list of the elements in the subset along the rows or just one row that has the total of your subset elements? if it is the former then you either use an Active Form or you use my approach. You just have to add enough SUBNM formulas to cover for when the list expands. If it is the latter then there is an undocumented feature in Perspectives where you can pass the name of a subset instead of the name of an element in the dimension parameter in a DBR or DBRW formula.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

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

Re: Using {AR01} functionality in a slice rather than Active Form

Post by NPTM1 » Wed Feb 21, 2018 12:32 pm

tomok wrote:
Tue Feb 20, 2018 4:57 pm
What is it that you want? A dynamic list of the elements in the subset along the rows or just one row that has the total of your subset elements? if it is the former then you either use an Active Form or you use my approach. You just have to add enough SUBNM formulas to cover for when the list expands. If it is the latter then there is an undocumented feature in Perspectives where you can pass the name of a subset instead of the name of an element in the dimension parameter in a DBR or DBRW formula.
Hi Tom

It's the latter of the two. I often pass the subset name in a DBRW formula to pick up the data I need, however in this case I need to be able to quickly change the elements,

For example, if I want the total for the subset FRUIT (which contains the elements BANANAS and APPLES) I was to be able to get the data for BANANAS APPLES and PEARS without having to manually add PEARS to the subset and refresh.

When using Active Form I can simply reference the range of cells that lists BANANAS APPLES PEARS and refresh, but on a slice this doesn't work.

If there was a way to add PEARS to the subset FRUIT via formula (similar to DBSS) rather than through perspectives then that would be great but I don't think you can.

declanr
MVP
Posts: 1561
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Using {AR01} functionality in a slice rather than Active Form

Post by declanr » Wed Feb 21, 2018 12:41 pm

Have you considered using an MDX subset?

User avatar
Steve Rowe
Site Admin
Posts: 1742
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Using {AR01} functionality in a slice rather than Active Form

Post by Steve Rowe » Wed Feb 21, 2018 5:20 pm

I'm not convinced there is any of doing this without having the relevant subset (really consolidation) present.

Depending on the size of the problem could you create all the consolidations you could possibly require in the dimension in a TI process?

I'm still not that clear on your use case to be honest, what drives the change in the selection? How and why does the user add PEAR? If you have a solution in an active form, why are you trying to do it in a slice?

You could do something bizarre with a ruled reference cube that is a mirror of your report that references the underlying cube. If you have a row dimension, you could DBSS the measure into the row measure, your rules reference this and pull in the relevant values from the underlying cube. You then return the "All Rows" total from the reference cube to get your total. Seems more than a bit Heath Robinson / Rube Goldberg though....

Post Reply