TM1 Security and VBA
Posted: Thu Oct 13, 2011 3:04 pm
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
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