BusinessObjects Query builder queries - Part III

[former_member182521]

Dear Folks,

This is the continuation of my previous two blogs on Query builder.

http://scn.sap.com/community/bi-platform/blog/2012/10/11/businessobjects-query-builder-queries

http://scn.sap.com/community/bi-platform/blog/2013/02/14/businessobjects-query-builder-queries--part...

Below are some more queries which I came across in past few days.

Publication

To list all the publications and their associated documents

SELECT SI_ID, SI_NAME, SI_PUBLICATION_DOCUMENTS FROM CI_INFOOBJECTS WHERE SI_KIND='PUBLICATION'

To list all the WebI reports and their associated publications

SELECT SI_ID, SI_NAME, SI_PUBLICATIONS FROM CI_INFOOBJECTS WHERE SI_KIND='WEBI' AND SI_PUBLICATIONS.SI_TOTAL > 0

Users

To list User logon details and the license type

SELECT SI_ID, SI_NAME, SI_CREATION_TIME, SI_UPDATE_TS, SI_LASTLOGONTIME, SI_FAILEDLOGONCOUNT, SI_NAMEDUSER FROM   CI_SYSTEMOBJECTS WHERE SI_KIND='USER'

To list all the Users Inbox, Favorites and Personal folders

SELECT SI_ID, SI_NAME, SI_INBOX, SI_FAVORITES_FOLDER, SI_PERSONALCATEGORY

FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'

To list recently accessed documents & received alerts for the User (BI 4.x)

SELECT SI_ID, SI_NAME, SI_RECENT_DOCUMENTS, SI_RECEIVED_ALERTNOTIFICATIONS

FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER'

Shortcut

To list Shortcut along with its Source report

SELECT SI_ID, SI_NAME, SI_SHORTCUT_TARGET FROM CI_INFOOBJECTS WHERE SI_KIND='Shortcut'

Calendar

To list Calendar objects

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND= 'Calendar'

Server & Server groups

To list Server and associated Server groups

SELECT SI_ID, SI_NAME, SI_SERVERGROUPS FROM CI_SYSTEMOBJECTS WHERE SI_KIND='SERVER'

Reports & Schedules

Find all reports schedule with a certain custom calendar

SELECT SI_ID, SI_NAME, SI_KIND, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,SI_NEXTRUNTIME, SI_SCHEDULE_STATUS, SI_STATUSINFO FROM CI_INFOOBJECTS WHERE SI_SCHEDULE_TYPE in (8,9)

Scheduled jobs created by User

SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_RECURRING=1 AND

SI_SCHEDULEINFO.SI_SUBMITTER = '<user>'

Program Objects

To list all the Program Objects

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND='PROGRAM'

Explorer Workspaces

To list all the Explorer workspaces

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’DATADISCOVERY’

Custom Access Levels

To list all the Custom Roles

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='CUSTOMROLE'

Hope you find this useful and interesting. Thanks for reading. Appreciate your valuable comments.

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