TM1 Forum

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.

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.

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.

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.

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.

Have you considered using an MDX subset?

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....