Universe to Group Mapping in Excel VBA Code

system · August 23, 2017, 5:52pm

Any help on this topic is really apprecaiated.

I’m tryuing to build the following.

Connections --> Corrsponding Universes --> Corresponding Groups --> Users in the Group --> Folders --> Reports in the folder.

So far, I was able to create the following

Groups and users wihin the group
2.Connection and corresponding universes.

Now I need to link Universes and its corresponding Groups, so that I can combine the above two.

Any assistance is really appreciaetd.

Code for Group to User Mapping


Private Sub CommandButton1_Click()
    Sheets("GrpUsers").Range("A5:L65000").ClearContents
    'Session Manager declaration
    'Updated 01/14/2013, added all references for BI4 assemblies, Ferdie_CienD.
    Dim SessionManager, Sess As SessionMgr
    'Enterprise Session declaration
    Dim esession As EnterpriseSession
    'InfoStore declaration
    Dim iStore As InfoStore
    'Info Objects declaration
    Dim Users, Groups As InfoObjects
    'Info Object declaration
    Dim UserItem, GroupItem As InfoObject
    'User Object declaration
    Dim GroupObject As UserGroup
    Dim UserObject As User
    
    ' new code
    Dim Rng As Excel.Range
    Dim RowNum, GroupNum As Long
    Dim SIName As String
    'Session Manager instanciation
    Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")
    'Enterprise Session instanciation
    Set esession = SessionManager.Logon("Administrator", "Password", "CMS", "secEnterprise")
    'Infostore instanciation
    Set iStore = esession.Service("", "InfoStore")

    Set Groups = iStore.Query("SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS Where SI_KIND='UserGroup' order by SI_NAME")
    
    RowNum = 4
    Set Rng = Sheets("GrpUsers").Cells
    For Each UserItem In Groups
        Set GroupObject = UserItem
        RowNum = RowNum + 1
 
        Rng(RowNum, 3) = GroupObject.Properties("SI_NAME")
        
        SIName = GroupObject.Properties("SI_NAME")
        
        Set Users = iStore.Query("SELECT SI_NAME,SI_KIND, SI_USERFULLNAME FROM ci_systemobjects WHERE descendants(""si_name='Usergroup-User'"", ""si_name ='" &amp; SIName &amp; "'"") AND SI_KIND='User' ")
            For Each GroupItem In Users
                Set UserObject = GroupItem
                RowNum = RowNum + 1
                Rng(RowNum, 4) = UserObject.Properties("SI_NAME")
                'Rng(RowNum, 5) = UserObject.Properties("SI_KIND")
                Rng(RowNum, 5) = UserObject.Properties("SI_USERFULLNAME")
                
           Next GroupItem
        
    Next UserItem
End Sub

Code for Connection to Universe Mapping


Private Sub CommandButton1_Click()
    Sheets("UnvConn").Range("A5:L65000").ClearContents
    'Session Manager declaration
    'Updated 01/14/2013, added all references for BI4 assemblies, Ferdie_CienD.
    Dim SessionManager, Sess As SessionMgr
    'Enterprise Session declaration
    Dim esession As EnterpriseSession
    'InfoStore declaration
    Dim iStore As InfoStore
    'Info Objects declaration
    Dim Users, Groups, Folders, Conn, UnvConn As InfoObjects
    Dim oConn As InfoObject
    'Info Object declaration
    Dim UserItem, GroupItem, FolderItem, ConnItem, UnvConnItem As InfoObject
    'User Object declaration
    Dim GroupObject As UserGroup
    Dim UserObject As User
    Dim UnvObject As Universe
    Dim FolderObject As Folder
    Dim ConnObject As Object
    Dim UnvConnObject As Object
        
        
    ' new code
    Dim Rng As Excel.Range
    Dim RowNum, GroupNum As Long
    Dim SIName As String
    Dim connSIID As String
    'Session Manager instanciation
    Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")
    'Enterprise Session instanciation
    Set esession = SessionManager.Logon("Administrator", "Password", "CMS", "secEnterprise")
    'Infostore instanciation
    Set iStore = esession.Service("", "InfoStore")
    
    ' Query to get the connections
     Set Conn = iStore.Query(" SELECT SI_ID,SI_NAME,SI_Description FROM ci_appobjects where SI_KIND in ('CCIS.DataConnection')")
    
    RowNum = 4

    Set Rng = Sheets("UnvConn").Cells
    For Each ConnItem In Conn
        Set ConnObject = ConnItem
        RowNum = RowNum + 1
        Rng(RowNum, 2) = ConnObject.Properties("SI_NAME")
        Rng(RowNum, 3) = ConnObject.Properties("SI_ID")
        Rng(RowNum, 4) = ConnObject.Properties("SI_DESCRIPTION")
        
        connSIID = ConnObject.Properties("SI_ID")
        SIName = ConnObject.Properties("SI_NAME")
        
        Set UnvConn = iStore.Query("SELECT SI_ID, SI_NAME, SI_OWNER FROM CI_APPOBJECTS WHERE descendants(""si_name='DATACONNECTION-UNIVERSE'"", ""si_id ='" &amp; connSIID &amp; "'"") ")
            For Each UnvConnItem In UnvConn
                Set UnvConnObject = UnvConnItem
                RowNum = RowNum + 1
                Rng(RowNum, 5) = UnvConnObject.Properties("SI_NAME")

                SIName = UnvConnObject.Properties("SI_NAME")
           
           Next UnvConnItem
        
    Next ConnItem
End Sub

308002184 (BOB member since 2017-08-09)

system · August 23, 2017, 8:11pm

I assume you mean you want to identify the user groups that have access to the universes?

If I have time tomorrow I’ll write up some sample code, but as as sketch:

Get the universes as InfoObject objects
For each, call InfoObject.SecurityInfo.ObjectPrincipalsEx(CeRightsModeAll). This will give you a collection of ObjectPrincipal objects, representing the groups that have inherited rights for the universe.
ObjectPrincipal.Name will give you the name of the principal (the user or user group).
You can interrogate each ObjectPrincipal object to confirm the access (it’s possible that a group have been denied access to the universe, so simply checking for the existing of inherited rights is not enough.

Note that the above will only capture groups that have access to the universe either explicitly or from the universe’s parent folder. It will not capture groups (or users) who have inherited access from a parent group. That might be ok for your needs, but worth keeping in mind.

joepeters (BOB member since 2002-08-29)

system · August 23, 2017, 8:56pm

Thanks a million for your help. This will be really helpful as I’m trying to build

Connections -> Universes -> Groups -> Users -> Folders -> Reports

Once single document would give full view.

Again, thx a million for your help and I will wait until tomorrow for your code.

308002184 (BOB member since 2017-08-09)

system · August 24, 2017, 1:23pm

Here’s some code:

Private Sub CommandButton1_Click()
Sub duh()
    Sheets("UnvConn").Range("A5:L65000").ClearContents
    'Session Manager declaration
    'Updated 01/14/2013, added all references for BI4 assemblies, Ferdie_CienD.
    Dim SessionManager, Sess As sessionMgr
    'Enterprise Session declaration
    Dim esession As enterpriseSession
    'InfoStore declaration
    Dim iStore As infostore
    'Info Objects declaration
    Dim Users As InfoObjects, Groups As InfoObjects, Folders As InfoObjects, Conn As InfoObjects, UnvConn As InfoObjects
    Dim oConn As InfoObject
    'Info Object declaration
    Dim UserItem As InfoObject, GroupItem As InfoObject, FolderItem As InfoObject, ConnItem As InfoObject, UnvConnItem As InfoObject
    'User Object declaration
    Dim GroupObject As UserGroup
    Dim UserObject As User
    Dim UnvObject As Universe
    Dim FolderObject As Folder
    Dim ConnObject As Object
    Dim UnvConnObject As Object
    
    Dim princ As objectprincipal
        
    ' new code
    Dim Rng As Excel.Range
    Dim RowNum, GroupNum As Long
    Dim SIName As String
    Dim connSIID As String
    'Session Manager instanciation
    'Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")
    Set SessionManager = New CrystalEnterpriseLib.sessionMgr
    

    'Enterprise Session instanciation
    Set esession = SessionManager.Logon("", "", "", "secEnterprise")
    'Infostore instanciation
    Set iStore = esession.Service("", "InfoStore")
    
    ' Query to get the connections
     Set Conn = iStore.Query(" SELECT SI_ID,SI_NAME,SI_Description FROM ci_appobjects where SI_KIND in ('CCIS.DataConnection')")
    
    RowNum = 4

    Set Rng = Sheets("UnvConn").Cells
    For Each ConnItem In Conn
        Set ConnObject = ConnItem
        RowNum = RowNum + 1
        Rng(RowNum, 2) = ConnObject.Properties("SI_NAME")
        Rng(RowNum, 3) = ConnObject.Properties("SI_ID")
        On Error Resume Next
        Rng(RowNum, 4) = ConnObject.Properties("SI_DESCRIPTION")
        On Error GoTo 0
        
        connSIID = ConnObject.Properties("SI_ID")
        SIName = ConnObject.Properties("SI_NAME")
        
        Set UnvConn = iStore.Query("SELECT SI_ID, SI_NAME, SI_OWNER FROM CI_APPOBJECTS WHERE descendants(""si_name='DATACONNECTION-UNIVERSE'"", ""si_id ='" &amp; connSIID &amp; "'"") ")
            For Each UnvConnItem In UnvConn
                Set UnvObject = UnvConnItem
                Rng(RowNum, 5) = UnvObject.Properties("SI_NAME")

                SIName = UnvObject.Properties("SI_NAME")
                Rng(RowNum, 5) = SIName
                
                ' Iterate over each principal with inherited or explicit rights
                For Each princ In UnvObject.SecurityInfo.ObjectPrincipalsEx(CeRightsModeAll)
                    If checkView(princ) Then
                        Rng(RowNum, 6) = princ.Name
                        RowNum = RowNum + 1
                    End If
                Next
                RowNum = RowNum + 1
            
           Next UnvConnItem
        
    Next ConnItem
End Sub


Function checkView(princ As objectprincipal) As Boolean
    checkView = False
    
    On Error GoTo noExplicit
    checkView = princ.Rights.Item("#" &amp; ceRightView).Granted
    Exit Function
    
noExplicit:
    Resume checkInherited
checkInherited:
    
    On Error GoTo skipOut
    checkView = princ.InheritedRights.Item("#" &amp; ceRightView).Granted
skipOut:
    
End Function

This iterates over the ObjectPrincipals collection returned from UnvObject.SecurityInfo.ObjectPrincipalsEx, then checks whether the principal has View access on the universe.

Few things to note:
[list][:8331c005ed]The Java SDK is way more advanced than COM/.NET, and it has a simple method for checking a right for a specified principal. The COM SDK does not, so we have to check explicit rights first, then inherited rights.
[:8331c005ed]This line in your code:
Dim Users, Groups, Folders, Conn, UnvConn As InfoObjects
Isn’t doing what you think it is, and is a common mistake (and one of the reasons why I hate VBA). It’s only declaring UnvConn as an InfoObjects; the rest are Variants. You have to specify “As InfoObjects” for each one.
[:8331c005ed]I’m no VBA expert, so my checkView() function could probably be improved.
[:8331c005ed]This is only checking for the View right, which is enough to see the universe but not enough to refresh a report.
[*:8331c005ed]As I mentioned in my prior post, this won’t display subgroups of groups that have access. [/list]
Joe

joepeters (BOB member since 2002-08-29)

system · August 24, 2017, 1:43pm

Joe…Thanks a lot for the code…it worked like a charm. you are super genius…Apprecaiet your inputs on the VBA code. I will be looking into the Java SDK once I provide the required data to Audit.

I need to add Folders and Reports to the existing code. I will be exploring on this. If you could share idea/logic to retrieve corresponding Folders and its reports - it would be greatful.

Again appreciate your help…this means a lot

308002184 (BOB member since 2017-08-09)

system · August 24, 2017, 1:46pm

If Java is an option, I’d definitely suggest looking into it. I started out doing BO SDK work in VBA but switched to Java many years ago. It’s not only more functional but has much more documentation.

Note, however, that SAP is putting more effort into the REST APIs now as compared to .NET and Java.

How do you want to link universes to folders? Do you want the reports associated with the universes, and the folders that those reports are in?

joepeters (BOB member since 2002-08-29)

system · August 24, 2017, 2:55pm

Yes, I woudl like to display the Reports associated with the universe and its Folders.

This would be a big help.

Thank you.

308002184 (BOB member since 2017-08-09)

system · August 25, 2017, 11:57am

You can do the last two steps with relationship queries.

Let’s say you have a universe with ID 5187. The following query will produce a list of all WebI reports associated with that universe:

select si_name,si_id,si_parent_folder from ci_infoobjects where parents("si_name='webi-universe'","si_id=5187") and si_instance = 0

Note that this logic isn’t perfect. In some cases, a WebI document will lose its association to its universe. The report will still work, but it won’t have a reference in the CMC to the universe. So it will not appear in the query above.

Finally, to get the folders, just re-query on the si_parent_id property.

By the way, do you have an unx universes? If so, you will need additional code.

Joe

joepeters (BOB member since 2002-08-29)

system · August 25, 2017, 1:40pm

Thanks Joe. We do have unx universes.

308002184 (BOB member since 2017-08-09)

system · August 25, 2017, 2:10pm

Ok, then you will need to change this line:

Set UnvConn = iStore.Query("SELECT SI_ID, SI_NAME, SI_OWNER FROM CI_APPOBJECTS WHERE descendants(""si_name='DATACONNECTION-UNIVERSE'"", ""si_id ='" &amp; connSIID &amp; "'"") ")

To:

Set UnvConn = iStore.Query("SELECT SI_ID, SI_NAME, SI_OWNER FROM CI_APPOBJECTS WHERE descendants(""si_name='DATACONNECTION-UNIVERSE'"", ""si_id ='" &amp; connSIID &amp; "'"") or parents(""si_name='dsl.universe-securedconnections'"",""si_id=" &amp; connSIID &amp; """) ")

in order to capture the unx universes associated with the connections. You’ll also need to adjust the universe-webi query to include unx universes.

Joe

joepeters (BOB member since 2002-08-29)

system · August 25, 2017, 7:59pm

Thanks Joe. Can you share the code snippet to retrieve the Folder Names.

308002184 (BOB member since 2017-08-09)

system · August 28, 2017, 5:50pm

Given a WebI report with ID 1234, this will print out its name and ancestor folders:

    Dim ios As InfoObjects
    Dim io As InfoObject
    Dim iop As CrystalFolderPlugin.Folder
    Dim parentID As Integer
    Dim path As ISProperties
    Dim pathseg As ISProperty
    Dim x As Integer
    
    Set ios = oInfostore.Query("select top 100 si_parent_folder,si_id,si_name from ci_infoobjects where si_id = 1234")
    
    For Each io In ios
        parentID = io.Properties("SI_PARENT_FOLDER").Value
        
        Set iop = oInfostore.Query("select si_name,si_path from ci_infoobjects where si_id = " &amp; parentID).Item(1)
        Debug.Print "Document: " &amp; io.title
        Set path = iop.Properties("SI_PATH").Value
        For x = path.Item("SI_NUM_FOLDERS").Value To 1 Step -1
            Debug.Print "  " &amp; path.Item("SI_FOLDER_NAME" &amp; x)
        Next
        Debug.Print "  " &amp; iop.title
    Next

joepeters (BOB member since 2002-08-29)

system · August 30, 2017, 2:59pm

Thanks for your response. I really appreciaet it. I was able to achive it in excel . Now I’m trying to replicate the entire project in Java so that it is easier to schedule a job to genertae the users/groups and access for auditing purposes. Appreciate all the help.

308002184 (BOB member since 2017-08-09)

system · August 30, 2017, 3:04pm

Eh, would have been easier if you started in Java! Most of the logic above will work, but you can make use of ISecurityInfo2.checkRight() instead of the checkView() function I wrote.

Joe

joepeters (BOB member since 2002-08-29)

system · August 30, 2017, 8:01pm

Thanks Joe. I’m new to Java. Is there any sample code you have for Jvaa. I can use it as a base and levergae it to achive from what I have written in VBA.

308002184 (BOB member since 2017-08-09)

system · September 13, 2017, 6:08pm

Joe - Is there a way to get the following

Creation Date of the Report
Last Modified Date of the Report
Last Refreshed Date of the Report.

Below is the complete code that retrieves, the user/groups/connection objects/universes and associated folders - Reports


Private Sub CommandButton1_Click()
    Sheets("Conn_Unv_Grp").Range("A5:L65000").ClearContents
    Sheets("Unv_RepFolders_Rep").Range("A5:L65000").ClearContents
    Sheets("GrpUsers").Range("A5:L65000").ClearContents
    'Session Manager declaration
    'Add all references for BI4 assemblies
        Dim SessionManager, Sess As SessionMgr
    'Enterprise Session declaration
        Dim esession As EnterpriseSession
    'InfoStore declaration
        Dim iStore As InfoStore
    'InfoObjects Declaration
        Dim Conn As InfoObjects
        Dim UnvConn As InfoObjects
        Dim Documents As InfoObjects
        Dim Users As InfoObjects
        Dim Groups As InfoObjects
    'InfoObject Declaration
        Dim DocumentItem As InfoObject
        Dim ConnItem As InfoObject
        Dim UnvConnItem As InfoObject
        Dim UserItem As InfoObject
        Dim GroupItem As InfoObject
    'User Object Declaration
        Dim UnvObject As Universe
        Dim ConnObject As Object
        Dim DocHierParent As Object
        Dim RepDocHierParent As Object
        Dim princ As ObjectPrincipal
        Dim GroupObject As UserGroup
        Dim UserObject As User
    'String Declaration
        Dim ReportID As String
        Dim FolderList As String
        Dim oldfolderlist As String
        Dim newfolder As String
        Dim DocReportID As String
        Dim docoldfolderlist As String
        Dim DocFolderList As String
        Dim docnewfolder As String
        Dim connSIID As String
        Dim SIName As String
        Dim unvSIID As String
        Dim GrpSIName As String
        Dim CMS_Name As String
    'Excel Range Declaration
        Dim Rng As Excel.Range
        Dim DocRng As Excel.Range
        Dim GrpRng As Excel.Range
     'Long Declaration
        Dim RowNum As Long
        Dim DocRowNum As Long
        Dim GrpRowNum As Long
    
        
        
        Worksheets("GrpUsers").Select
        With Worksheets("GrpUsers")
      
        MsgBox ("Environment Selected Is:" &amp; CMS_Name)
        
        Sheets("Conn_Unv_Grp").Range("C1").Value = CMS_Name
        Sheets("Unv_RepFolders_Rep").Range("C1").Value = CMS_Name
        Sheets("GrpUsers").Range("C1").Value = CMS_Name
        
On Error GoTo ErrorHandler
        
    'Session Manager Instantiation
    'Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")
        Set SessionManager = New CrystalEnterpriseLib.SessionMgr
    
    'Enterprise Session instanciation
    Set esession = SessionManager.Logon("Administrator", "Password", CMS_Name, "secEnterprise")
  
    'Infostore instanciation
    Set iStore = esession.Service("", "InfoStore")
    
    'Query to Retrieve Groups and Users
    Set Groups = iStore.Query("SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS Where SI_KIND='UserGroup' order by SI_NAME")
    
    GrpRowNum = 4
    
    Set GrpRng = Sheets("GrpUsers").Cells
    
    For Each UserItem In Groups
        Set GroupObject = UserItem
        GrpRowNum = GrpRowNum + 1
 
        GrpRng(GrpRowNum, 2) = GroupObject.Properties("SI_NAME")
        
        GrpSIName = GroupObject.Properties("SI_NAME")
        
        Set Users = iStore.Query("SELECT SI_NAME,SI_KIND, SI_USERFULLNAME FROM ci_systemobjects WHERE descendants(""si_name='Usergroup-User'"", ""si_name ='" &amp; GrpSIName &amp; "'"") AND SI_KIND='User' ")
            For Each GroupItem In Users
                Set UserObject = GroupItem
                GrpRowNum = GrpRowNum + 1
                GrpRng(GrpRowNum, 3) = UserObject.Properties("SI_NAME")
                'Rng(RowNum, 5) = UserObject.Properties("SI_KIND")
                GrpRng(GrpRowNum, 4) = UserObject.Properties("SI_USERFULLNAME")
           Next GroupItem
    Next UserItem
    
    
    ' Query to get the connections
    Set Conn = iStore.Query(" SELECT SI_ID,SI_NAME,SI_Description FROM ci_appobjects where SI_KIND in ('CCIS.DataConnection') order by SI_NAME")

    RowNum = 4
    DocRowNum = 4
    
    Set Rng = Sheets("Conn_Unv_Grp").Cells
    
    For Each ConnItem In Conn
        Set ConnObject = ConnItem
        RowNum = RowNum + 1
        Rng(RowNum, 3) = ConnObject.Properties("SI_NAME")
        On Error Resume Next
        Rng(RowNum, 4) = ConnObject.Properties("SI_DESCRIPTION")
        On Error GoTo 0
        connSIID = ConnObject.Properties("SI_ID")

        Set UnvConn = iStore.Query("SELECT SI_ID, SI_NAME, SI_OWNER, SI_DESCRIPTION FROM CI_APPOBJECTS WHERE descendants(""si_name='DATACONNECTION-UNIVERSE'"", ""si_id ='" &amp; connSIID &amp; "'"") ")
            For Each UnvConnItem In UnvConn
                Set UnvObject = UnvConnItem
                Rng(RowNum, 5) = UnvObject.Properties("SI_NAME")
                Rng(RowNum, 7) = UnvObject.Properties("SI_DESCRIPTION")
                SIName = UnvObject.Properties("SI_NAME")
                unvSIID = UnvObject.Properties("SI_ID")

       'Start Code To Retrieve Universe Folders
                'Rng(RowNum, 7) = UnvConnItem.id
                'Rng(RowNum, 8) = UnvConnItem.ParentID
                'Rng(RowNum, 9) = UnvConnItem.Title

                ReportID = UnvConnItem.id
                FolderList = ""

                If UnvConnItem.Parent.ParentID <> 4 Then
                    If ReportID <> 0 Then
                    Set DocHierParent = UnvConnItem.Parent
                        While ReportID > 200
                            oldfolderlist = FolderList
                            newfolder = DocHierParent.Title
                            If FolderList = "" Then Rng(RowNum, 2) = newfolder
                            FolderList = newfolder &amp; "\" &amp; oldfolderlist
                            ReportID = DocHierParent.ParentID
                            Set DocHierParent = DocHierParent.Parent
                        Wend
                            'Rng(RowNum, 8) = newfolder
                            'Rng(RowNum, 9) = Left(FolderList, Len(FolderList) - 1)
                    End If
                End If

        ' Iterate over each principal with inherited or explicit rights
                For Each princ In UnvObject.SecurityInfo.ObjectPrincipalsEx(CeRightsModeAll)
                    If checkView(princ) Then
                        Rng(RowNum, 6) = princ.Name
                        RowNum = RowNum + 1
                    End If
                Next
                RowNum = RowNum + 1
   
           '
        'End Code To Retrieve Universe Folders
        
    'Start Code to Retrieve Report Folder Code

        Set DocRng = Sheets("Unv_RepFolders_Rep").Cells
        
        Set Documents = iStore.Query("select si_name,si_id,si_parent_folder, si_last_run_time from ci_infoobjects where parents(""si_name='webi-universe'"",  ""si_id ='" &amp; unvSIID &amp; "'"") and si_instance = 0 ")
            
            DocRowNum = DocRowNum + 1
            DocRng(DocRowNum, 2) = SIName
        
        For Each DocumentItem In Documents
           'DocumentItem.id
            'DocRng(DocRowNum, 4) = DocumentItem.ParentID
            DocRng(DocRowNum, 5) = DocumentItem.Title
            'DocRng(DocRowNum, 6) = DocumentItem.GetUpdateTimeStamp
            DocReportID = DocumentItem.id
            DocFolderList = ""

        On Error GoTo Parent_not_Found
        
        If DocumentItem.ParentID <> 49 And DocumentItem.ParentID <> 60 Then
            If DocumentItem.Parent.ParentID <> 4 Then
                If DocReportID <> 0 Then
                Set RepDocHierParent = DocumentItem.Parent
                    While DocReportID <> 0
                        If DocReportID <> 4 Then
                            docoldfolderlist = DocFolderList
                            docnewfolder = RepDocHierParent.Title
                            DocFolderList = docnewfolder &amp; "\" &amp; docoldfolderlist
                            DocReportID = RepDocHierParent.ParentID
                            Set RepDocHierParent = RepDocHierParent.Parent
                        Else
                            DocReportID = RepDocHierParent.ParentID
                            Set RepDocHierParent = RepDocHierParent.Parent
                        End If
                    Wend
            DocRng(DocRowNum, 3) = docnewfolder
            DocRng(DocRowNum, 4) = Left(DocFolderList, Len(DocFolderList) - 1)

            End If
        End If
            Else
                If DocumentItem.ParentID = 49 Then
                    DocRng(DocRowNum, 2) = "Temporary Storage"
                    DocRng(DocRowNum, 3) = "Temporary Storage\Instances"
                Else
                    DocRng(DocRowNum, 2) = "Internal Storage Folder"
                    DocRng(DocRowNum, 3) = "Internal Storage Folder"
                End If
            End If

Parent_not_Found_Resume:
        On Error GoTo ErrorHandler
        
    DocRowNum = DocRowNum + 1
    Next DocumentItem

' End Code to Retrieve Report Folder

    Next UnvConnItem
    Next ConnItem
    
    Set Documents = Nothing
    Set Rng = Nothing
    Set DocRng = Nothing
    Set SessionManager = Nothing
    Set iStore = Nothing
    On Error Resume Next
    esession.Logoff

CleanUp:
    On Error Resume Next
    esession.Logoff
    Exit Sub
    
ErrorHandler:
        MsgBox Err.Source &amp; " - " &amp; Err.Number &amp; ":  " &amp; Err.Description &amp; " " &amp; Err.HelpContext, _
        vbCritical, "Failure in CommandButton1_Click()"
    Resume CleanUp

Parent_not_Found:
    Resume Parent_not_Found_Resume
 
End Sub


Function checkView(princ As ObjectPrincipal) As Boolean
    checkView = False
    
    On Error GoTo noExplicit
    checkView = princ.Rights.Item("#" &amp; ceRightView).Granted
    Exit Function
    
noExplicit:
    Resume checkInherited
checkInherited:
    
    On Error GoTo skipOut
    checkView = princ.InheritedRights.Item("#" &amp; ceRightView).Granted
skipOut:
    
End Function

308002184 (BOB member since 2017-08-09)

system · September 13, 2017, 6:27pm

Creation Date of the Report: Yes - DocumentItem.Properties(“SI_CREATION_TIME”)
Last Modified Date of the Report: Yes - DocumentItem.GetUpdateTimeStamp
Last Refreshed Date of the Report: No, not with VBA. You need to use the Raylight REST API

To get Creation/Modified date, the CMS Query needs to include the si_creation_time and si_update_ts properties.

Joe

joepeters (BOB member since 2002-08-29)

system · September 14, 2017, 3:27pm

Thank you

308002184 (BOB member since 2017-08-09)