Dynamic List of Dimension Elements in Spreadsheet

[ExApplix]

Hi Guys,

I am looking for a way by which I can get the list of Dimension elements in the spreadhseet.

I have already looked at the SUBNM function but it requires a index number. My problem is how to generate this index dynamically. My spreadsheet looks like this:

Index ProductDim
1 Apple
2 Orange
3 Strawberry
n nnnnn

The reason behind this problem is that I am on Tm1 9.4 and want to build Picklist kind of feature in the spreadsheets.

Any help in this matter will be higly regarded. Thank you.

[jim wood]

Why don'y you just number a column say 1-10. Then use subnm to pull the element for that index and then use the subnm column column as data range???? I don't understand why this doesn't work??

[ExApplix]

Why don'y you just number a column say 1-10. Then use subnm to pull the element for that index and then use the subnm column column as data range???? I don't understand why this doesn't work??

This is exactly what I am doing. But there is a problem with this approach. Lets say if there are 10 elements in the Product dimension and I create the Index column manually from 1-10 then in the future my Products dimension grow to 100 then in that case my Index column will have to be updated automatically.

So how can I create the index column based on the Dimension Element size?

[jim wood]

Put in 1-100. Subnm brings nothing back if the index doesn't exist. You could always be creative and bring back the number of elements in the dimension and put an if statement in there. You know something like if($B$1<a2,'',subnm.....). There are plenty of options. I would go through the excel function ref section of the help and think about what each function is going to give you. Once you know what is possible it makes life easier.

[lotsaram]

I think Jim has covered this but you could just have surplus index numbers and set the name range for your cell validation of the element names to be dynamic and grow as the non-blank list of members grows. This is quite easy to do with an offset formula.

If you want to use VBA you could also use SUBSIZ or DIMSIZ to get the number of elements then fill down and reset your ranges automatically with VBA.

[jim wood]

I didn't want to suggest running before walking but never mind....

[ExApplix]

I think Jim has covered this but you could just have surplus index numbers and set the name range for your cell validation of the element names to be dynamic and grow as the non-blank list of members grows. This is quite easy to do with an offset formula.

If you want to use VBA you could also use SUBSIZ or DIMSIZ to get the number of elements then fill down and reset your ranges automatically with VBA.

I guess VBA is the way to go. I am just a newbie to VBA. Can someone guide me how to do that or share some sample VBA code which can do this?

Thanks

[Alan Kirk]

I think Jim has covered this but you could just have surplus index numbers and set the name range for your cell validation of the element names to be dynamic and grow as the non-blank list of members grows. This is quite easy to do with an offset formula.

If you want to use VBA you could also use SUBSIZ or DIMSIZ to get the number of elements then fill down and reset your ranges automatically with VBA.

I guess VBA is the way to go. I am just a newbie to VBA. Can someone guide me how to do that or share some sample VBA code which can do this?

Thanks

I don't know how you plan to use this but I presume that it'll be a range of cells which fill a data validation dropdown or something similar. If that's the case you could do something like this. (I've used the Planning Sample's business units dimension for the example.)
1/ The lists will presumably be on a dedicated hidden sheet. Let's say that your sheet is called "Lists".
2/ Define two ranges on lists via Insert -> Name -> Define... (I neither know nor currently care where this command is buried in the Excel 2007 Ribbons; I could find it if I had to, but if you do, that's left as an exercise for the reader.) The first one (in this example, you can use whatever you want) I'll call BusinessUnitsStart. I've defined that as cell A1 on the Lists sheet. The second one is called BusinessUnits. This is a dynamic range so it's defined by formula. In the "Refers To" box I entered:

Code: Select all

=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)

That range will expand or contract automatically to cover only the cells in column A which have entries.
3/ You can then create a procedure (which can be triggered by a command button or by the Workbook_Open event, or whatever you want) which is as follows:

Code: Select all

Sub RecreateBUList()
'(c) Alan Kirk, 2011

Const SC_DIMNAME As String = "Planning Sample:Plan_Business_Unit"

Dim l_NumberOfElements As Long

'Clear the existing range. If there are no entries this will
'error, but we don't care so we just skip it.
On Error Resume Next
ThisWorkbook.Worksheets("Lists").Range("BusinessUnits").ClearContents
On Error GoTo ErrorHandler

'Get the number of elements in the dimension.
l_NumberOfElements = Application.Run("DimSiz", SC_DIMNAME)

If l_NumberOfElements = 0 Then
    Err.Raise vbObjectError + 20000, , "No elements found in the " _
     & SC_DIMNAME & " dimension. Check that you are logged in."
End If

'Fill a range, starting at "BusinessUnitsStart" (which I've defined
'as A1 on the Lists sheet) with SubNm formulas to cover the size of
'the dimension. These will then be picked up by the "BusinessUnits"
'dynamic range.
ThisWorkbook.Worksheets("Lists").Range("BusinessUnitsStart") _
 .Resize(l_NumberOfElements, 1).FormulaR1C1 = _
 "=SUBNM(""Planning Sample:Plan_Business_Unit"","""",ROW())"

ExitPoint:

Exit Sub

ErrorHandler:

MsgBox "Error " & Err.Number & vbCrLf & Err.Description

Resume ExitPoint

End Sub

[tomok]

I know others on this site like VBA but my advice to you is don't use it. If you do then you can forget about ever using the report in TM1Web. Why overly complicate something that is quite easy using lotsaram's suggestion. All you do is create a hidden tab for all your lists and have a series of three-column ranges for all your lists. In column A put a series of index numbers, 1 thru whatever, leaving your self plenty of room for expansion. In column B you put the SUBNM formula that uses the index number from column B to pull back the corresponding subset member. In column C you put the following formula: =LEN(A1). This will be used to let you know when there is no subset member for the given index number in column B. Now, take the formulas in columns B and C and copy them down to the last row for which you have input an index number. If you think that the subset may grown to 100 members then use 100 rows, if you think 1000 then use 1000 rows, etc. Leave room for expansion of the subset.

Now you need to define the list range. In my example the tab has been named "Lists", my range name is "ValidChoices", and I am using 100 rows, modify as necessary. Create the new range in Excel and use the following in the Refers To box: =OFFSET(Lists!$A$1,0,0,COUNTIF(Lists!$C$1:$J$100,">0"),1). Now you can use data validation in Excel and use the range ValidChoices. It will be a drop-down of only those rows for which a subset member exists.

[jim wood]

Tomok,

I agree with you to a degree. I don't however see TM1 web as the only reason not to use it. While VBA does have it's place in making things automated etc. I feel that using it for simple dimension element list is like using a sledge hammer for a small tack. But that is just my humble opinion,

Jim.

[ExApplix]

I know others on this site like VBA but my advice to you is don't use it. If you do then you can forget about ever using the report in TM1Web.

Thanks Tomok for the suggestion. My goal is to populate a ComboBox and then use the spreadsheet over the TM1Web. I will work on your guidelines and will let you guys know if there is any problem.

Also a Big thanks to Alan...I have tried your VBA code and it is working but as Tomok said VBA will not work over the Web so I will have to adopt other method.

[tomok]

My goal is to populate a ComboBox and then use the spreadsheet over the TM1Web.

A combo-box object from the VBA toolkit will not work. The only drop-down type object that works in TM1Web for 9.4, at least that I am aware of, is going to be data validation where you specify a range that holds a list of the acceptable choices for the cell.

[ExApplix]

Create the new range in Excel and use the following in the Refers To box: =OFFSET(Lists!$A$1,0,0,COUNTIF(Lists!$C$1:$J$100,">0"),1). Now you can use data validation in Excel and use the range ValidChoices. It will be a drop-down of only those rows for which a subset member exists.

This is working but a couple of questions:

1) Instead of having a seperate coulum for LEN() can we do something like COUNTIF(LEN($B$1:$B$15),">0") in the Named Range? I think the answer is NO, but if there is a way then it will be better.

2) I tried to use ComboBox instead of using Excel Data Validation but in the ListFillRange when I enter the name of the Named Range it gets cleared. Can we use ComboBox for this purpose and will it work over TM1Web?

EDIT: By the time I posted it, Tomok already answered number (2). Thanks again.

[ellissj3]

hello,

I have done something similar to this. Please see the below excel solution. Hope this helps you.

Thank you,
Steve

[Alan Kirk]

I agree with you to a degree. I don't however see TM1 web as the only reason not to use it. While VBA does have it's place in making things automated etc. I feel that using it for simple dimension element list is like using a sledge hammer for a small tack. But that is just my humble opinion,

For a single dimension, maybe. But supposing that you had (and I have had) several dimensions of hundreds to thousands of elements each. One thing that Excel workbooks don't do well is scale. They bloat in size hugely, the more that goes into them. One option is that you incorporate thousands upon thousands of formulas in them to cover all of the elements that are and might be in the future, and hope that the workbook doesn't collapse under its own gravity. The other is to generate just the lists that you need via VBA.

If you're on Web, you can't do that so you wear the size of the bloated workbook.

There's never one single right way. Everything has advantages and disadvantages, and you just pick the one that has more of the former than the latter for any given set of circumstances.

[tomok]

The other is to generate just the lists that you need via VBA.

Whenever I use these "data validation lists" I always use a subset (no coincidence that the formula used is SUBNM) so I do my best to cull the list down inside TM1 so I am not attempting to create a drop-down of the entire dimension. Some creative thinking combining dynamic subsets and a user parameter cube can do wonders for this type thing. I know a lot of people here don't like dynamic subsets but if used properly they can be a fantastic tool for enriching the client experience. Just like you mentioned though, there is no one right answer, it all depends on what your goals are and what trade-offs you are willing to accept. That's TM1 at it's finest.

[jim wood]

For a single dimension, maybe. But supposing that you had (and I have had) several dimensions of hundreds to thousands of elements each.

That's when you do need the sledge hammer. As you stated (as requested originally) it is a bit much for a a single dimension.

[Alan Kirk]

For a single dimension, maybe. But supposing that you had (and I have had) several dimensions of hundreds to thousands of elements each.

That's when you do need the sledge hammer. As you stated (as requested originally) it is a bit much for a a single dimension.

In your opinion. And I didn't state, I said "maybe". It depends on a range of factors, some I mentioned (like whether there are multiple dimensions involved, and the OP didn't explicitly say this one way or the other though it may have been implied depending on how it's read), and some I didn't (like whether the code could be reused across multiple reports). I reiterate what I did state, which is that "There's never one single right way."

[jim wood]

You're not wrong. There is no single right way. It's just sometimes it's better to offer one as a start rather blind somebody asking for help with solutions that are may be (and I'm not aiming this at this casein particular) learning to run before walking. We have to keep in mind that this forum is now seeing an influx of new users picking up TM1. Some (as we know) have only a basic understanding.