Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member182521
Active Contributor

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   

29 Comments
Labels in this area