BusinessObjects Board

Finding list of reports based on a Universe in folders

Hi,

I have got this requirement as we are planning to migrate from BO3.1 to BI4.0.

And for this, it would be great if we can get below information from some excel macro or query -

Folder Path — Report Name ---- Universe Used

Suppose we have 10 main folders and those 10 folders have 50 subfolders divided according to region and there are n no. of reports in those folders. Now I need to find the count of reports based on each universe and the folder location.

Can someone please guide me if I can get this information, else this will be very hectic task for me to do… :crazy_face: :crazy_face:

I am trying to search one post from HRS but not able to find it, thought it may help me…


aniketp :uk: (BOB member since 2007-10-05)

Try this:

Option Explicit
Dim oInfostore As Infostore
Dim allUnivs As InfoObjects

Sub duh()
    Dim oSessionMgr As CrystalEnterpriseLib.SessionMgr
    Dim oEnterpriseSession As CrystalEnterpriseLib.EnterpriseSession
    
    If oInfostore Is Nothing Then
        Set oSessionMgr = New CrystalEnterpriseLib.SessionMgr
        Set oEnterpriseSession = oSessionMgr.Logon("<userid>", "<password>", "<cms>", "secEnterprise")
        Set oInfostore = oEnterpriseSession.Service("", "InfoStore")
    End If
    
    Dim oIOs As InfoObjects
    Set oIOs = oInfostore.Query("select top 100000 si_id,si_name,si_universe from ci_infoobjects where si_kind = 'webi'")
    Set allUnivs = oInfostore.Query("select si_id,si_name from ci_appobjects where si_kind = 'universe'")
    
    Dim oIO As Webi
    Dim out As String
    
    Debug.Print "Document" &amp; vbTab &amp; "Path" &amp; "Universes"
    For Each oIO In oIOs
        out = oIO.Title &amp; vbTab &amp; getDocPath(oIO) &amp; vbTab &amp; getUnivList(oIO)
        Debug.Print out
    Next
End Sub

Function getDocPath(oIO As Webi) As String
    Dim out As String
    Dim tempIO As InfoObject: Set tempIO = oIO.Parent
    
    Do While tempIO.ID <> 0 And tempIO.ID <> tempIO.parentID And tempIO.ID <> 4
        If out = "" Then
            out = tempIO.Title
        Else
            out = tempIO.Title &amp; " / " &amp; out
        End If
        Set tempIO = tempIO.Parent
    Loop
    
    getDocPath = out
End Function


Function getUnivList(oIO As Webi) As String
    Dim out As String
    Dim univ As InfoObject
    Dim iUnivID As Long, x As Long
    
    For x = 1 To oIO.Universes.Count
        iUnivID = oIO.Universes(x)
        For Each univ In allUnivs
            If univ.ID = iUnivID Then
                If out <> "" Then out = ", " &amp; out
                out = out &amp; univ.Title
                GoTo vbaneedsarealbreakstatement
            End If
        Next
vbaneedsarealbreakstatement:
    Next
    getUnivList = out
    
End Function

joepeters :us: (BOB member since 2002-08-29)

You can also try the Repository Documentor located here.


clarence (BOB member since 2005-11-18)