Thursday, March 29, 2012

Query builder query



Universe vs report.
select si_id, si_name from ci_infoobjects, ci_appobjects where PARENTS("SI_NAME='Webi-Universe'", "SI_NAME='<universe name>'") and si_kind = 'Webi'
-----------------------------------------------------------
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'

No comments:

Post a Comment