Subset Elements in EXCEL Sheet. - Newbie in TM1

[bunchukokoy]

Good Day guys!

I want to ask something. I hope you can help me on this. I want to know how will I display all elements of a particular subset to an Excel Sheet.

Thanks!

bunchukokoy

[Alan Kirk]

I want to ask something. I hope you can help me on this. I want to know how will I display all elements of a particular subset to an Excel Sheet.

Depends on what your intention is, but a couple of ways:

Option 1: In Excel go to the TM1 menu -> Pick -> Element... -> This will launch the Subset Editor. Select the subset that you want, then select all of its elements -> OK -> Double click on the cell that you want them to be stored in -> Select whether you want them listed vertically or horizontally.

Option 2: Use SubNM formulas in an Excel spreadsheet. For example you could put the following in a cell in row 1:

Code: Select all

=SUBNM("Server:Dimension","SubsetName",ROW())

and copy it down for as many rows as the subset has members. If you don't know the exact number, just copy it down as far as you can; if the row number exceeds the number of members in the subset the formula will simply return an empty string.

[bunchukokoy]

Thanks Alan!

An additional information for me. Thanks!

[cramejia_09]

For me, my idea is to use the vba macro of Excel to list down all the elements of a dimension in an excel sheet.

1.First, get the no. of elements or size of a particular dimension that you want to use.
Dim x as long
x = Application.Run("DIMSIZ" ...etc
2.Second, use DIMNM to get the elements in a dimension. Use the size of the dimension to loop the elements in a dimension.
3. Then assign the elements that you will get from DIMNM to the cells in your worksheet.
Dim ctr as long

For ctr = 0 to x
Sheet1.Range("A"& ctr+1).value = Application.Run("DIMNM",dimension name,ctr)
Next ctr
4. Put this procedure inside the button as a trigger.
Note: please correct my vba codes if theres an error in terms of DIMNM and DIMSIZ syntax..


Thanks and Regards,
JR

[lotsaram]

For me, my idea is to use the vba macro of Excel to list down all the elements of a dimension in an excel sheet.

1.First, get the no. of elements or size of a particular dimension that you want to use.
Dim x as long
x = Application.Run("DIMSIZ" ...etc
2.Second, use DIMNM to get the elements in a dimension. Use the size of the dimension to loop the elements in a dimension.

If this is all you want to do it would be more efficient to programatically fill down the dimnm formula the number of rows specified by dimsiz then run tm1recalc1 macro. It would probably be faster too.

[bunchukokoy]

Guys,

Thanks for your help. I really appreciate it.

bunchukokoy