You can get some info out of Query Builder, here are some notes to help:
BO XI R2 Query Builder Notes
- Queries are limited to returning 1000 objects
- Cannot use subqueries as IN lists
- The order of fields in the SELECT has no effect, it renders the results in it’s own order.
- SI_KIND for CI_INFOOBJECTS includes ‘Webi’, ‘Pdf’, ‘Excel’, ‘Folder’, ‘FullClient’, ‘FavoritesFolder’, ‘Inbox’, ‘PersonalCategory’, ‘Shortcut’, ‘MyInfoView’, ‘AFDashboardPage’
- SI_KIND for CI_APPOBJECTS includes ‘Universe’, ‘Folder’, ‘MetaData.DataConnection’,‘ReportConvTool’, ‘WebIntelligence’, ‘Discussions’, ‘InfoView’, ‘CMC’, ‘busobjReporter’, ‘Designer’, ‘AdHoc’
- SI_KIND for CI_SYSTEMOBJECTS includes ‘User’, ‘UserGroup’,’‘Connection’,secWinAD’, ‘secLDAP’, ‘secWindowsNT’
SAMPLE QUERIES YOU CAN RUN IN QUERY BUILDER
Use the XI Launchpad URL and Query Builder is listed in the lefthand navigation links
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%’
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’
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’
pcgeekus (BOB member since 2003-10-16)