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.
Using {AR01} functionality in a slice rather than Active Form
-
- MVP
- Posts: 2832
- 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
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.
-
- Posts: 13
- 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
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.
-
- MVP
- Posts: 2832
- 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
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.
-
- Posts: 13
- 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
Hi Tomtomok 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.
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.
-
- MVP
- Posts: 1815
- 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
Have you considered using an MDX subset?
Declan Rodger
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Using {AR01} functionality in a slice rather than Active Form
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....
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....
Technical Director
www.infocat.co.uk
www.infocat.co.uk