Function for Dynamic Subset

[appleglaze28]

I'd like to know if anyone knows what function to use and how to use it to be able to get dimensions subset whether dynamic or static in excel to be placed in another sheet as a validation table or reference for a specific field.

Cause TM1RPTROW needs reference to a cube name.

[John Hobson]

Assuming you have the dimension name available then

=SUBNM("Server:Dimension", "Subset", row()-row($a$1))

(Where row($A$1) might be different depending on what row you start the list on)


Should get you a list to use a a validation source

[appleglaze28]

=SUBNM("Server:Dimension", "Subset", row()-row($a$1))

Do i put a value inside the () in "row()" as well as change the value of the ($a$1) to something else this the first time I encountered these 2 arguements and I'm not sure how I'm suppose to get a list from it.

I do get connected to the Subset Editor...but I was hoping a type like what you use in Active Form that whatever the Dynamic Subset you choose it will automatically update the row whether u add or delete elements that fit your dynamic subset condition.

[John Hobson]

=SUBNM("Server:Dimension", "Subset", row()-row($a$1))

Subnm is the TM1 function to return a subset member according to

What server & dimension you select as Server:Dimension
An index number (of the element within the subset)

The row() function is Excel

row() with nothing in the brackets returns the row of the current cell, so if you are in row 2 and you use row() - (row($A$1) you get 1.

If you copy that to row 3 you get 2 etc. etc. etc.

That way you can copy the same formula down a range of cells the same formula to get a list of all of the members of a subset (subject of course to you copying the formula the maximum rows required)

[Steve Vincent]

i use the same thing on a subset of all the level zero elements, although i just use row()-9 rather than the cell ref to take the necessary number of rows off.

so if my list started in cell A10, then i'd need the 1st value of my subset (1) to use row()-9, which equates to 10-9=1. Each cell afterwards then just follows the sequence

Its a very good way of maintaining a valid values list in Excel, especially if that list changes frequently. With some MDX in the subset it uses i also managed to control the list even further, as not all the level zero elements we actually want people to use.

[Martin Ryan]

If it's a small dimension you can also use dfrst and dnext. This doesn't bring back a subset, but rather the whole dimension.

Martin

[appleglaze28]

Its unavoidable not to have alias for dimension elements. I was thinking can TM1 Function be used in VBA to check all the elements and its security related with the user name so that the VB script will view all the elements for that user is insert in a certain range of cell. Has anyone tried it?

[Steve Vincent]

i doubt it as checking security is an admin only command. only way i can see that being done is something like an attribute that checks the security via a rule, but i wouldn't be too keen on that as its likely to be quite a high load on the server if its being checked all the time.

[appleglaze28]

What is the best way to filter subsets of dimension that will be the list available in combo boxes for excel? Like for example I have 200 stores, and I'd like to ensure that before a user starts he data entry, the list of stores in his drop would just be restricted to those stores available to him. Since direct input of the =subnm function in excel will result in alias to be still seen. I was hopin any of you guys tried either a VBA approach on any TM1 Function to be able to filter those elements contained in a VBA.

[Alan Kirk]

What is the best way to filter subsets of dimension that will be the list available in combo boxes for excel? Like for example I have 200 stores, and I'd like to ensure that before a user starts he data entry, the list of stores in his drop would just be restricted to those stores available to him. Since direct input of the =subnm function in excel will result in alias to be still seen. I was hopin any of you guys tried either a VBA approach on any TM1 Function to be able to filter those elements contained in a VBA.

I am entirely at a loss to fathom how many ways and times you can ask what is basically the same question (http://forums.olapforums.com/viewtopic.php?f=3&t=1263) and expect a different answer.

Once more then.
- If you have a named public subset which lists all of the elements (as distinct from the "All" subset, which can't be used like this); and
- You use SubNM functions to return the members of that public subset, then
- The user will only see the elements that they have access to.

No aliases, no elements will be returned aside from the ones that they have access to.

No VBA, no coding, and no re-routing it through SQL Server is required.

[appleglaze28]

Sorry if I can be a big pain since I'm still new to VBA & TM1. I'm still not sure with the if what you guys are sayin I'm doing it right. I'm able to use the SUBNM function in VBA...now my problem is how to fill the range with the specific dimension elemnts base on the dimension security? I just wrote the 1 element name just to test if I'm write the code correctly since I'm not sure how to write the command as it would have if I wrote the command directly in excel =SUBNM("BIGBOX:STORE","N LEVEL", row(), 0).

Code: Select all

Sub UpdateList()
Dim s_Result As String

s_Result = Application.Run("SUBNM", "BIGBOX:STORE", "N LEVEL", "1", -1)
Range("A1") = s_Result

End Sub

[Alan Kirk]

Sorry if I can be a big pain since I'm still new to VBA & TM1. I'm still not sure with the if what you guys are sayin I'm doing it right.

You're not. And I reiterate that there is no reason or need to use VBA, particularly when you're unfamiliar with it, when you could get your validation list by simply putting a SubNM formula in a hidden sheet and copying it down as per the suggestion in the other thread. However if you insist, the errors in your code are easy to correct.

I'm able to use the SUBNM function in VBA...now my problem is how to fill the range with the specific dimension elemnts base on the dimension security?

There is no need to test for security. As David has told you, as I've told you, provided that you use a named public subset, the function will only return the elements that the user has access to.

I just wrote the 1 element name just to test if I'm write the code correctly since I'm not sure how to write the command as it would have if I wrote the command directly in excel =SUBNM("BIGBOX:STORE","N LEVEL", row(), 0).

Code: Select all

Sub UpdateList()
Dim s_Result As String

s_Result = Application.Run("SUBNM", "BIGBOX:STORE", "N LEVEL", "1", -1)
Range("A1") = s_Result

End Sub

"SUBNM" is correct.
"BIGBOX:STORE" is correct if that's the name of your server and dimension.
"N LEVEL" is correct if that's the name of your public subset.
"1" is NOT correct, unless that's the name of the actual element that you're returning, and I'm betting that it isn't given that you were using a row() function before. That should be 1, as in a number, and not "1" as in a text string.
-1 is where the alias, if any is supposed to go. That should be some form of text (or omitted completely), not a number.

[Alan Kirk]

One other thing to note is that in 9.0 at least (and this should be the same in later versions) SubSiz will return the number of elements in the subset that the user has access to, not the total number of elements. That means that you can use that to determine how many cells to populate.

("2 minute" code, tested but not extensively.)

Code: Select all

Sub GetUserSubNMs()
'Substitute your own sheet name for Sheet2 and
'your own server and dimension name.

Dim l_Elements As Long
Dim l_Row As Long
Dim wks As Excel.Worksheet
Dim s_Element As String

On Error GoTo ErrorHandler

Set wks = ThisWorkbook.Worksheets("Sheet2")

'Remove previous list if any
wks.Columns("A:A").ClearContents

l_Elements = Application.Run("SUBSIZ", "sdata1:region", "N Level")

For l_Row = 1 To l_Elements

    s_Element = Application.Run("SUBNM", "sdata1:region", "N Level", l_Row)

    With wks.Cells(l_Row, 1)
        .NumberFormat = "@" 'Format as text
        .Value = s_Element
    End With

Next

ExitPoint:
Set wks = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description

Resume ExitPoint
End Sub

[appleglaze28]

I was wondering how come when I typed SUBSIZ in VBA or directly in Excel I get a different value than you would see in SUBSET EDITOR. Even for someone with Admin rights I have access to all 24 elements but when I use the Subsiz function I ony get 3 as a result. Regardless the name of the user I log on I keep getting 3. Even when the Subset Editor says otherwise.

SubsetEditor.JPG (27.96 KiB) Viewed 20457 times

I'd also like to clarify one more thing. Why is it when I'm logged in as the Admin, the elements being returned is the Alias Name while for other users the element being retrieve is in its Original Element in this scenario a numeric value.

Cause If I try to read the script and from what i understood, it should be doing what I want it to do but its not.

[Alan Kirk]

I was wondering how come when I typed SUBSIZ in VBA or directly in Excel I get a different value than you would see in SUBSET EDITOR. Even for someone with Admin rights I have access to all 24 elements but when I use the Subsiz function I ony get 3 as a result. Regardless the name of the user I log on I keep getting 3. Even when the Subset Editor says otherwise.

Conincidentally three was the number of elements that I gave my non-Admin user access to when I did the test on the code last night. However I just upped it to 4 and ran the test again, and SubSiz delivered the correct new value. However that's in 9.0 and you're on 9.4; I don't have access to a copy of 9.4 at present so unless someone who does can test the code on that version in the meantime, I'll have to leave it until later to check whether there's some kind of regression bug in 9.4. Though I'd be surprised.

One thing that I've occasionally encountered is that when you switch between user logins in the same session of Excel, some results end up seemingly being cached and giving unexpected values. Rebooting Excel between logins usually fixes it up. (Though in practice these days if I need to test something on a non-Admin login I just fire up a session of Citrix to do it.)

Still, it may be worth restarting Excel and seeing whether you get the right result.

I'd also like to clarify one more thing. Why is it when I'm logged in as the Admin, the elements being returned is the Alias Name while for other users the element being retrieve is in its Original Element in this scenario a numeric value.

In which environment, though? If it's the subset editor then it's possible that they just have the alias switched off. If they really can't see the alias at all, it sounds like they lack read access to the }ElementAttributes_Store cube or the corresponding attributes dimension.

[appleglaze28]

I tried restarting Excel however the list of element does change to what is expect of the user.

I tried checked the {ElementAttribute_Store and all user group have write access to all Attribute. So this also makes me wonder how come there was one user when I loggd in, I got both alias and original element.

[Steve Vincent]

not got a chance yet but i am working on testing 9.4 FP2 in a month or so. I see you are on FP1, do you have a test server you can upgrade and try FP2 on? There is a long list of "fixes" in that (see seperate thread) and it might be something that is broken in FP1 that's causing it.

[Alan Kirk]

not got a chance yet but i am working on testing 9.4 FP2 in a month or so. I see you are on FP1, do you have a test server you can upgrade and try FP2 on? There is a long list of "fixes" in that (see seperate thread) and it might be something that is broken in FP1 that's causing it.

Anyone ever watch The West Wing? There was a scene in which a congressman had pulled a self-centred stunt and Josh Lyman sighed, slumped his shoulders and said in a resigned way "I am so sick of Congress, I could throw up".

Substitute "TM1 regression bugs" for "Congress", and that's how I'm feeling this fine evening. Honestly, I am so far over it that if I stretched out slightly I could just about reach Mars. However I'm not going to put this into the bugs forum until I can reproduce it on another platform.

Scenario: Windows XP Professional (since unfortunately I don't have a spare Windows Server 2003 instance to test this on at the moment, but I doubt that it would make a difference). The TM1 server is running as a remote service, not as a Local server. The default Planning Sample server was used for the test. The client is Excel 2003 SP3.

I created a dimension called Store as shown in the following image:

StoreDim.jpg (42.17 KiB) Viewed 20388 times

Three security groups were created:
NthAmerica
Europe
AsiaPac

These were given write access to the corresponding consolidations and children.

Three accounts were created:
usr1
usr2 and
usr3, which were assigned to the respective security groups shown above. (usr1 to NthAmerica, usr2 to Europe, usr3 to AsiaPac).

Two public subsets were created; N Level, which is a static subset of all N level elements, and N LevelD, which is an MDX-generated dynamic subset of the same elements.

Under 9.4.1 MR1 with no fix packs applied, and 9.4.1 MR1 FP1, the following occurred:
- When the code was run under the first user's account (using either subset), the correct elements were returned in the list. This is the case regardless of whether the account used was usr1, usr2 or usr3.
- The correct elements were also shown subset editor.

If that user disconnected, and another user logged in in the same Excel session, then:
- When the code is run, the list belonging to the first user would still be returned, however
- The second user's elements would be correctly shown in subset editor.

If you then shut down Excel and log in as the second user, then
- When the code was run under the second user's account (using either subset), the correct elements were returned in the list.
- The correct elements were also shown subset editor.

- When you log out and log in as (say) either usr1 or usr3, you'll still get usr2's elements, however
- The correct elements were returned in the Subset Editor.

This really isn't so bad, since I had previously indicated that I knew of some issues that occurred when you log in as different users in the same Excel session. It's long been a known issue (even though I can't see this specific problem happening in 9.0 SP3), and I think that most Admins know enough to recommend that you don't have multiple users in the same Excel session.

But now let's look at what 9.4.1FP2 does:
- No matter which user you log in as, the SubSiz function returns the correct subset size. HOWEVER... the SubNM function returns the indicated member of the entire subset, not just the members that the user can see.

For example, usr3 has access to two elements in the subsets; Sydney and Canberra. And the code returns two elements... but they're Chicago and New York, even though usr3 has no access to those elements.

The correct elements are still returned in the subset editor, and indeed the two that the code returns cannot be seen.

Oh yeah, let's all upgrade to the latest version, that should fix all of our problems.

If I have time tomorrow I'll run some tests on another machine and see whether I get the same results. If I do I'll post the details into the bugs forum (sans opinion statements), then I'll see whether I can find the portal to Narnia... I mean to the IBM support site, the one that doesn't keep looping me around in an endless loop of login screens.

(Edit 1: By the way, the SubNM worksheet function, run directly in a sheet, also returns elements of the subset that the user can't see in FP2. Edit 2: I changed the text above slightly; I shouldn't have said that it preforms "like the DimNm function" because it does only return members from the public subset, not the entire dimension. It's just that in FP2 it looks like they may have "fixed" (or "stuffed", if you've been using this functionality) the issue with SubNm returning different elements depending on what the user's access is. Yay for progress. Edit 3: Also, is it just me, or can no-one else find a corresponding mention of a change in SubNm's functionality when used with public subsets in either the Release Notes or the Fixed Issues document?)

Edit 4: OH YAAAAY, I'M FINALLY IN TO THE IBM WEB SITE!!! I've logged this as PMR number 83724 999 616, and will post when I have a response from IBM.

[appleglaze28]

I've actually upgraded to F2 and I'm still having the same issue wherein, I get either the orignal elemen name or the alias. In this case the numeric value is th original elment while the alphanumeric is the alias.

I just need to find out a better way to create templates, since I want to feed these filtered elements into a combox box.

[paulsimon]

Appleglaze

I can't say that I've read every word of this thread, however, one thing that I haven't seen mentioned is the use of the M_CLEAR Macro.

Running this

Code: Select all

Call Application.Run("M_CLEAR")

gets Excel's cached view of the world back in step with TM1. We use this to get a SUBNM derived list that works from a Dynamic Subset to update correctly, when a different user logs on.

Regards


Paul Simon