SAP Business Objects Query Builder

Business Objects Query Builder

Among Business Objects tools there is an application named Query Builder which can be used to
query the Business Objects Meta Data repository in order to collect more information and details about your  Business Objects reporting system.

Its url is:
http://BOserver:port/AdminTools/

How to create a query

Queries are created in a language SQL like. Unlike Sql language there are not a lot of functions supported (like nesting SQL statements, the GROUP BY clause or the DISTINCT function).
Below the possible functions in Query Builder within the different clauses of a query are described:
(SELECT, FROM, WHERE, ORDER BY).

Some examples behind:

A list of the Id and Name of all universes

SELECT SI _ID, SI _NAME FROM CI _APPOBJECTS WHERE SI _KIND = 'UNIVERSE'


Total num ber of Web Intelligence Reports
SELECT COUNT(SI _ID) FROM CI _I NFOOBJECTS WHERE SI _KIND = 'WEBI '

A list of 100 Crystal Reports with all properties
SELECT TOP 100 * FROM CI _I NFOOBJECTS WHERE SI _KIND = 'CRYSTALREPORT'

The total num ber of users in the system
SELECT COUNT(SI _ID) FROM CI _SYSTEMOBJECTS WHERE SI _KIND = 'USER'


Get all the objects from all tables contain 'Costs' somewhere in the name

SELECT SI _ID, SI _NAME, SI _KIND FROM CI _I NFOOBJECTS, CI _APPOBJECTS, CI _SYSTEMOBJECTS WHERE SI _NAME LIKE '%Costs%'

Get a list of objects that contain 'sales' somewhere in the name

SELECT SI _NAME, SI _DESCRI PTI ON FROM CI _I NFOOBJECTS WHERE SI _DESCRI PTION LI KE '%sales%'

Get a list of users that are created after the 1st December 2013

SELECT SI _ID, SI _NAME, SI _CREATI ON_TIME FROM CI _SYSTEMOBJECTS WHERE SI _KIND = 'User' AND SI _CREATION_TIME > = '2013.12.01'


Get all Web Intelligence reports  ascending list

Select SI _ID, SI _NAME FROM CI _I NFOOBJECTS WHERE SI _KIND = 'Webi' ORDER BY SI _NAME ASC, SI _ID DESC

To find all crystal and webi reports – not instances:

Select si_id, si_name from ci_infoobjects where (si_kind = ‘CrystalReport’ or si_kind = ‘Webi’) and si_instance = 0 and si_children = 0

To find all crystal reports – not instances or shortcuts:

select si_id, SI_NAME,   si_owner,  SI_PARENT_FOLDER,  si_children, SI_PROCESSINFO.SI_FILES,  SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_RECORD_FORMULA from CI_INFOOBJECTS where (si_kind = ‘CrystalReport’) and si_instance = 0 and not si_name like ‘Shortcut to%’

To find failed instances:

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME>=2 and SI_SCHEDULEINFO.SI_STARTTIME>=’2012.12.02′

To find successful instances:

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO, SI_SCHEDULEINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME<2 and="" si_scheduleinfo.si_starttime="">=’2012.03.02′

To find successful instances of a particular report after a specific date :

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_STATUSINFO, SI_SCHEDULEINFO from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME<2 and="" si_scheduleinfo.si_starttime="">=’2012.04.29′ and SI_NAME = ‘Your Report.wid‘

To find scheduled reports (not instances) after a specified date, scheduled by a specified user:

select si_id, SI_NAME, si_owner, SI_PARENT_FOLDER, si_children, si_instance, SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_SUBMITTER, SI_SCHEDULEINFO.SI_DESTINATION, SI_SCHEDULEINFO.SI_UISTATUS from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_STARTTIME>=’2012.01.01′ and SI_SCHEDULEINFO.SI_SUBMITTER = ’scott.tiger’ and si_instance=0

To find recurring instances:

select si_id, SI_NAME, si_owner, SI_PARENT_FOLDER, si_children, si_recurring, SI_PROCESSINFO.SI_FILES, SI_PROCESSINFO.SI_LOGON_INFO, SI_PROCESSINFO.SI_RECORD_FORMULA, SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_SUBMITTER, SI_SCHEDULEINFO.SI_DESTINATION, SI_SCHEDULEINFO.SI_UISTATUS from CI_INFOOBJECTS where not si_name like ‘Shortcut to%’ and si_recurring=1 and SI_SCHEDULEINFO.SI_STARTTIME>=’2013.11.01′

From Specific Folder ALL Recurring Reports:

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ‘3711′ and SI_recurring = 1

From Specific Folder All Recurring NOT Paused:

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ‘3711′ and SI_recurring = 1 and SI_SCHEDULEINFO.SI_SCHEDULE_FLAGS = ‘0′

From Specific Folder, All Recurring PAUSED:

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ‘3711′ and SI_recurring = 1 and SI_SCHEDULEINFO.SI_SCHEDULE_FLAGS = ‘1′

To find users with at least one report or folder in their user folder:

SELECT si_name FROM CI_INFOOBJECTS where si_obtype=18 and si_children >0

To find users who have logged in since a specified date or whose userid was created after a specified date, but may not have logged in:

select si_name, SI_CREATION_TIME, si_lastlogontime from ci_systemobjects where si_kind = ‘user’ and (si_lastlogontime > ‘2012.11.01.04.59.59′ or SI_CREATION_TIME > ‘2012.04.01.04.59.59′ )

The FROM clause can be used to specify one or multiple tables that the query needs to run your query on.

The table below shows the options you have for the FROM clause:

CI_INFO OBJECTS
Contains objects that are often used to build the user desktop, such as favorites folders and
reports.

CI_SYSTEMOBJECTS
Contains objects that are often used to build the adm in desktop and internal system objects, such
as servers, connections, users, and user groups.


CI_APPOBJECTS
Contains objects that represent BusinessO bjects Enteprise applications. For exam ple, the
InfoView and Desktop Intelligence objects are stored in this table.


See Also

BusinessObjects Environment Cleanup activity using Query builder

Query Builder:queries for extract reports refreshed after o before a specific date

Business Objects auditing