Subset Views

[appleglaze28]

Is there anyway I can get a list of the available subset views of dimensions and use it as a validation list in Excel? I want to use allow easy dynamic feature of the rows. This will allow the more flexibility and better view of the rows wherein you have plenty of elements in a dimension, You can create filters in excel to limit the elements available to the client.

[Alan Kirk]

Is there anyway I can get a list of the available subset views of dimensions and use it as a validation list in Excel? I want to use allow easy dynamic feature of the rows. This will allow the more flexibility and better view of the rows wherein you have plenty of elements in a dimension, You can create filters in excel to limit the elements available to the client.

I'm not sure what you mean by "subset views", and I'm not clear on whether it's a list of subset names or a list of element names that you want to use for the validation list.

If you want to get a list of subset names, there's no easy way to do it. If the user has access to the TM1 data directory, which most don't, it would be possible to iterate through the .sub files in the relevant folders using VBA code. However that's academic for most users. The only other way that I can think of doing it is via the TM1 API code, which isn't pretty either.

However none of that is relevant if all you really want is a list of elements that the user can see. In which case, it would be just as easy for you to:
- Create a public subset of all of the elements that any user can select from;
- Take advantage of the characteristic described in the thread http://forums.olapforums.com/viewtopic. ... 919&p=5318. Specifically, the fact that a SubNM function which points to a public subset will only return the elements that the user has access to provided that it's not the "All" or the "" (unnamed) subset; and
- Use a standard SubNm function pointing to that subset instead of using data validation. In this way you'd be limiting them to the elements that they can see, and you wouldn't have to bother with Excel filters.

Data validation lists in Excel blow chunks anyway; if someone copies an unauthorised value from another workbook and pastes it in, bang goes your validation formatting and bang goes your validated data entry. I only ever use it for the simplest cases; if a SubNm won't do for whatever reason I'd use either a Comb Box or something which drives the E_Pick macro when clicked.