BusinessObjects-Query Builder Examples

Main tables: CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS

Some scenarios:

Which BusinessObjects Active Directory Groups don't have any member:
• Select SI_NAME from CI_SYSTEMOBJECTS WHERE SI_KIND = 'UserGroup' and SI_GROUP_MEMBERS is null

Which Universes use the Connection 'con1'?
• Select * FROM CI_APPOBJECTS WHERE CHILDREN("SI_NAME = 'dataconnection-universe'", "SI_NAME = 'con1'")

List of all Universes
• Select SI_NAME, SI_ID FROM CI_APPOBJECTS WHERE SI_KIND='Universe'

List of all Universe Connections
• Select SI_ID, SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND = 'metadata.dataconnection'

List of Active Connections
• Select * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='Connection'

Which Reports Use my Univ1 Universe
• SELECT si_id, si_name,si_universe,si_cuid
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Univ1'")
and SI_KIND = 'Webi' and si_instance = 0

List of Named Or Concurrent Users
• SELECT si_name FROM CI_SYSTEMOBJECTS where SI_NAMEDUSER=0 AND SI_KIND='User'
SI_NAMEDUSER=0 is for Concurrent users. Use SI_NAMEDUSER=1 for Nameduser

To bring back all folders and objects in the ‘Report Samples’ folder:
• select * from ci_infoobjects, ci_appobjects, ci_systemobjects where DESCENDENTS("si_name='Folder Hierarchy'","si_name='Report Samples' and si_kind='Folder'")

To bring back only direct children (objects and folders) in the ‘Report Samples’ folder:
• select * from ci_infoobjects, ci_appobjects, ci_systemobjects where CHILDREN("si_name='Folder Hierarchy'","si_name='Report Samples' and si_kind='Folder'")

To list reports and documents those are in public folders including Sub folders. (Excluding instances, personal documents and inbox documents):
• SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Txt', 'Excel', 'Webi', 'Analysis', 'Pdf', 'Word', 'Rtf', 'CrystalReport', 'Agnostic') AND SI_RUNNABLE_OBJECT = 0 AND SI_INSTANCE_OBJECT = 0 AND SI_ANCESTOR = 23

Tweet