Business Objects Query Builder – Part II

Hi All,

This is the continuation of my previous blog related to Business Objects Query Builder.

We are going to see some of the Query builder queries that I used real time in my experience.

1. To get  all the Web Intelligence documents created by user named ’Mani’

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ and SI_AUTHOR=’Mani’

2. To return  list of Web Intelligence documents that have a name starting with  “Annual” and are scheduled

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ AND SI_NAME LIKE ‘Annual%’ AND SI_RUNNABLE_OBJECT=1

3. To extract  list of Web Intelligence documents that are scheduled in a specified period of time

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’  and SI_RUNNABLE_OBJECT=1 and

SI_NEXTRUNTIME between ‘2010.07.08.09′ and ‘2010.07.08.11′

4. To return all report folders containing a string

SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME LIKE ‘%Service%’ AND SI_KIND=’Folder’

5. To returns all Universe folders containing a string

SELECT * FROM CI_APPOBJECTS WHERE SI_NAME LIKE ‘%Sales%’ AND SI_KIND=’Folder’

6. To see what type of rights you have for your BO software

SELECT SI_NAME from CI_SYSTEMOBJECTS where SI_NAMEDUSER=0 AND SI_KIND=’User’

7. 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 all the 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>=’2011.01.01′

• 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>=’2011.01.01′

• 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>=’2011.01.01′ and SI_NAME = ‘Test.rpt’

• To find scheduled instances for a specific time range

select SI_NAME, SI_SCHEDULEINFO.SI_submitter, SI_SCHEDULEINFO.SI_STARTTIME from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_STARTTIME>=’2011.01.01.16.00.00′ and SI_SCHEDULEINFO.SI_STARTTIME

• To find successfully scheduled reports (not instances) scheduled after a certain date

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,  SI_SCHEDULEINFO.SI_STARTTIME, SI_SCHEDULEINFO.SI_SUBMITTER,  SI_SCHEDULEINFO.SI_DESTINATION, SI_SCHEDULEINFO.SI_UISTATUS from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_OUTCOME = 1 and si_instance = 0 and SI_SCHEDULEINFO.SI_STARTTIME>=’2008.11.01′

• 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>=’2008.11.01′

• 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 > ’2008.11.01.04.59.59′ or SI_CREATION_TIME > ’2009.04.01.04.59.59′ )

• To find reports that have not been scheduled

select SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER from CI_INFOOBJECTS where (si_kind = ‘CrystalReport’ or si_kind = ‘Webi’) and si_instance = 0 and si_children = 0 and SI_SCHEDULEINFO.SI_SCHED_NOW = 0

• To find users are all logged in to Business Objects at a given Point of time

SELECT TOP 1000 * FROM CI_SystemObjects WHERE si_kind = 'Connection' AND si_parent_folder = 41 AND si_authen_method != 'server-token' ORDER BY si_name

• To get list of Crystal reports by data connection from BO Enterprise

SELECT SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND=’MetaData.DataConnection’

• To find universe used by the report

SELECT SI_ID, SI_NAME, SI_WEBI , SI_OWNER
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where PARENTS(“SI_NAME=’Webi-Universe’”,”SI_NAME =’Your Universe Name’”)

• To get all recurring reports from Specific folder

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ’3711′ and

SI_recurring = 1

• To get all recurring reports from Specific folder NOT Paused

SELECT * FROM CI_INFOOBJECTS WHERE si_parent_folder = ’3711′ and

SI_recurring = 1 and SI_SCHEDULEINFO.SI_SCHEDULE_FLAGS = ’0′

• To get all recurring reports 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 get list of users who is logged in to your Business Objects XI at a given Point of time

SELECT TOP 3000 * FROM CI_SystemObjects WHERE si_kind = 'Connection' AND si_parent_folder = 41 AND si_authen_method != 'server-token'

ORDER BY si_name

• To get Get All Webi reports from the repository

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Webi’ And SI_INSTANCE=0

• To get Full Client Reports from the repository

SELECT SI_ID, SI_NAME,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND in( ‘webi’ ,’FullClient’)

1. To get all reports from the repository
   Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Report’ And SI_INSTANCE=0
2. To get all universes from the repository
   Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND =’Universe’
3. To get all Users from the repository
   SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID=’CrystalEnterprise.USER’
4. To get all groups from the repository
   Select * from CI_SYSTEMOBJECTS Where SI_KIND=’UserGroup’
5. To get all folders from the repository
   Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID=’CrystalEnterprise.Folder’
6. To get all categories from the repository
   SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND=’Category’
7. To get all personal categories from the repository
   Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND=’PersonalCategory’

Hope all these could be useful to you when it comes in to handy. In the forthcoming post, I will discuss on Business Objects file repository servers in detail.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-764095/，如需轉載，請註明出處，否則將追究法律責任。