Skip to Content

126 Comments

You must be Logged on to comment or reply to a post.

  1. Mike Watson

    Manikandan,

    Not sure where best to put this comment.

    Thank You!

    This blog series has been very useful, helping us with our upgrade efforts to BOE BIP 4.0.  You should be very pleased with your contribution to the community knowledge base.

    Thanks Again.

    Mike

    (1) 
      1. pramod k

        Mani,

         

        This post is really helpful .

         

        Can I build a universe on these tables ? I need to capture the user session in dashboard . I need to capture user credentials , like user ID and user name .

        (0) 
        1. Manikandan Elumalai Post author

          Pramod,

           

          You cannot really build universe on top of these tables as they are virtual in nature not physical. You

          need to really pull information using SDK and push it to your custom tables. then you build universe and do whatever you want.

           

          Regards

          Mani

          (0) 
      2. Sasi Kumar

        Hi Mani I looking for an script to run in QB… please share if you have any for it!

        Aim is to retrieve the list of groups assigned to an user in CMC. I came across where we can pull the list of user in a group but not what a user has. please do share if you have any on this.

        This is open to all 🙂

        (0) 
  2. Bikash Mohapatra

    Hi Mani,

     

    Can u please help me how to get the list of users logged in to BO in a period of time (like month wise info for a particular year)

     

    How many reports run (in a period)?

     

    And which user is accessing which report very frequently?

     

    Thanks

    Bikash

    (0) 
    1. Manikandan Elumalai Post author
      I would recommend you to have a look at Audit reports rather than COS repository as the information of your interest would be historical in nature rather than realtime snapshot.
      (0) 
      1. Bikash Mohapatra

        Hi Mani, Can u please help me, how to get the report refresh time in respect to the user ID from repository DB? I dont have the audit DB information with me. Using SAP BO 4.0. Please suggest.

        (0) 
        1. Manikandan Elumalai Post author

          As far as CMS database is being concerned the only available entry for the report refresh time is SI_LAST_RUN_TIME which doesnt gie you the duration instead the time on which report started refreshing. If your report is the scheduled one you can always get the refresh time from SI_STARTTIME & SI_ENDTIME columns.

           

          The required report refresh interval is available at individual report (.wid) level for which you need to write SDK code.

           

          Try this query and review your results for the statement above.

          SELECT * FROM CI_INFOOBJECTS WHERE SI_ID=5544 (SI_ID of your report)

          (0) 
  3. mahesh bondulabo

    Dear Team,

     

    I am new to sap bo, where exactly we need to test these queries to get the required output?

     

    o list all the Users

     

              SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’


     

    I mean at webi level or at universe level or at database level?? Plz provide the navigation It will be much helpful for me.



    Regards,

    Mahesh

    (0) 
  4. Roman Peijan

    Thank you Manikandan. Great stuff.

     

    Additional to your examples, I would like to know how to retrieve the used database tables of an universe star schema. Like Table1, Table2 is used within UniverseA. Is this retrievable? 

     

    Ty in advance

    Roman

    (0) 
  5. Missy Mazzola

    Hi Mani, et al,

     

    Is there anyway to script a ‘call’ to the querybuilder tool, pass the query, and then output the results to a text file?

    Thanks,
    Missy

    (0) 
    1. Andrew Dale

      you can do it with VBA in excel

      start a new spreadhseet and choose the developer tab from the ribon

      if it is not on the ribbon choose file-options-Customise ribbon and then tick Developer

       

      on the VBA menu choose tools-references and tick the following libraries

        Visual Basic for Applications

        Microsoft Excel 14.0 Office Library

        Microsoft Office 14.0 Office Library

        Crystal enterprise ImfoStore Library 14.0

        Crystal enterprise Framework Library 14.0

      I am on BO 4.0 so am using the 14.0 libraries, choose different ones if you are on a different version

       

      paste the following into the sheet code area

      this example sucks out information about universes with some side calls to connections and folder details

       

      you can customise the strSQL to get whatever you want

       

      Option Explicit
          Dim strName, strPassword, strCMS, strAuth As String
         
      Sub UniverseDetails()
          Dim SessionManager, Sess As SessionMgr
          Dim esession As EnterpriseSession
          Dim iStore As InfoStore
          Dim Universes As InfoObjects
          Dim UniverseItem As InfoObject

          Dim Rng As Excel.Range
          Dim ExitLoop As Boolean
          Dim RowNum
         
          Dim strSQL              As String
          Dim strConns            As String
          Dim strConnName         As String
          Dim strConnCUID         As String
          Dim strConnCUIDAll      As String
          Dim iterativeUNVcount   As Integer
          Dim unvCount            As Integer
          Dim unvCountStr         As String
          Dim i                   As Integer
        
          Dim vSI_DESCRIPTION
          Dim vSI_ID
          Dim vSI_OWNER
          Dim vSI_CUID
          Dim vSI_UPDATE_TS
          Dim vSI_DATACONNECTION
          Dim vSI_REVISIONNUM
          Dim vSI_LOCKER_ID
          Dim vSI_FOLDER          As String
          Dim vSI_FOLDER_NAME     As String
          Dim vSI_NAME
         
          On Error GoTo ErrorHandler
          ‘set your login details
              strName = “”
              strPassword = “”
              strCMS = “”

          ‘choose one of the following 3
              strAuth = “secEnterprise”
              ‘strAuth = “secWinAD”
              ‘strAuth = “secLDAP”

          ‘Infostore instanciation
          Set SessionManager = CreateObject(“CrystalEnterprise.SessionMgr”)
          Set esession = SessionManager.Logon(strName, strPassword, strCMS, strAuth)

      ‘your query goes here’
          strSQL = “Select  SI_LOCKER_ID , SI_ID, SI_UNIVERSE, SI_WEBI, SI_SL_DOCUMENTS, SI_FILES, SI_DATACONNECTION, SI_SL_UNIVERSE_TO_CONNECTIONS, SI_UPDATE_TS, SI_REVISIONNUM, SI_SL_VERSION_NUMBER, SI_DESCRIPTION, SI_OWNER, SI_CUID, SI_DERIVEDUNIVERSE, SI_COREUNIVERSE FROM CI_APPOBJECTS WHERE (SI_KIND = ‘Universe’ Or SI_KIND = ‘DSL.MetaDataFile’) “
          Set iStore = esession.Service(“”, “InfoStore”)
          Set Universes = iStore.Query(strSQL)
         
          ‘initialise variables
          unvCount = Universes.Count
          unvCountStr = Format(unvCount, 0)
          iterativeUNVcount = 0
         
          ‘set up sheet for this BO environment
          Set Rng = Sheets(1).Cells
          Sheets(1).Activate
          Rng.ClearContents
         
          RowNum = 1
          Rng(RowNum, 1) = “ID”
          Rng(RowNum, 2) = “CUID”
          Rng(RowNum, 3) = “Folder”
          Rng(RowNum, 4) = “Title”
         
          Rng(RowNum, 5) = “Description”
          Rng(RowNum, 6) = “Revision Number”
          Rng(RowNum, 7) = “Last Updated”
         
          Rng(RowNum, 8) = “Associated Connection(s)”
          Rng(RowNum, 9) = “Connection CUID”
          Rng(RowNum, 10) = “Locked by”

          ‘loop through the universes
          RowNum = RowNum + 1
          For Each UniverseItem In Universes
                 
              iterativeUNVcount = iterativeUNVcount + 1
             
              Application.StatusBar = ” Universes read ” + Format(iterativeUNVcount, 0) + ” of ” + unvCountStr
              DoEvents
             
              vSI_REVISIONNUM = Empty
              vSI_UPDATE_TS = Empty
              vSI_DATACONNECTION = Empty
              vSI_LOCKER_ID = Empty
              vSI_NAME = UniverseItem.Title
              vSI_CUID = UniverseItem.CUID
              vSI_FOLDER = UniverseItem.Parent.CUID
              Call getFolderDet(vSI_FOLDER, vSI_FOLDER_NAME)
                     
              Rng(RowNum, 1) = UniverseItem.ID
              Rng(RowNum, 2) = vSI_CUID
              Rng(RowNum, 3) = vSI_FOLDER_NAME
              Rng(RowNum, 4) = vSI_NAME
              Rng(RowNum, 5) = UniverseItem.Description ‘”Description”

              For i = 1 To Universes.Item(iterativeUNVcount).Properties.Count
                  ‘ vSI_UPDATE_TS
                  If Universes.Item(iterativeUNVcount).Properties.Item(i).Name = “SI_UPDATE_TS” Then
                      vSI_UPDATE_TS = i
                  End If
                  ‘ vSI_REVISIONNUM
                  If Universes.Item(iterativeUNVcount).Properties.Item(i).Name =”SI_REVISIONNUM” Or Universes.Item(iterativeUNVcount).Properties.Item(i).Name = “SI_SL_VERSION_NUMBER” Then
                      vSI_REVISIONNUM = i
                  End If
                  ‘ vSI_DATACONNECTION
                  If Universes.Item(iterativeUNVcount).Properties.Item(i).Name = “SI_DATACONNECTION” Or Universes.Item(iterativeUNVcount).Properties.Item(i).Name = “SI_SL_UNIVERSE_TO_CONNECTIONS” Then
                      vSI_DATACONNECTION = i
                  End If
                  ‘ vSI_LOCKER_ID
                  If Universes.Item(iterativeUNVcount).Properties.Item(i).Name = “SI_LOCKER_ID” Then
                      vSI_LOCKER_ID = i
                  End If
              Next
             
              If Not IsEmpty(vSI_REVISIONNUM) Then Rng(RowNum, 6) = UniverseItem.Properties.Item(vSI_REVISIONNUM).Value
              Rng(RowNum, 7) = UniverseItem.Properties.Item(vSI_UPDATE_TS).Value

              strConns = “”
              strConnCUIDAll = “”
              ‘there can be multiple connections so new query to find them all
              If Not IsEmpty(vSI_DATACONNECTION) Then
                  For i = 2 To Universes.Item(iterativeUNVcount).Properties.Item(vSI_DATACONNECTION).Properties.Count
                      Call getConnectionDet(Universes.Item(iterativeUNVcount).Properties.Item(vSI_DATACONNECTION).Properties.Item(i).Value, strConnName, strConnCUID)
                      If strConns = “” Then
                          strConns = strConnName
                      Else
                          strConns = strConns & vbCrLf & strConnName
                      End If
                      If strConnCUIDAll = “” Then
                          strConnCUIDAll = strConnCUID
                      Else
                          strConnCUIDAll = strConnCUIDAll & vbCrLf & strConnCUID
                      End If
                  Next
              End If
              Rng(RowNum, 8) = strConns
              Rng(RowNum, 9) = strConnCUIDAll
              If Not IsEmpty(vSI_LOCKER_ID) Then If UniverseItem.Properties.Item(vSI_LOCKER_ID).Value <> 0 Then Rng(RowNum, 10) = UniverseItem.Properties.Item(vSI_LOCKER_ID).Value

              ‘update row count for individual sheet
              RowNum = RowNum + 1
          Next UniverseItem
         
      CleanUp:
          On Error Resume Next
          esession.Logoff
          Exit Sub
         
      ErrorHandler:
          MsgBox Err.Source & ” – ” & Err.Number & “:  ” & Err.Description & ” ” & Err.HelpContext, _
              vbCritical, “Failure in getUniverseDet()”
          Resume CleanUp
      End Sub

      Sub getConnectionDet(connId As String, strConnName As String, strConnCUID As String)
          Dim SessionManager, Sess As SessionMgr
          ‘Enterprise Session declaration
          Dim esession As EnterpriseSession
          ‘InfoStore declaration
          Dim iStore As InfoStore
          ‘Info Objects declaration
          Dim Connections As InfoObjects
          ‘Info Object declaration
          Dim ConnectionItem As InfoObject
          ‘User Object declaration
          Dim j As Integer
          Dim strSQL As String
         
          On Error GoTo ErrorHandler
          ‘Session Manager instanciation
          Set SessionManager = CreateObject(“CrystalEnterprise.SessionMgr”)
          ‘Enterprise Session instanciation
          Set esession = SessionManager.Logon(strName, strPassword, strCMS, strAuth)
         
          strSQL = “SELECT SI_NAME, SI_CUID FROM CI_APPOBJECTS where SI_KIND = ‘CCIS.DataConnection’ and SI_ID = ” & connId

          ‘Infostore instanciation
          Set iStore = esession.Service(“”, “InfoStore”)
          Set Connections = iStore.Query(strSQL)
         
          For Each ConnectionItem In Connections
              If ConnectionItem.Properties.Count > 1 Then
                  strConnName = ConnectionItem.Properties.Item(1).Value
                  strConnCUID = ConnectionItem.Properties.Item(2).Value
              Else
                  strConnName = ConnectionItem.Properties.Item(“SI_NAME”).Value
                  strConnCUID = “***mising***”
             
              End If
          Next ConnectionItem
        
      CleanUp:
          On Error Resume Next
          esession.Logoff
          Exit Sub
         
      ErrorHandler:
          Debug.Print connId & ”  not found”
          MsgBox Err.Source & ” – ” & Err.Number & “:  ” & Err.Description & ” ” & Err.HelpContext, _
              vbCritical, “Failure in getConnectionDet()”
          Resume CleanUp

      End Sub

       

      Sub getFolderDet(strFolderCUID As String, strFolderName As String)
      ‘get the folder name for a given CUId passed from a universe parentfoldercuid
          Dim SessionManager, Sess As SessionMgr
          ‘Enterprise Session declaration
          Dim esession As EnterpriseSession
          ‘InfoStore declaration
          Dim iStore As InfoStore
          ‘Info Objects declaration
          Dim Folders As InfoObjects
          ‘Info Object declaration
          Dim FolderItem As InfoObject
          ‘User Object declaration
          Dim j As Integer
          Dim strSQL As String
         
          On Error GoTo ErrorHandler
          ‘Session Manager instanciation
          Set SessionManager = CreateObject(“CrystalEnterprise.SessionMgr”)
          ‘Enterprise Session instanciation
          Set esession = SessionManager.Logon(strName, strPassword, strCMS, strAuth)
         
          strSQL = “SELECT SI_NAME FROM CI_APPOBJECTS where SI_CUID = ‘” & strFolderCUID & “‘”

          ‘Infostore instanciation
          Set iStore = esession.Service(“”, “InfoStore”)
          Set Folders = iStore.Query(strSQL)
         
          For Each FolderItem In Folders
                  strFolderName = FolderItem.Properties.Item(“SI_NAME”).Value
          Next FolderItem
        
      CleanUp:
          On Error Resume Next
          esession.Logoff
          Exit Sub
         
      ErrorHandler:
          Debug.Print strFolderCUID & ”  not found”
          MsgBox Err.Source & ” – ” & Err.Number & “:  ” & Err.Description & ” ” & Err.HelpContext, _
              vbCritical, “Failure in getFolderDet()”
          Resume CleanUp

      End Sub

      (0) 
      1. Missy Mazzola

        Very cool – thanks.  I am still working through this to pair it down for what I need – which, for starters at least, is just to extract the username, but I greatly appreciate the detailed example – this is really awesome!

        -missy

        (0) 
      2. Missy Mazzola

        Have you noticed that the count of X read # by # differs than the actual result at all? Just trying to figure out if it is something I changed in the code or something else.

        For example, my query (via querybuilder) is returning 327 users (in my case) and the  Application.StatusBar message displays that accurately (which means the counts are being calculated right) but the actual number of users that get written is 321.  I am not sure what is different about 6 of the users/usernames that they aren’t getting written to the excel file though.

        (0) 
        1. Andrew Dale

          Hi Missy, my counts are correct, do you want to post your VBA and I can run it on my system and see if I can spot the issue?

          (0) 
          1. Missy Mazzola

            I just tried the generic code from your post below and that ran fine, with the counts matching… so I think I am good now, but thanks for the offer!

            thanks again for your help with this!!

            -missy

            (0) 
      3. Andrew Dale

        here is some generic code that should work for any query

        Option Explicit

        Sub doit()
        Dim strName As String
        Dim strPassword As String
        Dim strCMS As String
        Dim strAuth As String

        ‘set your login details
        strName = “”
        strPassword = “”
        strCMS = “”

        ‘choose one of the following 3
        strAuth = “secEnterprise”
        ‘strAuth = “secWinAD”
        ‘strAuth = “secLDAP”

        Call InfoObjectDetails(strName, strPassword, strCMS, strAuth)

        End Sub
         
           
        Sub InfoObjectDetails(strName As String, strPassword As String, strCMS As String, strAuth As String)
            Dim SessionManager As SessionMgr
            Dim esession As EnterpriseSession
            Dim iStore As InfoStore
            Dim InfoObjects As InfoObjects
            Dim InfoObject As InfoObject

            Dim Rng As Excel.Range
            Dim ExitLoop As Boolean
            Dim RowNum  As integer
           
            Dim strSQL              As String
            Dim InfoObjectsCount    As Integer
            Dim InfoObjectsCountStr As String
            Dim i                   As Integer
            Dim j                   As Integer
            Dim PropertyName        As String
           
            On Error GoTo ErrorHandler
           
            ‘Infostore instanciation
            Set SessionManager = CreateObject(“CrystalEnterprise.SessionMgr”)
            Set esession = SessionManager.Logon(strName, strPassword, strCMS, strAuth)

        ‘put your query here
            strSQL = “SELECT top 2000 * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’ “

            Set iStore = esession.Service(“”, “InfoStore”)
            Set InfoObjects = iStore.Query(strSQL)
           
            ‘initialise variables
            InfoObjectsCount = InfoObjects.Count
            InfoObjectsCountStr = Format(InfoObjectsCount, 0)
               
            ‘set up sheet for this BO environment
            Set Rng = Sheets(1).Cells
            Sheets(1).Activate
            Rng.ClearContents
           
            ‘I want these to appear first
            Rng(1, 1) = “SI_NAME”
            Rng(1, 2) = “SI_ID”
            Rng(1, 3) = “SI_CUID”
           
            RowNum = 2
            ‘loop through the InfoObjects
            For Each InfoObject In InfoObjects
                Application.StatusBar = ” Reading object ” + Format(RowNum – 1, 0) + ” of ” + InfoObjectsCountStr
                DoEvents
                For i = 1 To InfoObjects.Item(RowNum – 1).Properties.Count
                    j = 0
                    PropertyName = InfoObjects.Item(RowNum – 1).Properties.Item(i).Name
                    Do
                        j = j + 1
                        ‘loop through the names in the top row looking for this property or a blank column
                    Loop Until Rng(1, j) = PropertyName Or Rng(1, j) = “”
                    Rng(1, j) = PropertyName
                    Rng(RowNum, j) = InfoObjects.Item(RowNum – 1).Properties.Item(i).Value
                Next
                RowNum = RowNum + 1
            Next InfoObject
           
        CleanUp:
            On Error Resume Next
            esession.Logoff
            Exit Sub
           
        ErrorHandler:
            If Err.Number = 1004 Then   ’empty value
                Resume Next
            Else
                MsgBox Err.Source & ” – ” & Err.Number & “:  ” & Err.Description & ” ” & Err.HelpContext, _
                    vbCritical, “Failure in getUniverseDet()”
                Resume CleanUp
            End If
        End Sub

        (0) 
        1. Vinesh Pratapwar

          To get the list of all WebI Reports based on given Universe (.unv, .unx), following SQL code can be used in VBA.

          strSQL = “SELECT SI_ID, SI_NAME, SI_UNIVERSE, SI_DESCRIPTION, SI_CUID, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS  Where PARENTS ( ” & Chr(34) & “SI_NAME = ‘WEBI-UNIVERSE'” & Chr(34) & “,” & Chr(34) & “SI_NAME = ‘<universe name>‘” & Chr(34) & “)” & “OR PARENTS ( ” & Chr(34) & “SI_NAME = ‘Document-DSL.Universe'” & Chr(34) & “,” & Chr(34) & “SI_NAME = ‘<universe name.unx>‘” & Chr(34) & “)”

          Regards,

          Vinesh

          (0) 
          1. Venkat Konga

            Hi Vignesh,

            Above SQL worked for list of all WebI Reports based on given Universe.

            Can i get a list of webi reports based on a event name = ‘Run Daily Reports’

            strSQL = “SELECT SI_ID, SI_NAME, SI_UNIVERSE, SI_DESCRIPTION, SI_CUID, SI_OWNER FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS  Where PARENTS ( ” & Chr(34) & “SI_NAME = ‘Run Daily Reports'” & Chr(34) & “,” & Chr(34) & “SI_NAME = ‘Opex'” & Chr(34) & “)” & “OR PARENTS ( ” & Chr(34) & “SI_KIND = ‘Event'” & Chr(34) & “,” & Chr(34) & “SI_NAME = ‘Run Daily Reports'” & Chr(34) & “)”

            Tried this SQL but did not worked, do i need to do some more changes in this SQL ?

            Thanks,

            Venkat

            (0) 
  6. Anand Raj

    Is there a SI_ID data in Activity Universe or in tables used within activity universe? I am trying to find what all reports are refreshed for past 6 months.  

    This is how I am doing:

    I got all reports list from query builder.

    I got what all reports refreshed from activity universe. Now, I am trying to find unique identifier between both.

    Thanks for your help!

    (0) 
  7. Archana Desai

    Hi, Is there a way to get the exact SQL for the failing Ad hoc report using query builder?

    Any help with this is much appreciated.

    Thanks,

    Archana

    (0) 
  8. Rohit kumar

    Hi Mani,

    Could you please provide me any solution how to delete reports from user’s inbox until a particular date.e.g I want to delete all the reports from 10 mar 2014 to 20 may 2014.Is this possible using query builder are there are any other solution to do this.Quick response much appreciated.

    (0) 
  9. Rohit kumar

    Thanks Mani,

    Have you tested this tool.Since I am dealing with Production Environment and many business user using this server for their daily activities.If something going wrong then this will create a huge issue.

    I am using XI 3.1

    (0) 
  10. Archana Reddy

    Hello Mani,

    I am using below query to get all the users set up as Webi Query Editors. How to get the group names in the final result like which webi query editor group they belong to (the name) ?

    select

    SI_ENT_USERFULLNAME, SI_NAME, SI_EMAIL_ADDRESS from ci_systemobjects

    where children(“si_name = ‘usergroup-user'”, “si_name like  ‘%WebI_Query_Editor'”)

    (0) 
  11. Gayathri Subramaniam

    Mani, this is a great source of information for starting to use the Query Builder.

    Could you tell me what privileges I may require to be able to see all the information? I tried Admin user on my Production Repository however, it appears that I am only able to retrieve a subset of the information I am querying for when I compare it with the results from a Test Repository (granted there would be not so needed info in Test).

    Thanks!

    (0) 
  12. stephane boinon

    Hello Mani

    One simple question. Is it possible to retrieve SQL query(ies) content embedded into each data provider(s) from each webi reports by using query builder or not ?

    If yes could you provide us a basic query builder query example for a given webi report please ?

    Many thanks for your feedback.

    (0) 
  13. Christiaan de haes

    I need to extract schedule data from BOXI 2 and was hoping to use Query Builder for that.

    I doesn’t appear to have been installed on the application server.

    Is there a way of downloading and installing it one the server?

    Thanks for your help,  Christiaan de haes

    (0) 
  14. tilak mishra

    Mani and others,

    I need the following info for our scheduled reports between Jan to May from 4.1 CMS.

    Can you please help me what  query needs to be used for this.

    User | Report Name | Date | Month | Status | Duration

    I tried several times with several combination with the Query builder but not finding the right extract.

    Thanks,

    Tilak

    (0) 
  15. nadia denata

    Hi Mani,

    is there any query return the preferences of user.

    i need user list who use webi interactive viewer. (the user can activate this option in his preference)

    is there any solution to do this.

    Any help with this is much appreciated.

    Thanks

    Nadia

    (0) 
  16. Mark Last

    Hi experts,

    Is there any query to list the web Intelligence Report’s that are not executed since , for example, 1 year??? (executed online, not programmed).

    Thanks,

    M

    (0) 
  17. Carlos Lajas

    Hi everyone,

    I would like to know if Query Bulider can extract all the objects (dimensions, filters, metrics, data provider…) used in a repport (deski or webi)?

    I will be grateful if you can send me this information.

    Carlos

    (0) 
  18. sandrapati sravani

    Hi All,

    Is it possible to run a query by recipient for the reports housed in Crystal? I received a request to remove three associates from the distribution list, however was not advised for which reports.

    If this is possible, please advice how it should be requested and i will do so.

    (0) 
    1. Jawahar Konduru

        SELECT SI_ID,SI_NAME,SI_PARENT_FOLDER,SI_FILES FROM
      CI_INFOOBJECTS WHERE SI_KIND = ‘WEBI’ AND SI_INSTANCE = 0 AND SI_ANCESTOR = [SI_ID OF THE FOLDER]

      (0) 
      1. Mahanaz Syed

        Thanks Jawahar.

        Works like a charm. Can i export into some file as a report ?

        Is there a query that i can run directly against the audit database.

        Thanks again.

        We have a request where we wanted to create a Crystal report that give list of all reports in BO by folder(Including subfolders).

        (0) 
        1. Jawahar Konduru

          You can copy the results to excel. Out of box there is no export. Or you can develop a SDK to extract the same information.

          You cannot use the audit DB for this purpose.

          (0) 
    1. Siddhartha Nirgudkar

      Hi Eileen,

      Based on your question, the below query might help:

      select * from ci_infoobjects where si_kind=’WebI’ and si_parent_folder=FOLDER_ID and si_last_run_time=’yyyy.MM.dd.hh.mm.ss’

      • You can use <, > instead of =.
      • MM is Month, while
      • mm is minutes
      • Kind can be CrystalReport or WebI

      This would give you all reports within a folder with id=FOLDER_ID and of last run time as given.

      Hope this helps.

      -Sid

      (0) 
      1. Eileen Keck

        Thanks  for all the good info – I was looking actually to list the run time for the last instance for all reports and their paths within a particular ancestor folder. I did get the report complete with alot of manipulation but was wondering if there was an easy way to accomplish this.

        (0) 
          1. Eileen Keck

            The tedious way omg  lol but I did get a count of instances that way also. You asked:

            SELECT top 5000  SI_Name,SI_ID,SI_endtime,SI_Starttime

            FROM CI_INFOOBJECTS
            WHERE SI_KIND != ‘Folder’ AND SI_ANCESTOR = # and SI_Name like ‘A%’  

            I am in the process of getting a new pc but currently had to pull by alphabet and sometimes within the letter to get all or my pc froze.

            1. I copied it into Excel and filtered and grabbed Name and ID than sorted by name and ID and did a if statement to find the most current id ( t or false if the name was the same)

            2. I sorted by ID and put a count for ID I did a vlookup on the downloaded info and and grabbed the count on the downloaded spreadsheet. I was able to than grab the start and end times sort them by the ID and copy them into my spreadsheet. so I would up with Name, ID, T o rF if it was the latest instance,  Start-Time, End-Time, Count(#of Instances).

            Since it was requested for the last 5 instances – avg run time – I had to sort in reverse order and grab the latest 5- filtering – copy them and calculate the avg time

            oh by the way they also wanted the latest run time so I had to give that, which i of course had in the last 5 since I had to total each individual run time to get the average.

            Aren’t you glad you asked.

            If I could have pulled the information seperately and married them by ID that would have worked – If i export to excel rather than copy does it list in column order rather tha report where I would have to filter each column to grab the data?

            btw this was my initiation into BOB Query builder. Are there any other tools or way to access the data?

            (0) 
            1. Siddhartha Nirgudkar

              Haha that’s pretty heavy!!!

              Once you export the data to Excel, things should be easier as far as the analysis goes since you can manipulate Excel data in several ways.

              The trick would be to ensure that you’re getting all the raw data using QueryBuilder so that you can play around with it in Excel!

              Your last bit about pulling the information separately sounds more cleaner. Albeit, there would be a little more manual work since you’ll have to match them by ID or CUID later on to refine the data.

              Not as an alternate, but as a supplement I’d recommend pulling whatever data available through Auditing reports (ensuring that we are auditing the required events). You can then mix and match the data obtained via Audit reports + QB output.

              (0) 
              1. Eileen Keck

                Thanks – I was matching them by ID in Excel – perhaps I wasn’t clear enough:) Is there any other tool besides QB that enables you to utilize additional SQL functions such as with cte or scripting functionality to accomplish it?

                (0) 
  19. Gaurav Saxena

    Awesome Blog Mani.

    I have one question.

    When a report is scheduled, there is a PID generated for that instance.

    I am not sure if the instance related information is captured in these virtual tables.

    Is there any SQL in Query Builder , which will give me report names based on PID?

    Thanks

    Gaurav

    (0) 
  20. Sam Govaski

    Hello Manikandan Elumalai

    We are looking for following information for our system, I checked all if your blogs and did not see any information on it. Could you please help me out to run below query against the server? I will appreciate your help in this matter.

    Users logging in to the BOBJ to run the Webi/any reports?

    What user ran what report/s on what day?

    Thanks,

    Sam

    (0) 
    1. Siddhartha Nirgudkar

      Hi Sam,

      What you may be looking for is AUDITING.

      QueryBuilder is good to show MetaData of objects stored in your repository.

      To see which actions were performed, when and by whom Auditing is a better options.

      Mani has another blog which would help you out here:

      SAP BusinessObjects 4.0 Auditor Configuration & Deployment End to End

      If you decide to go with this, you would need these as well:

      Sample Auditing Universe and Reports for SAP BusinessObjects_4_x

      Lastly, the Administrator’s guide contains how and what auditing events work. So have a look at that as well.

      Hope this helps.

      -Sid

      (0) 
  21. NEERAJ SHARMA

    Hi Manikandan,

    Very informative topic.

    Just one query:

    How can I get the list of users who accessed a particular webi report on a particular day?

    Regards,

    Neeraj Sharma

    (0) 
  22. Mahanaz Syed

    Hello,

    Can anyone help me identify the crystal reports that uses dynamic parameters via Business view manager. Using query builder.

    I am trying to isolate reports based on Dynamic parameters and normal ones.

    Thanks

    (0) 
  23. Raymond de Rozario

    I was wondering if it’s possible to see what non-scheduled Crystalreport is running and who’s running it. I mean the ones that are stared manualy. What I’m looking for is a way to detect what report might cause a high CPU usage. Hopefully figure out what the PID is from that report and kill it?

    (0) 
      1. Mayank M

        Yes, I am able to access CMCAPP and INFOVIEW app. My website is hosted on IIS. I am unsure about WebApps folder. Can you please tell where to find that.


        Regards,

        Mayank

        (0) 
    1. Siddhartha Nirgudkar

      Hi,

      Are you using the full Enterprise version of BusinessObjects or are you on Edge?

      Also as suggested by Shankar check the folder:

      <install root>\tomcatx\webapps\ and see if a folder named “AdminTools” exists here.

      Check also in:

      <install root>\tomcatx\work\Catalina\localhost if AdminTools exists.

      Also ensure Tomcat is up and running.

      Regards,

      Sid

      (0) 
    2. Anand Raj

      Mayank – there are some macro enabled spreadsheets which will pull all the information related to bobj. I would suggest to search for those. I think its called as boxi repository documentor or something like that.

      (0) 
      1. Mayank M

        Thanks Anand.
        I tried that option but macros are giving “Transport error: Communication failure.(FWM 00001)” whose remediation requires SIA restart which is not feasible 🙁 .

        (0) 
        1. Manikandan Elumalai Post author

          Please check with your IIS Administrator/BO Administrator whether the AdminTools Web Applicaation is deployed or now. I have seen in some of the organisations where Admin Tools is not deployed to restrict the back door entry on BO metadata.

          Thanks

          Mani

          (0) 
  24. Enrique Rollano

    Hi Mani,

    I need to get a list of users’ Personal Folders contents with information such as user  ID, folder, Report Name, Description, etc.

    The statement below takes me somewhere but I am missing the subfolders Names (~Webintelligence, etc.):

    SELECT SI_KIND,SI_PARENT_FOLDER, SI_NAME, SI_DESCRIPTION, SI_OWNER, SI_UPDATE_TS FROM CI_INFOOBJECTS WHERE SI_INSTANCE = 0 AND SI_KIND NOT IN(‘Folder’) AND SI_ANCESTOR =18 AND SI_DESCRIPTION != ” ORDER BY SI_OWNER ASC

    How can I achieve this?

    Thank you!

    (0) 
    1. Siddhartha Nirgudkar

      Hi Enrique,

      Could you please elaborate what is meant by “missing the subfolders names”?

      Do you also want the query to give folder names?

      SI_PARENT_FOLDER will give you the ObjectID of the folder that any given object is located inside.

      SI_KIND NOT in(‘Folder’) will skip all folders that would match the query otherwise.

      Maybe you can run this query, followed by one more query which only seeks out FOLDERS with SI_ANCESTOR=18.

      Export this in a spreadsheet and pivot or match folder IDs to folder names.

      -Sid

      (0) 
  25. Angie Chuah

    Hi Mani,

    I would like to know how to retrieve the Last Accessed Date for each of the webi report via Query Builder?

    And, would it be possible to schedule it and send via email or FTP?

    Thanks in advance!

    Regards,

    AC

    (0) 
    1. Siddhartha Nirgudkar

      QueryBuilder may not be able to show you the last accessed date.

      For this an Audit report would help – by checking the “open” and “modify” events for a given report.

      (0) 
  26. E. Ocula

    Hi , I would like to have a list with all Report-tabs of all (or certain) WEBI-Documents. Is this possible with Query Builder ?

    Second question : is there a kind of DISTINCT keyword usable ? I’m trying something as

    SELECT DISTINCT SI_KIND FROM CI_INFOOBJECTS

    Many thanks

    (0) 
    1. Dell Stinnett-Christy

      You can’t get the report tabs through QueryBuilder.  If you’re on 4.x, you can get them through the RESTful Web Services (see http://help.sap.com/bobip to find the help files for getting to this information).  If you’re on 3.1 or lower, you’ll need to write a program that uses the ReportEngine SDK to get this info.

      No, there is no “Distinct” key word, unfortunately.

      -Dell

      (0) 
  27. Sasi Kumar

    Hi Mani/Dell… I looking for an script to run in QB… please share if you have any for it! Aim is to retrieve the list of groups assigned to an user in CMC. I came across where we can pull the list of user in a group but not what a user has. please do share if you have any on this. thanks for your help in advance

    (0) 
  28. Edward Sujecki

    Hi Mani/Dell/…,

    I have a question about SI_ data names in the CI_INFOOBJECTS table. In trying to upgrade from BusinessObjects Enterprise XI v3.0 to v3.1, we encountered an issue where we found an SI_ data name no longer existed in the upgraded version of the CI_INFOOBJECTS table. Specifically, using QB we were able to verify that the SI_LOCAL_FILEPATH data name no longer existed in the upgraded CI_INFOOBJECTS table of v3.1. Can you confirm if that data name was dropped from the CI_INFOOBJECTS table for that upgrade? Thanks…

    (0) 
    1. Dell Stinnett-Christy

      It’s been a long time since I’ve worked in 3.x .  However, I don’t see the SI_LOCAL_FILEPATH property at all in the 3.1 demo system we have here in the office.  What information are you trying to get from it?

      -Dell

      (0) 
      1. Edward Sujecki

        Thanks for the reply Dell,

        We were trying to upgrade from 3.0 to 2013. Once we bought 2013, we found out that you could only upgrade to 2013 from 3.1. So we first had to upgrade to 3.1 from 3.0. In doing that (still do not know the proper way to do that???) we found that an older program we used to launch the reports on the Web, no longer worked. That C# launcher program returned the report ID, and used the following code “infoObjects = infoStore.Ouery(Select SI_ID From CI_INFOOBJECTS Where SI_LOCAL_FILEPATH = ‘reportPathAndName’ And SI_INSTANCE=0”. The reportPathAndName parameter was passed in thru a querystring parameter.

        So the reports were not coming up in the launcher on the Web after we installed 3.1 on another server, and imported the 3.0 data using a BIAR file of 3.0. We have support for the 2013 product, but have kind of been left out in the cold on this 3.0 to 3.1 upgrade. In our investigation of why things weren’t working, we compared the CI_INFOOBJECTS table of 3.0 to 3.1 using QB, and found the data name missing from the table. I was just looking for confirmation that that data name was removed from v3.0 to v3.1, because we’re still not sure we did the upgrade/import of the 3.0 data files correctly. Any insight on the way we did the upgrade, or the way we should have done the upgrade would be most helpful.

        (0) 
          1. Edward Sujecki

            Ok Dell,

            Can you tell me which category to open a new discussion in please? I just spent over an hour crafting a question about my previous problem into a new discussion, only to have it deleted by a moderator for me not putting it into the correct space! 😯 Really?

            (0) 
    2. Manikandan Elumalai Post author

      Please note we dont have a physical table called CI_INFOOBJECTS which is virtual in nature.

      In my experience, I never seen property called SI_LOCAL_FILEPATH. Please create a separate thread with complete detail of issue with related screen so that we cn investigate further.


      Thanks

      Mani

      (0) 
  29. Scott Gilsdorf

    Folks is there a location/document that shows all of the fields, tables and definitions? Example of one of my questions is: what is SI_LAST_ACCESS_TS? Is this the last time that a particular report was accessed? What is the TS at the end refer to?

    (0) 
    1. Dell Stinnett-Christy

      All of the fields that end with “TS” are TimeStamp fields.

      As Kashif said, there is no reliable documentation beyond this series by Manikandan.  I do a LOT of SDK work so I’m running different types of queries depending on what code I’m trying to write.  I usually run a QueryBuilder queries to determine the exact “fields” that I need for my code.  I also wrote a program that pulls out a list of what object SI_KIND values are in which table.  The list below is not exhaustively complete, but it should give you a start for knowing which table to query:

      CI_INFOOBJECTS

      AAD.AnalysisApplication

      Agnostic

      Category

      CrystalReport

      DataDiscovery

      DFS.Parameters

      Excel

      FavoritesFolder

      Folder

      FullClient

      Hyperlink

      Inbox

      LCMJob

      LicenseRestriction

      MDAnalysis

      MON.Probe

      NotificationSchedule

      ObjectPackage

      Pdf

      PersonalCategory

      PlatformSearchScheduling

      Publication

      Rtf

      ScopeBatch

      Txt

      VISILums

      Webi

      Word

      XL.XcelsiusEnterprise

      CI_APPOBJECTS

      AAD.AnalysisApplication_Bookmark

      AAD.Plugin

      AAD.SDKExtension

      AdminTool

      AlertingApp

      AppFoundation

      BEX.BExWeb

      BICommentaryApplication

      BIonBI

      BIP.CafApplication

      BIWidgets

      busobjReporter

      CCIS.DataConnection

      ClientAction

      ClientActionSet

      ClientActionUsage

      CMC

      CommonConnection

      CRConfig

      DataSearchUniverseDataAccessProvider

      Designer

      DFS.ConnectorConfiguration

      Discussions

      DSL.MetaDataFile

      Folder

      HANAAuthentication

      InformationControlCenter

      InformationDesigner

      InfoView

      LCM

      LCMOverride

      LCMScanHistory

      LCMSettings

      LumiraApp

      MetaData.BusinessElement

      MetaData.BusinessField

      MetaData.BusinessFilter

      MetaData.BusinessView

      MetaData.DataConnection

      MetaData.DataDBField

      MetaData.DataFoundation

      MetaData.DataProcedure

      MetaData.DataTable

      MetaData.MetaDataRepositoryInfo

      MOB_Mobile

      MobileOfflineDocuments

      MON.ManagedEntityStatus

      MON.MBeanConfig

      MON.MonAppDataStore

      MON.Monitoring

      MultitenancyManager

      OpenDocument

      OverrideEntry

      Pioneer

      PlatformSearchApplication

      PlatformSearchApplicationStatus

      PlatformSearchContentExtractor

      PlatformSearchContentStore

      PlatformSearchIndexEngine

      PlatformSearchQueue

      PlatformSearchSearchAgent

      PlatformSearchServiceSession

      PolestarApp

      QaaWS

      RecycleBinApplication

      ReportConvTool

      RepositoryPromptGroup

      RestWebService

      StreamWorkIntegration

      TransMgr

      Universe

      UpgradeManagementTool

      VisualDiff

      VisualDiffApp

      VMS

      WebIntelligence

      WebService

      XL.XcelsiusApplication

      CI_SYSTEMOBJECTS

      AlertNotification

      AuditEventInfo

      AuditEventInfo2

      Calendar

      Connection

      CryptographicKey

      CustomMappedAttribute

      CustomRole

      DependencyRule

      DeploymentFile

      EnterpriseNode

      Event

      Folder

      HotBackup

      Install

      LicenseKey

      LogicalGroup

      MetricDescriptions

      MON.Subscription

      RemoteCluster

      Server

      ServerGroup

      Service

      ServiceCategory

      ServiceContainer

      User

      UserGroup

      -Dell

      (0) 
      1. Edward Sujecki

        Hi Dell,

        Thanks for that listing of the values, it will help I’m sure. Do you know where I can find a download of the SDK for CR2008 V1? I believe you said in another post that there were differences in the SDKs for V0 and V1.

        Thanks,

        Bill

        (0) 
  30. Ramanaidu Kolasani

    Hi Manikandan,

    Actually we have a script which is used to get the list of user for particular roles

    But now there is no data

    I would like to share you the script..plz check

    SELECT TOP 1000000 SI_NAME from ci_systemobjects where children(“si_name = ‘usergroup-user'”, “si_name = ‘xxxx Viewers'”)

    Kindly let me know Is this script fine or not ?

    (0) 
  31. Dineshkumar R

    My Requirement:

    I want to fetch the details of reports( Report name and Path) that was build using the column name”Rate_Set”

    Can anyone help to provide me a sample query for this? Its quite urgent.

    (0) 
  32. Megha Sen

    Hi,
    I want to filter the output using SI_MAIL_ADDRESS:

    SELECT top 100 SI_SCHEDULEINFO.SI_DESTINATION
    FROM CI_INFOOBJECTS WHERE si_recurring = 1
    and SI_id  in (somelist)

    is there anyway to achieve this?

    (0) 
    1. Manikandan Elumalai Post author

      Right now there is no direct way to  achieve what you are looking for. However you can get the required information easily by using the utility below http://www.forumtopics.com/busobj/viewtopic.php?t=175779

      Thanks
      Mani

      (0) 
  33. Arpan Gupta

    Hi Mani,

     

    I am using the auditing universe  for designing a webi report having UserID and User Group detail for each User.

    1. The number of Users, output by universe is far less than the users shown by query builder, Why ? Could you please throw some light on this.
    2. If I want to have a extra column, User last logon time in the above report, Is there any possibility to do it by using your universe.

    Regards,

    Arpan

     

    (0) 
  34. Prakash Tilhoo

    hi,

    We have crystal reports published on BO 4.2.

    Will the query builder allow me to list all docs having string ‘2016’ in parameters :

    If yes, how ?

    Thank you.

     

    (0) 
  35. William Ayd

    In case anyone out there is struggling with reporting off of the AdminTools data, I’ve created a Python package that you can use to convert this information into a DataFrame (tabular format), enabling SQL-like joins and output to excel. Feel free to clone from the GitHub page below:

    https://github.com/WillAyd/BOATParser

    (0) 

Leave a Reply