Does anyone know what the syntax is to 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.[/quote]
mcliffordgoo 
(BOB member since 2003-02-13)
This should work but it could take a long time since SI_ANCESTOR is dynamically calculated for each object:
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
You may need to expand the SI_KIND list to get a complete list of the document types you want. Those are all the kinds I could come up with off the top of my head.
BoB LoblaW (BOB member since 2007-10-23)
That helps me as I am looking for Webi reports in public folders.
Where can I find syntax for finding all the Webi reports that use a certain universe?
When I click on the name of a Webi report the Data Summary shows the Data Source which lists the universe for the query. However, I don’t know how to get to the Data Summary with its Data source given a Webi report in Query Builder.
fmcnutt (BOB member since 2008-04-22)
This question has surfaced many times before. You cannot do this (easily) using Query Builder because it does not support subqueries. There may be a utility in BOB’s downloads that will generate the list for you. If not, try a search and I’m sure you’ll come across other posts discussing the topic.
BoB LoblaW (BOB member since 2007-10-23)
Try this, Universe and associated webi docs:-
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'")Mak 1 
(BOB member since 2005-01-06)
Very cool, Mak 1. I’ve never seen that ‘parents()’ function before. May I ask where you discovered that tidbit?
BoB LoblaW (BOB member since 2007-10-23)
I stole it… 
.
One of my colleagues discovered it, I think it may have even been from here…
Mak 1 (BOB member since 2005-01-06)
Didn’t you Best Practice it to avoid reinventing the wheel?
Damocles (BOB member since 2006-10-05)
Now, now, Damocles, you know my only best practice is to have a calendar table… 
.
Mak 1 (BOB member since 2005-01-06)
I ran below query and getting me an error “Plugin does not Exit”. Any idea?
SELECT SI_ID, SI_NAME, SI_WEBI , SI_OWNER
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where PARENTS(“SI_NAME=‘Webi-Universe’”,“SI_NAME =‘Sales’”)
Philip (BOB member since 2006-03-02)
The syntax looks fine, do other query builder queries work for you?
Mak 1 (BOB member since 2005-01-06)
Take care using simple and double quotes! Did you run this query logged as the Administrator?
Indeed.
Strange thing is, when I copy both into Notepad they look the same. However, If you look at the example displayed in BoBs character set, they look as though they could be different.
I did the notepad test, before I originally answered, to check but maybe the use of quotes / single quotes is incorrect in his example… 
.
Mak 1 (BOB member since 2005-01-06)
Hi Mak,
I remove single code from the SQL and it works fine. But not showing any count of object. However there are couple of reports created from this universe. Here is the working SQL:
SELECT SI_ID, SI_NAME, SI_WEBI, SI_OWNER
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where PARENTS(“SI_NAME=Webi-Universe”,“SI_NAME =Sales”)
Any idea what it is not displaying any count?
Philip (BOB member since 2006-03-02)
What are you trying to count?
I have never tried to put a count in one of the Query Builder queries, as this is broken down by individual reports, I’m unsure what it would really be worth… :? .
Mak 1 (BOB member since 2005-01-06)
Actually I am not getting report list associated with this universe. Here is the result what I got:
Number of InfoObject(s) returned: 0
Number of InfoObject(s) found: 0
Philip (BOB member since 2006-03-02)
Run a query for one of the reports that uses the universe but isn’t showing up in the query:
select * from ci_infoobjects where si_id = <id of document>
Look at the SI_UNIVERSE field. If it is blank, then the original query will not associate that document with the universe and the SDK would have to be used.
BoB LoblaW (BOB member since 2007-10-23)
when I hardcode SI_ID and SI_NAME, it will display the report name. But I do not see any universe name associated with this report. under SI_UNIVERSE I saw SI_TOAL.
Philip (BOB member since 2006-03-02)
In that case, you cannot use Query Builder to accomplish what you want to do. You must either open each report by hand and inspect the universes or use the SDK to automate the process.
BoB LoblaW (BOB member since 2007-10-23)
Open each report and know the universe name is a manual process.
Is there any alternative way?.. we need to think…
Philip (BOB member since 2006-03-02)