BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: Upcoming Events: SAP TechEd: Sep 28.

Universe to Group Mapping in Excel VBA Code

Goto page Previous  1, 2
 
Search this topic... | Search SDK (VBA/ASP/JSP)... | Search Box
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Author Message
308002184
Forum Member
Forum Member



Joined: 09 Aug 2017

Posts: 16



PostPosted: Wed Sep 13, 2017 1:08 pm 
Post subject: Re: Universe to Group Mapping in Excel VBA Code

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

Code:

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:" & 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 ='" & GrpSIName & "'"") 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 ='" & connSIID & "'"") ")
            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 & "\" & 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 ='" & unvSIID & "'"") 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 & "\" & 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 & " - " & Err.Number & ":  " & Err.Description & " " & 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("#" & ceRightView).Granted
    Exit Function
   
noExplicit:
    Resume checkInherited
checkInherited:
   
    On Error GoTo skipOut
    checkView = princ.InheritedRights.Item("#" & ceRightView).Granted
skipOut:
   
End Function

Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6145
Location: Connecticut, USA


flag
PostPosted: Wed Sep 13, 2017 1:27 pm 
Post subject: Re: Universe to Group Mapping in Excel VBA Code

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
Back to top
308002184
Forum Member
Forum Member



Joined: 09 Aug 2017

Posts: 16



PostPosted: Thu Sep 14, 2017 10:27 am 
Post subject: Re: Universe to Group Mapping in Excel VBA Code

Thank you
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Page 2 of 2 All times are GMT - 5 Hours
Goto page Previous  1, 2
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0143 seconds using 18 queries. (SQL 0.0032 Parse 0.0002 Other 0.0109)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo