Skip to Content
Author's profile photo Manikandan Elumalai

BusinessObjects Query builder queries

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   

Assigned tags

      240 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Harry Shenoy
      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

      Author's profile photo Joe Peters
      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.

      Author's profile photo IT PI SAP BW team Team
      IT PI SAP BW team Team

      it was not working for me as well, but just corrected some formatting like single codes, semi colon, equalto sign, etc and then it worked

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog 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

      Author's profile photo Gayathri Somwanshi
      Gayathri Somwanshi

      Hi Mani,

      I am trying to fetch list of crystal reports from specific folder however I am getting duplicate records for example there are 104 reports but query builder shows 398 records. How to avoid duplicate records ? I am using below query:

      SELECT SI_ID,SI_NAME, LAST_RUN_TIME FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER = 5698

      Thanks,

      Gayatri S

      Author's profile photo Loic DE L'EPREVIER
      Loic DE L'EPREVIER

      Hi,

      Using SI_PARENT_FOLDER returns documents and instances of these documents, that are in specified folder. You should go with SI_PARENTID instead if you are interested only in documents.
      There is no keyword to remove duplicate in CMS query syntax, since by design CMS cannot return an infoobject more than once for a query.

      By the way, it would be better to specify another filter on an indexed column (like SI_NAME, SI_KIND, SI_CREATION_TIME ...) to avoid unnecessary work by CMS.

      Regards,
      Loic

      Author's profile photo Former Member
      Former Member

      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.

       

       

       

       

       

      Author's profile photo Joe Peters
      Joe Peters

      Try just:

      SI_PROCESSINFO.SI_WEBI_PROMPTS

      (leave off the .*)

      Author's profile photo Sinan Altiner
      Sinan Altiner

      Hi,

      is it possible to have webi reports SQL?

       

      Author's profile photo Joe Peters
      Joe Peters

      Are you asking if you can retrieve the SQL of WebI reports?  No, that can't be done with CMS queries.

      Author's profile photo Mohamad Adel
      Mohamad Adel

      I am having the same question,  Can you recommend a tool or a way to get the report's query.

      Currently, we are having 1K report on LaunchPad, and Need a tool that accepts the table's name. and return a list of reports those are using this table in their query

      Author's profile photo Vijesh Chandra
      Vijesh Chandra

      I think it's not possible from CMS query at all.

      You can use the DB system tables like v$sql for oracle.

      Author's profile photo Serena Gunter
      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

      Author's profile photo Nor A Abdul Latif
      Nor A Abdul Latif

      Hi Mani,

      What is the variable to use to select users whose accounts have been disabled (Disabled option checked) ?

       

      Thanks in advance.

       

      Author's profile photo Former Member
      Former Member

       

      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.

      Author's profile photo Joe Peters
      Joe Peters

      Security information is not available via CMS queries.

      Author's profile photo Sinan Altiner
      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.

      Author's profile photo Former Member
      Former Member

      is it possible to query the comments solution table to get the context which was using for each front end tool to apply the comment ?

      Author's profile photo Steve Sutherland
      Steve Sutherland

      Hello All, We have been play around with the Query Builder a bit and noticed an oddity I guess.

      We are finding that when an Administrator logs in and uses the Query Builder mentioned in this post All users are returned as expected.. But when a NON-Administrator runs the same query in Query Builder only the Users that are designated as Administrators and the User that runs the query are returned.

      We have also been toying with this: https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3

      And we are finding the same issue when running a Report using the “data Access driver” that when an Administrator Runs a Report All users are returned as expected, and when a NON-Administrator runs the same Report only the Users that are designated as Administrators and the User runs the report are returned in the report.

      Does anyone have any input on this behavior, as we would like to have Non-Administrators Run some of these Reports.

      Thank you..

       

      Author's profile photo Former Member
      Former Member

      Hi

      how to get the Data security profile name from AdminTools query. I can get the number of Data

      Security profiles,but not able to get the name of the each DATA Security Profile name from the query.

      select * from CI_APPOBJECTS where SI_ID =<<-->>

      Appreciate for your help.

      Warm Regards

      Amar

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai
      Blog Post Author

      Did you checked this

      https://blogs.sap.com/2013/07/26/businessobjects-query-builder-whats-new-in-bi-40/

      Author's profile photo Ninad Gaikwad
      Ninad Gaikwad

       

      Hi,

      Can you suggest a query to get the successful instance count within specific folder and for specific date.

       

      regards,

      Author's profile photo Marcio Vilariño
      Marcio Vilariño

      Hi,

      You can suggest a query to get the group, universes, and folders.

       

      best regards

      Author's profile photo Giridhar Avinash Kumar DARA
      Giridhar Avinash Kumar DARA

      Hi Mani,

       

      Can you let me know in Query designer what is the query to fetch. Which users having access to which reports.

      Thanks in advance.

      Regards,

      Giridhar

      Author's profile photo Don Elicot
      Don Elicot

      Is it possible to query reports utilizing a specific element from a specific universe?

      Author's profile photo Punniyamurthy Sunderam
      Punniyamurthy Sunderam

      Hi Mani,

      Is it possible to list users who have refresh and save the report in Favorite Folders?. your help is greatly appreciated.

       

      Thanks

      Sunder