Query to get InfoObject(ReportObject) from specific Folder

[Former Member]

Hi,

I am using XI Release 2.I am having one report object in two folders with the same name.

example: I am having one report object named TestReport that exist in folder ALL as well as folder TEMPORARY in CMS.

Following query i am using:

"SELECT TOP 1 * FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'").append(" AND SI_INSTANCE=0 AND SI_NAME='").append(TestReport ).append("'");

but it always returns the report object from folder ALL while i want to have report Object from folder TEMPORARY

Reporrt Path: path://InfoObjects/Root Folder/Temporary/

Can anyone help me out with the query to get the report object from specific folder?

Thanks

Prakhar Paharia

Edited by: prakhar paharia on Mar 10, 2009 3:43 PM

Edited by: prakhar paharia on Mar 10, 2009 3:44 PM

Edited by: prakhar paharia on Mar 10, 2009 4:17 PM

[Former Member · Accepted solution]

Hello Prakhar,

You are querying the the CI_INFOOBJECTS table for crystal reports that have the name TestReport.

If a crystal with name TestReport exists in folder ALL and another crystal with name TestReport exists in folder TEMPORARY , you should get two INFOOBJECTs with your query!

Look at your query :

SELECT TOP 1 means select one entity.

I guess this is the reason for your problem.

bernd

Edited by: BerndB on Mar 12, 2009 8:42 AM

[Former Member · Accepted solution]

Thanks berned,

you are right but my problem was to get the report from other folder.i.e since i gets two infoobject & my query picks the top 1 that means it always give me the report from folder ALL since it comes before TEMPORARY.while i want the report from TEMPORARY.

Now it has been solved by using two queries.

[ted_ueda]

Web Services Consumer SDK or Enterprise SDK?

Sincerely,

Ted Ueda

[Former Member]

Enterprise SDK...

[ted_ueda]

Then with XI Release 2, you'd have to either: (1) query for the folder first, then get the SI_ID for the report SI_PARENTID as Jason states, or (2) query for the report, then for each, retrieve the SI_PARENTID values, and retrieve the folders, and check each as SI_ID for the folder.

Sincerely,

Ted Ueda

[Adam_Stone]

You will want to query for the folder that contains the report to get its ID, and use that in your query. ie, where SI_PARENTID = ID of folder

[Former Member]

Thanks Adam & Ted,

now i am using two queries as you stated.With first query i am getting the folder id & using this folder id in the second query to get the report from that folder.

Edited by: prakhar paharia on Mar 12, 2009 9:03 AM