Bo Query builder. List all report of a universe

system · June 23, 2010, 9:47am

Hy to everybody.
I use this query to select all the webi reports of an universe

SELECT TOP 10000 si_id, si_name,SI_AUTHOR,SI_LAST_RUN_TIME,SI_PARENTID, SI_ISTANCE from ci_appobjects, ci_infoobjects where PARENTS("SI_NAME='Webi-Universe'", "SI_NAME='NAME_OF_THE_UNIVERSE'")

it works perfectly, but it select all the report, also the istances. I would like to take only the main reports…how i can edit this query to find it?
Thanks in advance

flavio20002 (BOB member since 2010-03-23)

system · June 24, 2010, 6:47am

Remove SI_ISTANCE from the query I think it should work.

souravroy3 (BOB member since 2008-03-26)

system · June 24, 2010, 7:12am

Also you can search for some Excel Macro which you might find in download section of this forum…

aniketp (BOB member since 2007-10-05)

system · June 24, 2010, 7:22am

Yes, but i write always the excel macro myself because they are much more specifics! I will solve in some way and i will post my results

flavio20002 (BOB member since 2010-03-23)

system · June 24, 2010, 11:35am

flavio20002:

Hy to everybody.
I use this query to select all the webi reports of an universe
SELECT TOP 10000 si_id, si_name,SI_AUTHOR,SI_LAST_RUN_TIME,SI_PARENTID, SI_ISTANCE from ci_appobjects, ci_infoobjects where PARENTS("SI_NAME='Webi-Universe'", "SI_NAME='NAME_OF_THE_UNIVERSE'")
it works perfectly, but it select all the report, also the istances. I would like to take only the main reports…how i can edit this query to find it?
Thanks in advance

How about adding;


where SI_INSTANCE = 0

Also, I don’t know if you copy/pasted the above code from your actual code, but you misspelled “ISTANCE”, in case that helps.

Good luck

Atul Chowdhury (BOB member since 2003-07-07)

system · July 29, 2010, 10:43am

Almost exactly what the sort of thing i was looking for. This works great, with the suggestions, for one universe at a time but does anyone have any idea if this can be changed to just give a count of reports against all the universes i have? I’ve got quite a few

Cheers

Lofty

lofty77 (BOB member since 2009-06-09)

system · July 30, 2010, 12:30am

This should work:

SELECT count(si_id) from ci_appobjects, ci_infoobjects where PARENTS("SI_NAME='Webi-Universe'", "SI_NAME is not null")

I’m not in a position to test the above at the moment so if that doesn’t work, this should:

SELECT count(si_id) from ci_appobjects, ci_infoobjects where PARENTS("SI_NAME='Webi-Universe'", "SI_NAME like '%'")

BoB LoblaW (BOB member since 2007-10-23)

system · July 30, 2010, 9:55am

Thanks for that, the first one did seem to work, but i think my original post wasn’t very well written sorry. What i was after is a list of universes with a count of reports against each one. So i can get rid of universes not being used.

Sort of like this thing (but this doesnt work ):

SELECT count(si_id), si_name from ci_appobjects, ci_infoobjects where PARENTS("SI_NAME='Webi-Universe'", "SI_NAME is not null") group by si_name

also what does the PARENTS function do in the code i dont understand that bit?

Cheers
Lofty

lofty77 (BOB member since 2009-06-09)

system · July 30, 2010, 4:18pm

In that case, Query Builder will be of no use to you. “Group by” is not supported and you would need to group by the universe name or id to get a count by universe. The SDK is what you need to essentially implement your own group by. There may be a utility available in the downloads section that performs this functionality.

Edit: See this blog for information about relationship queries.

BoB LoblaW (BOB member since 2007-10-23)

system · August 2, 2010, 9:58am

dang, i suspected as much.
thanks for the link some good stuff on that blog though
Cheers

lofty77 (BOB member since 2009-06-09)

system · August 3, 2010, 11:38am

Hi lofty77 -

The most painless way to achieve what you’re after would be to use CMS/Connect Community Edition. It’s - simply put - a data driver to your CMS / enterprise metastore that allows you to report against its contents from within Web Intelligence.

I’ve attached some screens of three new reports that come packaged with the current version (2.6.3.2) that provide Universe-Report-Connection relationships as well as a Reports Inventory screenshot that fully categorizes your CMS reports catalog by Folder.

One of the most interesting features about it is its ability to section all your data across folders hierarchically - the same as you’d natively see it via the CMC - as sections (or PDF bookmarks).

You can download it (yes, it’s free) from my signature.

Enjoy!

Atul Chowdhury (BOB member since 2003-07-07)

system · September 5, 2012, 3:39pm

Hi All,

I am trying to understand the above query
“SELECT TOP 10000 si_id, si_name,SI_AUTHOR,SI_LAST_RUN_TIME,SI_PARENTID, SI_ISTANCE from ci_appobjects,
ci_infoobjects where PARENTS(“SI_NAME=‘Webi-Universe’”, “SI_NAME=‘NAME_OF_THE_UNIVERSE’”)” on how is it built.
Are we joining thr two tables ci_appobjects and ci_infoobjects here? If yes, on what condition are we joining them?
Also I dont see SI_LAST_RUN_TIME, SI_ISTANCE in the output. I only see SI_PARENTID,si_id, si_name,SI_AUTHOR in the output.
Why are the other two missing? Please help

Lucky002 (BOB member since 2012-08-28)

system · September 5, 2012, 4:02pm

The query builder language is “SQL-ish”; not all of the regular SQL rules apply. When you see two tables in the FROM clause, it just means that both tables will be evaluated in the WHERE clause (think of it more like a union than a join). This particular query is pulling WebI reports by Universe – universes are stored in ci_appobjects and reports are in ci_infoobjects, so both tables are required for the query to function.

To illustrate further, consider this simple query:

SELECT si_name 
 FROM ci_infoobjects,ci_appobjects

This will give you a single list of the names of all reports, folders, universes, and connections.

Regarding why you may not see a field in the output – si_istance should be si_instance. si_last_run_time should show up, but only for reports that are not instances.

joepeters (BOB member since 2002-08-29)

system · September 5, 2012, 5:15pm

Thank you so much for the reply Joe.
And what does TOP 10000 signify? Does it display only 10000 rows if there are more than 10000 in database?
And also is that in the database order? On what basic does it consider top 10000? Is it for the first 10000 reports on the universe?

Lucky002 (BOB member since 2012-08-28)

system · September 5, 2012, 7:17pm

Yes, it limits the result to the first 10,000, ordered by si_id. You can change that with an ORDER BY clause like in real SQL.

By default, CMS queries have an implied “TOP 1000” setting, so it’s common to use a higher number if you expect more than 1000 records.

Joe

joepeters (BOB member since 2002-08-29)

system · September 5, 2012, 10:18pm

Thank you Joe.
I haev another question in Query Builder.
I see that there are two sections in Query builder:

One is for submit Query (I have been using this to query)
Building a Query Statement Step-by-Step (Choose selection criteria, Specify the relationship between the rules and Choose the attributes that are to be returned.)
When do we use the second step? Is it when we are not sure of the SQL syntax in the first step? or is it used along with the first one?

Lucky002 (BOB member since 2012-08-28)

system · September 5, 2012, 10:37pm

That section is used when you are not familiar with the query syntax. It’s pretty useless in my opinion so I would just stick with the top part and type your query directly.

BoB LoblaW (BOB member since 2007-10-23)

system · September 6, 2012, 1:37pm

Thank you Bob.

Lucky002 (BOB member since 2012-08-28)

system · September 6, 2012, 8:39pm

Thank you so much Joe.

Lucky002 (BOB member since 2012-08-28)

system · November 1, 2012, 5:41pm

Thank you very much for the information above. One quick question though. Do we know what could be the max number of records that we can pull form Query Builder using the top xxx cmd?

its_vicky07 (BOB member since 2007-09-02)