=SUBNM funcion Filtering

[appleglaze28]

Is there anyway to filter the subnm function when used as a validation list in Excel...I noticed that when security is in place that the that specific elements that are allowed are shown but the restricted elements are also shown I think as index numbers...I'm not quiet sure have to check it out since the alias are also numbers.

[David Usherwood]

If the user has no access to an element, it won't come back from TM1. If the user has read or write access, it will. I don't think you get the index numbers back, though you should say what version you are on just in case it behaves oddly.
If you want to distinguish read and write access, you could make the ElementSecurity_<dimension> cube available to your code and pick up the status from there.

[Alan Kirk]

If the user has no access to an element, it won't come back from TM1.

Yes it will... if the subset is either the All or empty string ("") subset. (The aliases won't (since that involves reading from a cube, not just the dimension), but the base element name will if you access it by index number.) If it's any other public subset, you're correct. (http://forums.olapforums.com/viewtopic. ... 919&p=5318)

[appleglaze28]

I used an MDX filter to get the 0 level element since data entry happens on the leaf level. So what is the best approach on being able t use a dynamic subset as a validation data? Would a VBA Code be able to solve this?

[David Usherwood]

Alan - thanks. Can't think why I haven't been hit by this before.
Appleglaze28 - what are you trying to validate?

[Steve Vincent]

i got around it with an attribute "validationlist". If it is not blank then it gets ignored in a subset that is created with the following MDX;

Code: Select all

{FILTER( {TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Resource Breakdown] )}, ASC)}, 0)}, [Resource Breakdown].[ValidationList] = "")}

In my case the attributes are manually administered, but its not changing all that often so its not much a burden.

[appleglaze28]

Well I'm trying to create a drop down list a way for users to navigate in the excel template since most users who are not aware of TM1 would not notice that TM1 dimensions may be filtered by double clicking on the subset to determine what element they want. This is only noticed in the TM1 web since you will be able to see 2 arrow heads pointing downward on all cells containing SUBNM functions.

This will also be easier to navigate on valid cost center hierarchy whose rights are given to specific users. The list would and all its element would not be seen except whose rights you have.

[Alan Kirk]

Well I'm trying to create a drop down list a way for users to navigate in the excel template since most users who are not aware of TM1 would not notice that TM1 dimensions may be filtered by double clicking on the subset to determine what element they want. This is only noticed in the TM1 web since you will be able to see 2 arrow heads pointing downward on all cells containing SUBNM functions.

This will also be easier to navigate on valid cost center hierarchy whose rights are given to specific users. The list would and all its element would not be seen except whose rights you have.

Did you read the link that I gave in my previous post in this thread?

If you really want to do it that way (and I can't help but thinking that sticking an instruction on the sheet saying "Double click on the yellow (or whatever colour) cells to select your cost centre" would be considerably easier, but hey, your call...) you can:
- Create a hidden sheet;
- Put in a formula in cell A1 which has a formula like:

Code: Select all

=SUBNM("Server:CostCentres","Some Named Public Subset but NOT the one named 'All'",ROW(),"Descriptive Alias")

- Copy that down as far as is necessary (ie, for as many rows as any user is likely to need, then a bunch more);
- Give the range of cells that contain the formula a name. You could even create it as a dynamic range name if you wanted to exclude the SubNMs which return an empty string, but that's left as an exercise for the reader;
- Create a Combo Box from the Control Toolbox toolbar;
- Assign the range name to the Combo Box's ListFillRange property.

The SubNM formulas will only return the elements that the user has access to. Those will therefore be the only ones which appear in the Combo Box. (Though you'll have a stack of empty values at the end of the list if you don't use a dynamic range name which resizes to the same size as the populated rows. Not that that matters as the users probably won't scroll down that far.)

This effectively does the same thing as using a public subset and a double clickable SubNm formula, just with vastly more work and overhead.

[Alan Kirk]

- Put in a formula in cell A1 which has a formula like:

Code: Select all

=SUBNM("Server:CostCentres","Some Named Public Subset but NOT the one named 'All'",ROW(),"Descriptive Alias")

- Copy that down as far as is necessary (ie, for as many rows as any user is likely to need, then a bunch more);
- Give the range of cells that contain the formula a name. You could even create it as a dynamic range name if you wanted to exclude the SubNMs which return an empty string, but that's left as an exercise for the reader;

Oh what the hey... I'll put it in here in case anyone else searches for this in future since the syntax is slightly obscure. Dynamic range names are typically created using the CountA function, but that won't work in this case since the SubNM's that don't return elements return empty strings. CountA doesn't see this as "empty".

Assuming that the sheet which contains the SubNm formulas was named "Lists" and that the formulas were in column A, when the range is created the Refers To reference would be:

Code: Select all

=OFFSET(Lists!$A$1,0,0,COUNTIF(Lists!$A:$A,"?*"),1)

[Alan Kirk]

Well I'm trying to create a drop down list a way for users to navigate in the excel template since most users who are not aware of TM1 would not notice that TM1 dimensions may be filtered by double clicking on the subset to determine what element they want. This is only noticed in the TM1 web since you will be able to see 2 arrow heads pointing downward on all cells containing SUBNM functions.

Incidentally, there's another option there too (but again a river-lowering rather than bridge raising one IMHO); you can just put your own graphic object containing as many arrows as you want just near the element to be selected. You can then attach a VBA procedure to that graphic. The procedure can use the E_Pick macro which, by version 9 at least, does allow you to specify a particular subset as well if desired.