Also, another useful list of CMS queries for Query Builder was compiled from member pcgeekus and written to the post:
queries
BO XI R2 Query Builder Notes
- Queries are normally limited to returning 1000 objects
-- To return more than 1,000 records, use the 'Top N' function in the query. For example:
SELECT top 2000 * FROM CI_INFOOBJECTS (If it times out, you'll need to reduce it)
- Query Builder will only return results if you have view access to the objects. For example, you might run a query to find all WebI reports that use a universe and when you run a query to get the report names from the SI_IDs, some are not returned because you do not have view rights on those objects. These may be in User Favorites folders or in public folders you cannot view.
- Cannot use subqueries as IN lists but you can use the PARENTS() function
- The order of fields in the SELECT has no effect, it renders the results in it's own order. I've heard this will be better in XI 3.1 or higher.
- If you ever deleted the universe, the association between Report and Universe may be missing, so when you Query the Universe, you may not see all the WEBI report IDs you expected. One way to solve this issue is using Import Wizard to re-migrate them. Perhaps migrate to/from a Dev system.
- SI_KIND for CI_INFOOBJECTS includes 'Webi', 'Pdf', 'Excel', 'Folder', 'FullClient', 'FavoritesFolder', 'Inbox', 'PersonalCategory', 'Shortcut', 'MyInfoView', 'AFDashboardPage','Program' (Trigger related)
-------------------------------------------------------------------------
select * from CI_infoOBJECTS WHERE SI_ID > 1 AND SI_KIND != 'Folder' AND SI_KIND != 'Inbox' AND SI_KIND != 'FavoritesFolder' AND SI_KIND != 'Webi' AND SI_KIND != 'FullClient' AND SI_KIND != 'PersonalCategory' AND SI_KIND != 'Shortcut' AND SI_KIND != 'Excel' AND SI_KIND != 'Pdf' AND SI_KIND != 'MyInfoView' AND SI_KIND != 'Txt' AND SI_KIND != 'Hyperlink'
--------------------------------------------------------------
- SI_KIND for CI_APPOBJECTS includes 'Universe', 'Folder', 'MetaData.DataConnection','Overload', 'ReportConvTool', 'WebIntelligence', 'Discussions', 'InfoView', 'CMC', 'busobjReporter', 'Designer', 'AdHoc'
- SI_KIND for CI_SYSTEMOBJECTS includes 'User', 'UserGroup','Folder','FavoriteFolder','Inbox','Event','MyInfoView','Hyperlink','AFDashboardPage','Analytic','Server', 'ServerGroup', 'Connection','OLAP.ServerConnection','secEnterprise','secWinAD', 'secLDAP','secWindowsNT', 'secWindowsNT','LicenseKey','Calendar','Category','PersonalCategory','Profile','Pdf','FullClientAddin', 'MetaData.DataTable','WCSAdmin','Rtf','Overload',MetaData.DataCommandTable','MetaData.MetaDataTextObject','MetaData.DataFoundation','RepositoryPromptGroup','Publication','FullClientTemplate','MetaData.BusinessCompositeFilter','MetaData.BusinessElement','FullClientProcAdmin','InfoObjectsFolder','FullClient','MetaData.MetaDataCustomFunction','MetaData.DataProcedure','MetaData.BusinessFilter','StrategyBuilder','Powerpoint','ReportAppServerAdmin','Encyclopedia.Topic','Program','WebIntelligence','Txt','Universe','FullClientCacheAdmin',ePortfolio,MetaData.DataDBField, MetaData.BusinessField,ReportConvTool,ReportProxy,Excel,
The SI_KIND is a string value that identifies the BusinessObjects Enterprise object. Another property to review is the SI_PROGID, both property bags contain the same information. Here is a sample query to return Crystal Reports objects only using the SI_KIND filter:
(e.g. SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND='CRYSTALREPORT')
See http://devlibrary.businessobjects.com/businessobjectsxi/en/en/BOE_SDK/boesdk_dotNet_doc/doc/boesdk_net_doc/html/QueryLanguageReference2.html#1175703
for a list of INFOOBJECTS properties.
Below is a complete list of SI_PROGID & SI_KIND values for BusinessObjects XI Release 2:
ProgID SI_KIND
---------------------------------- -----------------------------
CrystalEnterprise.AuditAdmin AuditAdmin
CrystalEnterprise.CacheServerAdmin CacheServerAdmin
CrystalEnterprise.Calendar Calendar (e.g. SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='Calendar'
CrystalEnterprise.Category Category
CrystalEnterprise.CMSAdmin CMSAdmin
CrystalEnterprise.Connection Connection
CrystalEnterprise.Report CrystalReport
CrystalEnterprise.Diskunmanaged Diskunmanaged
CrystalEnterprise.Event Event
CrystalEnterprise.EventServerAdmin EventServerAdmin
CrystalEnterprise.Excel Excel
CrystalEnterprise.FavoritesFolder FavoritesFolder
CrystalEnterprise.Folder Folder
CrystalEnterprise.FileServerAdmin FileServerAdmin
CrystalEnterprise.Ftp Ftp
CrystalEnterprise.FullClient FullClient (Desktop Intelligence Document)
CrystalEnterprise.Hyperlink Hyperlink
CrystalEnterprise.Inbox Inbox
CrystalEnterprise.JobServerAdmin JobServerAdmin
CrystalEnterprise.LicenseKey LicenseKey
CrystalEnterprise.Managed Managed
CrystalEnterprise.ObjectPackage ObjectPackage
CrystalEnterprise.Overload Overload
CrystalEnterprise.PageServerAdmin PageServerAdmin
CrystalEnterprise.PersonalCategory PersonalCategory
CrystalEnterprise.Pdf Pdf
CrystalEnterprise.Powerpoint Powerpoint
CrystalEnterprise.Profile Profile
CrystalEnterprise.Program Program
CrystalEnterprise.Publication Publication
CrystalEnterprise.ReportAppServerAdmin ReportAppServerAdmin
CrystalEnterprise.Rtf Rtf
CrystalEnterprise.Server Server
CrystalEnterprise.Shortcut Shortcut
CrystalEnterprise.Smtp Smtp
CrystalEnterprise.SSOAdmin SSOAdmin
CrystalEnterprise.Txt Txt
CrystalEnterprise.Universe Universe
CrystalEnterprise.User User
CrystalEnterprise.Usergroup Usergroup
CrystalEnterprise.Webi Webi
CrystalEnterprise.WebiServerAdmin WebiServerAdmin
CrystalEnterprise.Word Word
SAMPLE QUERIES YOU CAN RUN IN QUERY BUILDER
Use the XI Launchpad URL and Query Builder is listed in the lefthand navigation links
--------------------------------------------------------------------------------------
query only reports and documents that are in public folders via Query builder? I don't
want any instances and or personal or 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
--------------------------------------------------------------------------------------
Find all the Webi reports that use a certain universe
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'")
--------------------------------------------------------------------------------------
How many AD users logged in today?
Select * From CI_SYSTEMOBJECTS Where SI_KIND='Connection' And SI_PARENT_FOLDER=41 And SI_AUTHEN_METHOD!='server-token' AND SI_AUTHEN_METHOD='secWinAD'
--------------------------------------------------------------------------------------
What are my AD Mapped Groups?
SELECT SI_NAME, SI_MAPPED_GROUPS, SI_DEFAULT_DOMAIN, SI_AVAIL, SI_SSO_ENABLED FROM CI_SYSTEMOBJECTS WHERE SI_NAME IN ('secWinAD','secEnterprise', 'secLDAP', 'secWindowsNT')
--------------------------------------------------------------------------------------
List of all Groups in BO with Subgroups and parents
select SI_ID,SI_ALIASES,SI_DESCRIPTION,SI_NAME,SI_USERGROUPS,SI_GROUP_MEMBERS from ci_systemobjects where si_kind='UserGroup'
--------------------------------------------------------------------------------------
To find all children and grandchildren of an object (a folder), try this query:
SELECT * FROM CI_INFOOBJECTS
WHERE SI_PROGID='CrystalEnterprise.Webi' AND SI_ANCESTOR = 14897 AND SI_INSTANCE = 0
Replace 14897 with your folder's SI_ID. This query may timeout due to the large amount of
stuff and activity on our production environment. SI_INSTANCE = 0 excludes all history copies if a report is scheduled.
NOTE: The SI_ANCESTOR can only be used in the WHERE clause.
--------------------------------------------------------------------------------------
To look at what is Enabled/Disabled on your system try this query
select * from ci_systemobjects where si_id=4
--------------------------------------------------------------------------------------
To get Dashboard info try this query
select SI_CUID,SI_NAME,SI_GUID, SI_DASHBOARD_LEVEL, SI_DASHBOARD_LINKTYP, SI_DATA,SI_ID,
SI_CHILDREN, SI_CORPORATE_CATEGORIES
from CI_INFOOBJECTS
where SI_KIND='AFDashboardPage' and SI_NAME like '%DPS%'
------------------------------------------------------------------------
To get Scheduling info for ALL reports, try
Select SI_SCHEDULEINFO From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Report'
Note: It may timeout, so limit your result by adding more conditions like this:
Select SI_NAME, SI_SCHEDULEINFO From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Report' AND SI_NAME like 'CFD%'
Use SI_NEXTRUNTIME to select the names of all reports that will be run on some date at some time. The >, <, >=, <= and != operators can also be used. Additionally VBScript includes the Now() function, which returns the current date and time, and can be reformatted to extract all reports run today.
The format of the date in the query string must be yyyy.mm.dd.hh.mm.ss, where you can omit anything starting from the right, and the separator can be any separator character. The time part must be in UTC time (GMT with no daylight savings) and it must use the 24 hour clock. For more information
SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO FROM CI_INFOOBJECTS WHERE SI_NEXTRUNTIME > '2007.08.23' AND SI_NEXTRUNTIME < '2007.08.24'
TRY THIS ONE:
SELECT * FROM CI_INFOOBJECTS WHERE SI_NEXTRUNTIME > '2007.10.04' AND SI_OWNER LIKE '%cfdbobj%'
MORE NOTES:
The current status of the scheduled job.
SI_SCHEDULE_STATUS is the recommended property to access scheduling information. It combines the values for SI_INSTANCE_OBJECT and SI_RUNNABLE_OBJECT into a status value.
SI_SCHEDULE_STATUS SI_INSTANCE_OBJECT SI_RUNNABLE_OBJECT
------------------|------------------|------------------
No schedule status| false | false
pending | false | true
running | false | true
success/failure | true | false
------------------------------------------------------------------------
What Users have logged on to which system today and how many times? Keep in
mind that logins are cummulative for a day and the numbers do not means
the totals for right now.
SELECT SI_NAME,SI_KIND,SI_LAST_ACCESS,SI_CREATION_TIME,SI_LASTLOGONTIME,SI_APSNAME
FROM CI_SYSTEMOBJECTS
WHERE SI_LASTLOGONTIME > '2007.09.13.00.00.01' AND SI_KIND='Connection'
ORDER BY SI_APSNAME
To just count the total number of unique users who logged on, try this
SELECT count(SI_NAME)
FROM CI_SYSTEMOBJECTS
WHERE SI_LASTLOGONTIME > '2007.09.13.00.00.01' AND SI_KIND='Connection'
To count total number of connection made today
SELECT count(SI_CREATION_TIME)
FROM CI_SYSTEMOBJECTS
WHERE SI_LASTLOGONTIME > '2007.09.13.00.00.01' AND SI_KIND='Connection'
Note: I tried count(*) but it returns a 0. You can use AND SI_AUTHEN_METHOD='secWinAD' to limit results to AD accounts only. *** It is not clear how long these connection records are kept but it looks like only 1 day.
Here is a sample of the numbers I got today:
Total User Connections 408
Total Connections 408 (appears that 1st and 3rd queries have the same count)
Total Unique Users 220.
So average logons per user is about 2 so far.
This query brings back one of the metrics we see on the Settings area in the CMC. SI_APS_LOAD seems to match number of connections on the CMCs.
SELECT SI_SERVER_DESCRIPTOR, SI_SERVER_NAME, SI_KIND, SI_APS_LOAD, SI_DISABLED, SI_ENABLE_PUBLISH_CA, SI_ENABLE_AUDIT, SI_PROGID, SI_NAME, SI_SERVER_ID, SI_OWNER, SI_SERVER_KIND, SI_DESCRIPTION, SI_REGISTER_TO_APS, SI_SERVER_IS_ALIVE, SI_NTSERVICE_NAME, SI_SERVER_REGISTERING, SI_FRIENDLY_NAME FROM CI_SYSTEMOBJECTS
WHERE SI_KIND='Server' and SI_SERVER_KIND = 'APS'
-- This query lists info on all 47 servers, but the SI_APS_LOAD only applies to the CMCs.
SELECT * FROM CI_SYSTEMOBJECTS
WHERE SI_KIND='Server'
------------------------------------------------------------------------
What WebI Reports have Prompts?
select * from ci_infoobjects where si_kind='Webi' and si_id < 50000
or
select * from ci_infoobjects where si_kind='Webi' and si_name like'%jon%'
** Then, look for si_has_prompts=true in the processing info. I couldn't get it to use that field in the where or select clause.
---------------------------------------------------
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.USER'
NOTE:This times out on our system!!! So try this to see what groups a user is in
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.USER' and SI_NAME = 'jf42475'
You can also try
Select SI_NAME , SI_LASTLOGONTIME from CI_SYSTEMOBJECTS Where SI_KIND='User'
but you might want to add a date range or less than clause.
---------------------------------------------------
What Reports Use my Universe?
NOTE: If you delete a universe and then re-import it, this will not list the reports until you re-save them. It's best to restore a Universe from a locally saved file rather than re-migrate from 5.x/6.x.
First run this query to get a list of report IDs
SELECT SI_NAME,SI_DESCRIPTION,SI_E65_COMMENT,SI_SHORTNAME,SI_KIND,SI_UPDATE_TS,SI_ID,SI_E65_ID,SI_OWNER,SI_WEBI,SI_FILES,SI_PARENT_FOLDER,SI_DATACONNECTION,SI_DERIVEDUNIVERSE,SI_COREUNIVERSE
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_NAME LIKE 'CCE%'
-----------------------------------------------------
To get all universe data including a WebI report list
SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_NAME='CCE Mass Channel'
Then, use the list of IDs to get a list of report names that use this Universe
SELECT SI_NAME,SI_DESCRIPTION, SI_ID,SI_AUTHOR,SI_PARENT_FOLDER,SI_UNIVERSE,SI_HAS_PROMPTS
FROM CI_INFOOBJECTS
WHERE SI_ID IN (nnnnn,nnnnn,nnnnn)
-------------------------------------------------------
How do I get a list of all connections matching a substring
SELECT SI_NAME
FROM CI_APPOBJECTS
WHERE SI_KIND='MetaData.DataConnection' AND SI_NAME LIKE '%CFD%'
*Here's one I ran that combines connections and Universes in the same SQL.
SELECT SI_NAME,SI_CONNUNIVERSE,SI_KIND, SI_ID
FROM CI_APPOBJECTS
WHERE SI_KIND='MetaData.DataConnection' AND SI_NAME LIKE '%Pow%' OR SI_NAME LIKE '%497%' OR SI_NAME LIKE '%184%'
OR SI_ID IN (156713,156714,239817,239823,337532,337533,337534,337535,359959,359960,359961,359962,359983,415489,415490,1662556) ORDER BY SI_KIND
There was some notes about dynamic data using
ceRelation_ConnUniverseDataConnection| 35 |Used to query for universes and their related data connections. No example given.
-------------------------------------------------------
To list all WebI reports when objects exceed 1000
SELECT SI_TARGETID,SI_KIND,SI_UPDATE_TS,SI_NAME,SI_ID,SI_SENDABLE,SI_OWNER,SI_FILES,SI_DESCRIPTION,SI_UNIVERSE,SI_PARENT_FOLDER,SI_OWNERID,SI_SUBMITTERID,SI_STARTTIME,SI_ENDTIME,SI_WEBI_PROMPTS,
FROM CI_INFOOBJECTS
WHERE SI_KIND='Webi' and SI_NAME > 'L'
then use <= 'L'
NOTE: To exclude History or scheduled runs, add AND SI_INSTANCE='false'
-----------------------------------------------------------
To list all report folders containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE '%Dealer%' AND SI_KIND='Folder'
For Universe folders, use CI_APPOBJECTS
------------------------------------------------------------
To list all Webi reports containing a string
SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NAME LIKE '%Dealer%' AND SI_KIND='Webi'
--------------------------------------------------------------------------------------------------
SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe'
---------------------------------------------------------------------------------------------------
To get a list of Universes that match a pattern:
SELECT SI_CUID, SI_ID, SI_NAME, SI_DESCRIPTION
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_NAME LIKE 'DX_N%'
----------------------------------------------------------------
To get detailed info on a particular Universe, Note the SI_ID and use this
SELECT *
FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_ID=1030
-----------------------------------------------------------------
To get a list of all Application Objects and what kind they are:
SELECT SI_CUID, SI_ID, SI_NAME, SI_DESCRIPTION, SI_KIND
FROM CI_APPOBJECTS
-------------------------------------------------------
To get a list of Full Client reportS, run this SQL:
SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND='FullClient'
* Leave the Where clause off to see all
------------------------------------------------------------------
To get a list of reports in a particular folder, 1st you need the parent folder ID, then use it to list reports
To find the parent ID, that is the folder ID, run this query and look for your report names
SELECT SI_NAME, SI_KIND, SI_DESCRIPTION, SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi')
For me, the PARENT_ID I was interested in was 383. Now run this query to get a list of Full Client and WebI reports from that folder.
SELECT SI_NAME, SI_KIND, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Webi') AND SI_PARENTID=383
------------------------------------------------------------------
To list User Account Connnection to Active Directory Names, try this
SELECT *
FROM
CI_SYSTEMOBJECTS
WHERE
SI_PROGID ='CrystalEnterprise.Connection'
------------------------------------------------------------------
To retrieve the schedules of reports btw 2003 and 2006.
SELECT * FROM CI_INFOOBJECTS WHERE SI_NEXTRUNTIME BETWEEN '2003' AND '2005'
------------------------------------------------------------------
To determine CUIDs for a location
1. In the BusinessObjects Enterprise Admin Launchpad, under the
ìClient Samplesî area, select Query Builder.
2. Specify the User Name, Password, and Authentication.
Note: Ensure that you log on as an user with administrator rights in
BusinessObjects Enterprise.
3. Enter your SQL statement.
ï You can enter the following SQL statement to find all the CUIDs that
are related to General Business:
SELECT SI_ID, SI_CUID, SI_NAME FROM CI_INFOOBJECTS
WHERE SI_NAME = 'General Business'
For other locations, replace 'General Business' with the relevant
location name.
ï If you want to see all CUIDs on the system, type
SELECT SI_ID, SI_CUID, SI_NAME FROM CI_INFOOBJECTS
4. Click Submit.
---------------------------------------------------------------------------------
How do I find the DocID of a report in Web Intelligence XI or XI Release 2?
Resolution
To find the DocID of a report in WebIntelligence XI or XI Release 2 perform the following steps:
1. Go to Start > Programs > BusinessObjects XI Release 2 > BusinessObjects Enterprise, click either Administration Launchpad, and click Query Builder.
2. In the Query Builder, type the following query to return a list of all Web Intelligence reports with their SI_ID, which is the same as the DocID used in report linking:
Select si_id, si_name from ci_infoobjects where si_kind = 'webi'
====================
NOTE:
DocIDs can change during migration. Please be sure to check the DocID used in reports after migration to be certain the links continue to work.
====================
3. To return the CUID, which can also be used in report linking like the DocID type the following query:
SELECT SI_CUID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND = 'webi'
------------------------------------------------------------------------------------
To see if the InfoObjects for the secWinAD, secLDAP and secWindowsNT plugins are there
SELECT SI_ID FROM CI_SYSTEMOBJECTS WHERE SI_NAME IN (ësecWinADí, ësecLDAPí, ësecWindowsNTí)
--------------------------------------------------------------------------------------
FROM BOB
have compiled a list of queries that will get some basic information out of the CMC. I'm trying to figure out how to run more complex queires to get all the reports that I need.
here is the initial list. feel free to add queries.. Thanks
Get All Webi reports
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Webi' And SI_INSTANCE=0
Get Full Client Reports
SELECT SI_ID, SI_NAME,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND in( 'webi' ,'FullClient')
Get All reports
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Report' And SI_INSTANCE=0
Get All universes
Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND ='Universe'
Get All Users
SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.USER'
Get all groups
Select * from CI_SYSTEMOBJECTS Where SI_KIND='UserGroup'
Get a list of all user SI_IDs in Groups containing "EDM"
SELECT SI_GROUP_MEMBERS FROM CI_SYSTEMOBJECTS
WHERE SI_KIND ='UserGroup' ANd SI_NAME like '%EDM%'
get all folders
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Folder'
get all categories
SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND='Category'
get all personal categories
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND='PersonalCategory'
get all objects created before this date
SELECT SI_ID, SI_NAME, SI_KIND, SI_CREATION_TIME, SI_UPDATE_TS, SI_DESCRIPTION, SI_PARENTID, SI_PARENT_FOLDER from ci_infoobjects
where si_update_TS < '2006/06/29,20:15:33'
get the top 10 records before 30 Apr 07
SELECT top 10 SI_ID, SI_NAME, SI_KIND, SI_CREATION_TIME, SI_UPDATE_TS, SI_DESCRIPTION, SI_PARENTID, SI_PARENT_FOLDER from ci_infoobjects
where SI_UPDATE_TS < '2007/04/30,12:00:00'
"The Date/Time format in queries can include hours, minutes, and seconds, and can be specified in this way:
yyyy.mm.dd.hh.mm.ss
You can omit anything starting from the right; for example, you can omit seconds, or seconds and minutes.
Note: The time must be in the 24 hour clock format.
If you specify hh.mm.ss, they must be in UTC time (GMT with no daylight savings), where the separator can be any separator character, and each separator can be a different character. All of the following scenarios would work:
yyyy/mm/dd/hh/mm/ss
yyyy.mm.dd.hh.mm.ss
yyyy/mm/dd.hh.mm.ss
yyyy/mm/dd,hh:mm:ss
However, to ensure that you are using the correct date/time format, use the ConvertToUTC Method before executing a query. Otherwise, your result set may contain inconsistencies.
Example
SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_UPDATE_TS > '2000.01.11.18:00:00'
SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_UPDATE_TS != '2000.01'"
------------------------------------------------------------------------------------------------------
To get a list of SI_IDs for all of Supply Management's Reports 1 level down (TIMES OUT even with 7)
select SI_NAME, SI_ID, SI_KIND FROM CI_INFOOBJECTS
WHERE SI_PARENT_FOLDER IN (122266,122272,122282,122497,122508,122512,122516)
then with these folder sets
122520,122544,122556,122560,122564,122571,122577
122581,122591,122596,122600,122604,122608,122612
122616,122624,122628,1250281
--------------------------------------------------------------------------------------------------------
FOR MORE INFORMATION
visit http://devlibrary.businessobjects.com/BusinessObjectsXIR2/en/devsuite.htm
go to BusinessObjects Enterprise SDK
go to COM developer Guide and API reference
go to Query Language Reference
(BOB member since 2004-03-17)