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
After series of blogs on Query builder, in this blog I would like to share best practices with the typical usability in Query builder. Here we go


Useful Functions in Query Builder




















Function Desription
COUNT Retrieves the number of distinct values of a property
TOP specifies the maximum number of object to be returned



- Count of Universes



             SELECT COUNT (SI_ID) FROM CI_APPOBJECTS WHERE SI_KIND = 'UNIVERSE'

- Top 10 WebI reports order by their number of Instances

SELECT TOP 10 * FROM CI_INFOOBJECTS WHERE SI_KIND = 'WEBI' ORDER BY SI_CHILDREN DESC


 Useful operators in Query Builder








































Operators
=
!=
<
>
<=
>=
IN
LIKE
BETWEEN
ALL

- Users created between a date ranges

           SELECT SI_ID,SI_NAME,  SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' AND SI_CREATION_TIME  BETWEEN '2013.06.01' AND '2013.06.10'

- To get the list of all WebI and FullClient reports

           SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND  IN ('WEBI' ,’FULLCLIENT’)

- User who is member of more number of groups

           SELECT SI_ID, SI_NAME, SI_USERGROUPS FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'USER' AND SI_USERGROUPS >= ALL SI_USERGROUPS

Working with Date/Time

- Reports that are last updated on 28th May 2013 (Date comparison)

           SELECT SI_ID, SI_NAME, SI_UPDATE_TS FROM CI_INFOOBJECTS WHERE SI_KIND = 'WebI' AND SI_UPDATE_TS = '2013.05.28'

- Users who are created on or after 18th Mar 2013 3PM (Date/Time comparison)

           SELECT SI_ID, SI_NAME,  SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' AND SI_CREATION_TIME >= '2013.03.18.15:00:00'

Frequently used properties of Infoobjects

















































InfoObject property Property description
SI_ID Unique id generated for the Infobject within the environment
SI_NAME Name of the InfoObject
SI_KIND Type of the Infoobject
SI_CUID Cluster Unique Identifier of the InfoObject
SI_GUID Global Unique Identifier of the InfoObject
SI_RUID Unique id of the Infobject within the Object package
SI_PARENTID Parent Objects SI_ID
SI_OWNERID SI_ID of the InfoObject's owner
SI_CREATION_TIME Creation time of InfoObject
SI_UPDATE_TS Last updated time of InfoObject

Here is the query with all the properties listed above in a single query

SELECT SI_ID, SI_KIND, SI_NAME,SI_CUID, SI_GUID, SI_RUID, SI_PARENTID, SI_OWNERID, SI_CREATION_TIME,SI_UPDATE_TS FROM CI_INFOOBJECTS WHERE SI_KIND='WEBI'


 Hope you find this interesting.

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

35 Comments
Labels in this area