Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/
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.appleglaze28 wrote: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.
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
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.appleglaze28 wrote: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.
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.appleglaze28 wrote: 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?
"SUBNM" is correct.appleglaze28 wrote: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
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
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.appleglaze28 wrote: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.
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 wrote: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.
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".Steve Vincent wrote: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.
Code: Select all
Call Application.Run("M_CLEAR")