TM1 Security and VBA

[Carl Peach]

At the moment I am returning a subset based on MDX and using the following code to popluate a spreadsheet.

strService = "harmony dev:"
strCube = "HR Employee List Oracle"
strCostCentre = "Total Group"
strServCube = strService & strCube
strHR_Location = "All Oracle Locations"
strPersonType = "Employee"
strYear = "2011_12"
strMonth = "Sep"
strVersion = "All Versions"

strSubsetName = "FTE_By_Month"
strDimension = strService & "HR_Employee_Oracle"
strSubsetSize = Run("SUBSIZ", strDimension, strSubsetName)
lSubsetSize = val(strSubsetSize)
lRow = 0

For i = 1 To lSubsetSize
strElement = Run("SUBNM", strDimension, strSubsetName, i)
strFTE = Run("DBRW", strServCube, strYear, strMonth, strVersion, strCostCentre, strPersonType, strHR_Location, strElement, "FTE")
dFTE = val(strFTE)

If dFTE > 0 Then

strSurname = Run("DBRA", strDimension, strElement, "Surname")
strFirstName = Run("DBRA", strDimension, strElement, "First Name")
strCostCentreAttr = Run("DBRA", strDimension, strElement, "Cost Centre")

ActiveCell.Offset(lRow, 0 + OffsetNo).Value = strElement
ActiveCell.Offset(lRow, 1 + OffsetNo).Value = strSurname
ActiveCell.Offset(lRow, 2 + OffsetNo).Value = strFirstName
ActiveCell.Offset(lRow, 3 + OffsetNo).Value = strCostCentreAttr
ActiveCell.Offset(lRow, 4).Value = strFTE

lRow = lRow + 1
End If

Next i

The results returned are two cost centres having two employees each. However, I have set up security on the cost centre such that the user does not have access to one cost centre but using this code returns values for that cost centre and hence ignoring security.

Can you please help.

Moved in to the main TM1 forum. It was previously in useful code, hints and tips, Admin Team

[lotsaram]

This is an unfortunate artifact of using the TM1 macro functions as SUBNM etc will not respect user security rights. There is no easy way around this. You would need to resort to TM1 API development which adds several layers of complexity compared with the simplicity of running the available TM1 macros.

[paulsimon]

I haven't tried it but try running the M_CLEAR macro before you access your MDX based subset. That in some cases will force Excel to refresh its local copy of the MDX results.

Regards

Paul Simon