Skip to Content

Hello Techies,

Some of the Query builder queries to explore the BusinessObjects repository. This will be handy for those who searching for Query Builder Queries.

General Queries

To get BO Repository Information

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4

To get BO File Repository Server Information

SELECT * FROM CI_SYSTEMOBJECTS

WHERE SI_KIND = ‘SERVER’ AND SI_NAME LIKE ‘%FILEREPOSITORY%’

To get the all the public folders (Non System Folders)

SELECT * FROM CI_INFOOBJECTS

WHERE SI_PARENTID=23 AND SI_NAME!=’REPORT CONVERSION TOOL’  AND

SI_NAME!= ‘ADMINISTRATION TOOLS’ AND SI_NAME!= ‘AUDITOR’

WebI Report queries

To list all the WebI reports with prompts

SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS,

SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES,

SI_PROCESSINFO.SI_PROMPTS  FROM CI_INFOOBJECTS

WHERE  SI_KIND = ‘WEBI’ and SI_INSTANCE = 0 and

              SI_PROCESSINFO.SI_HAS_PROMPTS=1

To extract all the report names from specific folder

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]

To get Reports those are spanning multiple universes

SELECT SI_ID, SI_KIND, SI_NAME FROM CI_INFOOBJECTS  WHERE SI_UNIVERSE.SI_TOTAL>1

Scheduled reports queries

To list all the events and corresponding event file location

SELECT SI_ID, SI_NAME, SI_FEATURES FROM CI_SYSTEMOBJECTS WHERE SI_KIND= ‘Event’

To list all Scheduled reports based on event

SELECT SI_NAME, SI_SCHEDULEINFO  FROM CI_INFOOBJECTS

WHERE SI_RUNNABLE_OBJECT = 1 AND SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL > 0

To list reports those are not scheduled

SELECT SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER
FROM CI_INFOOBJECTS
WHERE SI_KIND = ‘WEBI’ AND SI_CHILDREN = 0 AND SI_SCHEDULEINFO.SI_SCHED_NOW = 0

To get the list of all reports scheduled daily excluding Paused

SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,

SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS

FROM CI_INFOOBJECTS

WHERE SI_SCHEDULE_STATUS !=8  AND SI_RECURRING = 1

To get the list of reports scheduled by a particular user

SELECT * FROM CI_INFOOBJECTS

WHERE SI_OWNER = ‘<USER NAME>’ AND SI_RECURRING = 1

Universe queries

To Show count of reports per Universe

SELECT SI_NAME, SI_WEBI FROM CI_APPOBJECTS

WHERE SI_KIND=’Universe’ AND SI_WEBI.SI_TOTAL > 0

To retrieve all Web Intelligence reports connected to a Universe

SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE PARENTS(“SI_NAME=’WEBI-UNIVERSE'”,”SI_NAME =’EFASHION’”)

To Show all universes using a specific connection

SELECT SI_ID, SI_NAME, SI_OWNER FROM CI_APPOBJECTS

WHERE CHILDREN(“SI_NAME=’DATACONNECTION-UNIVERSE’ “, “SI_NAME=’TEST'”)

To list all Webi reports that uses the connection (multiple universes)

SELECT * FROM CI_APPOBJECTS, CI_INFOOBJECTS WHERE PARENTS(“SI_NAME=’WEBI-UNIVERSE'”, “CHILDREN(‘SI_NAME=”DATACONNECTION-UNIVERSE” ‘, ‘SI_NAME=”TEST” ‘)”)  AND SI_KIND=’WEBI’

User/UserGroups queries

To find the number of users in a group

SELECT SI_NAME,SI_GROUP_MEMBERS FROM CI_SYSTEMOBJECTS

WHERE SI_KIND = ‘USERGROUP’ AND SI_NAME=’ADMINISTRATORS’ 

To extract all the users from specific user group

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS
WHERE DESCENDANTS(“SI_NAME=’USERGROUP-USER'”, “SI_NAME=’ADMINISTRATORS'”)

Hope the above queries might be useful to those who start digging in to Business Objects repository.

Query Builder Blog series

Basics

             BusinessObjects Query builder – Basics

               BusinessObjects Query builder – Best practices & Usability

Sample Queries

             BusinessObjects Query builder queries

               BusinessObjects Query builder queries – Part II

               BusinessObjects Query builder queries – Part III

               BusinessObjects Query builder queries – Part IV

               BusinessObjects Query builder – Exploring Visualization Objects

              BusinessObjects Query builder – Exploring Monitoring Objects

              BusinessObjects Query builder – Exploring Lumira & Design studio Objects


Use cases

               BusinessObjects Environment assessment using Query builder

               BusinessObjects Environment Cleanup using Query builder

               BusinessObjects Query builder – What’s New in BI 4.0   

To report this post you need to login first.

227 Comments

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

  1. Harry Shenoy

    Hello Manikandan, I work in Dallas and I thank you for writing this blog post.

    I tried your query, “To extract all the report names from specific folder”. But it is throwing errors. Can you please let me know what might have gone wrong? All I changed is the SI_ID of the folder and it didn’t work. Please suggest me. 

    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]

    Thanks,

    Hari

    (0) 
    1. Joe Peters

      Queries with si_ancestor conditions can run for a long time.  If you haven’t changed the default query timeout limit (of nine minutes), then that’s probably the cause of your errors.

      (0) 
      1. Manikandan Elumalai Post author

        Yes. Always make sure Single & Double quotes involved in the queries as expected by the standard format. I noticed this behavior when you use this in Mac.

         

        Thanks

        Mani

        (0) 
  2. Michael Gledhill

    Is there a BO4 version of these SQL examples ?

    On BO 4, I find that the following SQL runs fine. and includes a section listing Process Info (including the report’s parameter names, etc)

    SELECT *
    FROM CI_INFOOBJECTS 
    WHERE SI_ID = 44582

    … but this SQL (which worked fine with BO 3.x) fails miserably… with a “Not a valid query.” error.

    SELECT SI_PROCESSINFO.SI_WEBI_PROMPTS.*
    FROM CI_INFOOBJECTS 
    WHERE SI_ID = 44582

    How do I get a list of Parameter Names, and their Types  (string or numeric) using SQL ?

    I need to do this, as the BO4 REST service to retrieve this information takes 2-4 minutes to run, even with “lovInfo=false” at the end of the URL:

    http://YOUR_SERVER_NAME:6405/biprws/raylight/v1/documents/11729/parameters?lovInfo=false

    Thanks for your help.

     

     

     

     

     

    (0) 
  3. Serena Gunter

    Hello.

     

    I am trying to find the right SQL to return the SI_User field contents from the SI_Prompts of Crystal Reports.

     

    So something like:

     

    SELECT SI_ID, SI_NAME, SI_PROMPTS.SI_USER FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’

     

    I try the above and it does not return SI_PROMPTS.SI_USER

    (0) 
  4. selva meena

     

    Hi Mani,

     

    Very nice blog and you have explained everything on query builder.

     

    I want to extract the user security information of a folder or an universe to find out the parent level user rights which has rights to access it. Could you please help to find out that info using query builder or cms database.

    Thanks.

    (0) 
  5. Sinan Altiner

    Is it possible to find embedded image properties in webi report with admin query. what i am asking, one of our developer had used “image from address” in Appearance option in format cell. who had been write external web url. we want another reports whether used this url or not.

    (0) 

Leave a Reply