Function for Dynamic Subset

[appleglaze28]

Well Paul's response did help with the issue of the need to close Excel just to allow a specified user to log in to his account retrieve the right elements with his rights however, until now I don't get why I'm having either alias and original element or both. But when I tried to not activate the alias...the element being dervied is perfect which in this case is numeric value.

User1.JPG (54.19 KiB) Viewed 3126 times

AdminUser.JPG (126.21 KiB) Viewed 3126 times

User2.JPG (38.26 KiB) Viewed 3126 times

You can see from my example...user 1's value of Store Dimension is mixed...while admin has alphanumeric (alias) and user 2 has numeric (original element name).

[Steve Rowe]

Hi apple,
Can you post the exact script that you are using to return the excel values?

My guess is there is a bug in your VBA or in the TM1 client that is mixing up the dimension index and subset index.

When the subset index and dimension index match and the user security allows access you get the correct result.
When they don't match the VBA returns an element which the user doesn't have access too and so you don't get the alias.

Cheers,

[appleglaze28]

I didn't notice it until now that the numeric value is the dimension index rather than what I assumed to be the original element name.

Code: Select all

Option Explicit

Private Sub CommandButton1_Click()
Call Application.Run("M_CLEAR")
'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("Reference")

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

l_Elements = Application.Run("SUBSIZ", "bigbox:store", "N Level")

For l_Row = 1 To l_Elements

    s_Element = Application.Run("SUBNM", "bigbox:store", "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

[Alan Kirk]

I didn't notice it until now that the numeric value is the dimension index rather than what I assumed to be the original element name.

That would be why I said previously

"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.

Also, you're aware that since you've decided to update to FP2 that code is no longer likely to work as per my previous post in this thread?

In FP1 or earlier a series of SubNm functions (which is what that code effectively is) will return the members of a public subset (other than the "All" or undefined ("") subset) that the user has access to.

In FP2 a series of SubNm functions will return the first n elements of the subset (where n is the number of elements that the user has access to) regardless of whether the user has access to those specific elements.

In other words, in FP2 (at least as per the test described) SubNm now works with all subsets in the way that it worked with the "All" or unspecified ("") subsets behave in previous versions.

This is obviously going to break functionality (including that procedure) which depended on the previous performance of the SubNm function.

It's all laid out in the post...