Currently Being Moderated

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

Use cases

               BusinessObjects Environment assessment using Query builder

               BusinessObjects Environment Cleanup using Query builder

               BusinessObjects Query builder – What's New in BI 4.0

Comments

Actions

Filter Blog

By author:
By date:
By tag: