ELCOMP Work sheet Function

[Abinaya]

Hi,
I would like to get all the n level elements of a consolidated element which has multiple levels. Now ELCOMP works when we have only one level.
What to do If we have multiple levels? I like to use a worksheet function.

regards,

Abi

[rmackenzie]

Create a subset called something like 'N level elements of Consol' and apply an MDX expression to it:

Code: Select all

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[dim].[consol]}, ALL, RECURSIVE )}, 0)}

Then in the worksheet, you can refer to the elements in the subset using the SUBNM formula.

[Headon01]

Abinaya,

If you want to list all those "n" level elements in your worksheet, I can think of two methods:

Active Form
The active form uses TM1 worksheet functions so, technically, this fits. If you need to retrieve values from a cube with those "n" level elements than you couldn't ask for anything better. I would use an MDX expression (see rmackinzie's reply) as the 7th parameter of the TM1RptRow function and leave the 3rd (subset) and 4th (subset elements) parameters blank. You could even prototype it in a cube view, export it as an Active Form and do the parameter substitution on the exported Active Form.

If you are not interested in bringing down a value with the elements you could still use an Active Form but you are going to have to bring something else down for the ride. In this case, I would use the }elementsattributes_{dimension} cubes and bring down an attribute into a hidden column.

The nice thing about this is that you can use Excel's Concatenate function on the MDX expression and make this work with a SubNM function so that you can pick any element within a dimension and list their n level children or the element itself if it is n level.

Also works fine as a TM1 Websheet.

VBA
This is cheating but again, technically, it fits because you would be using TM1 worksheet functions. In your VBA, you could call ELCOMP and ELCOMPN with an Application.Run method and recursive functions. This works well for just listing the n level elements into your worksheet. This method could also be used with a SubNM function.

Only works in a Perspectives enabled workbook.

[Abinaya]

Thanks for the reply.