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