BusinessObjects Board

Query

Hi All,

Can some one help me with a query to run on the query builder to see how many reports and the list of reports that are scheduled and when the actual report was created?

Thanks…


JohnJustus :us: (BOB member since 2007-06-25)

This tool in BOB’s Downloads will get you most of what you need.

https://bobj-board.org/t/111635


jwhite9 :us: (BOB member since 2006-07-28)

Thanks for the information.
I have a query like this,

SELECT SI_NAME, SI_PARENTID, SI_DESCRIPTION, SI_OWNER,SI_AUTHOR, SI_CREATION_TIME, SI_UPDATE_TS,SI_LAST_RUN_TIME,SI_KIND from CI_INFOOBJECTS a WHERE SI_KIND in ('WEBI','FULLCLIENT') AND SI_INSTANCE=0 AND SI_LAST_RUN_TIME > '2009.01.01.20.00.00'

The only thing now I need is to get the location of each SI_NAME specified in this query.

Thanks,


JohnJustus :us: (BOB member since 2007-06-25)

That would be indicated by si_parentid, in your case it would be a folder. If you query again using si_id = si_parentid you will see a property that gives you the full path of the folder.


jwhite9 :us: (BOB member since 2006-07-28)

Thanks. As you suggests, I added si_id = si_parentid to be below query but returns 0 objects.

SELECT SI_NAME, SI_PARENTID, SI_DESCRIPTION, SI_OWNER,SI_AUTHOR, SI_CREATION_TIME, SI_UPDATE_TS,SI_LAST_RUN_TIME,SI_KIND from CI_INFOOBJECTS a WHERE SI_KIND in ('WEBI','FULLCLIENT') AND SI_INSTANCE=0 AND SI_LAST_RUN_TIME > '2009.01.01.20.00.00' AND si_id = si_parentid 

Thanks.


JohnJustus :us: (BOB member since 2007-06-25)

Sorry, my previous response wasn’t clear. You need to create s second query to get the parent information, you cannot do it in the same query.


jwhite9 :us: (BOB member since 2006-07-28)

No problems.
This query gives multiple SI_Name,SI_ID and SI_parentID. I am expecting just one.

 Select  SI_ID,SI_NAME,SI_parentid From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Folder' and si_parentid='1996'

What would be the issue?


JohnJustus :us: (BOB member since 2007-06-25)

Of course, you are asking for all the objects that have an si_parentid of 1996. If you want to find out about object 1996 then select on si_id=1996.


jwhite9 :us: (BOB member since 2006-07-28)

Awesome, Thanks.


JohnJustus :us: (BOB member since 2007-06-25)

Hi Jwhite,

Is it possible to find how many reports and list of reports that has been added from a particular date? Like reports that has been added > April’01,2010.

Thanks…


JohnJustus :us: (BOB member since 2007-06-25)

Try running a select * for one object of each type (Webi, Deski, Folder, User, etc.) and look at the properties that are available for you to work with, I think you will be able to determine how to do this.

See the SDK documentation for help with dates. This section How do I use the query language to retrieve classes from the CMS repository? in the Java Enterprise SDK has information on querying the CMS.


jwhite9 :us: (BOB member since 2006-07-28)