How to I get names of universes used by Webi and Deski documents?

Recently I have run into a few customers who are interested in grabbing the names of the universes that their webi or deski reports are running against.

Customers do this for various reasons, such as to try and clean up their Enterprise systems by removing unused or underused universes.

Most try and use the query builder and find that this will not work as expected since the name of the universe is not directly tied to the documents that use them.  It takes multiple queries to get the information and doing is via the SDK is much faster and easier than doing it by hand in query builder.

The following code shows how to query for the universe name using the Enterprise SDK in VB.NET:

Full VB.NET codebehind

<textarea cols=”75″ rows=”10″>

Imports CrystalDecisions.Enterprise

Partial Class _Default

    Inherits System.Web.UI.Page

    ‘Logon and Enterprise vars

    Dim ceSessionMgr As New SessionMgr()

    Dim ceSession As EnterpriseSession

    Dim ceService As EnterpriseService

    Dim ceInfoStore As InfoStore

    Dim ceInfoObjects As InfoObjects

    Dim ceInfoObject As InfoObject

    Dim boUniObject As InfoObject

    Dim boUniObjects As InfoObjects

    ‘Used to store query string

    Dim sQuery As String = “”

    Dim userid As String = “Administrator”      ‘Enter user name here

    Dim password As String = “”    ‘Enter password here

    Dim aps As String = “localhost”          ‘Enter APS name here

    Dim auth As String = “secEnterprise”

    Dim UniverseId As String

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        ‘Log on to the APS

        ceSession = ceSessionMgr.Logon(userid, password, aps, auth)

        ceService = ceSession.GetService(“”, “InfoStore”)

        ceInfoStore = New InfoStore(ceService)

        ‘Grab all Webi templates

        sQuery = “SELECT SI_Name, SI_Universe FROM CI_INFOOBJECTS WHERE SI_ProgId = ‘CrystalEnterprise.Webi’ and si_instance = 0”

        ceInfoObjects = ceInfoStore.Query(sQuery)

        Response.Write(“<b>Webi documents and their universe</b>” & “<BR><BR>”)

        Response.Write(“<table border=1 cellspacing=2>”)

        ‘Loop through the objects for the universe ids of webi docs

        For Each ceInfoObject In ceInfoObjects

            ‘Output name of the webi doc

            Response.Write(“<tr><td>” + ceInfoObject.Properties(“SI_Name”).ToString + ” </td>”)

            ‘Get the id of the Universe and query for it

            UniverseId = ceInfoObject.Properties(“SI_Universe”).Properties(“1”).ToString

            sQuery = “Select SI_Name from ci_appobjects where si_id =” & UniverseId

            boUniObjects = ceInfoStore.Query(sQuery)

            ‘Output the name of the Universe

            Response.Write(“<td>” + boUniObjects(1).Title + ” </td></tr>”)

        Next

        Response.Write(“</table>”)

        Response.Write(“<BR><BR>”)

        ‘Grab all Deski templates

        sQuery = “SELECT * FROM CI_INFOOBJECTS WHERE SI_ProgId = ‘CrystalEnterprise.FullClient’ and si_instance = 0”

        ceInfoObjects = ceInfoStore.Query(sQuery)

        Response.Write(“<b>Deski documents and their universe</b>” & “<BR><BR>”)

        Response.Write(“<table border=1 cellspacing=2>”)

        ‘Loop through the objects for the universe name of the deski docs

        For Each ceInfoObject In ceInfoObjects

            ‘Output the deski doc name

            Response.Write(“<tr><td>” + ceInfoObject.Properties(“SI_Name”).ToString + ” </td>”)

            ‘Get the universe name and output

            UniverseId = ceInfoObject.ProcessingInfo.Properties(“SI_FULLCLIENTDATAPROVIDERS”).Properties(“1”).Properties(“SI_FCDP_SOURCENAME”).ToString

            Response.Write(“<td>” + UniverseId + ” </td></tr>”)

        Next

    End Sub

End Class

</textarea>

Hopefully this helps some of you looking into this issue.