Help creating Query in Query Builder 1

[YANKRAY]

Using CE10

I am not familiar with creating Queries in Query Builder and need help with the SQL.

I am trying to query the date and time when a report was last run (SI_LAST_RUN_TIME Property).

With this information I would like to bring back the Folder (and or Subfolder where the report was run and the Name of the report.

Can anyone provide the SQL to use in Query Builder?

Thanks,
Ray

 

[Turkbear]

Hi,
The advantage of Query Builder is that it will write the SQL for you when you point and click the parts you want.

If the particular items are not listed, the pick those that are and add,by editing the SQL shown the additional objects you want to see.

Or you can have it return ALL info on a given report and read the ones you need.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[YANKRAY]

Below is the query I created, but Query Builder is saying it is not valid.

SELECT SI_ID, SI_NAME, SI_LAST_RUN_TIME, FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'

Can you correct it?

It works without the SI_LAST_RUN_TIME, part in it.

Thanks,
Ray

 

[IanWaterman]

Remove the comma before from

SELECT SI_ID, SI_NAME, SI_LAST_RUN_TIME FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'

Ian

 

[YANKRAY]

Ian,

Thanks,I am getting results from my query now.

The SI_NAME is giving me Folder names.
I am trying to get report names.

What is the SI_ for the Report Titles?

Thanks,
Ray

 

[YANKRAY]

I think I am getting it now.

I am only getting 1000 reports at a time.
Can that be increased?

Also, how to I Select reports from a specific folder?

An example folder name is "EVMS 2011"

Thanks

 

[IanWaterman]

I am not familiar with database.

Try

SELECT * FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'

This will bring back all fields and you should then be able to decide which fields you want in your query and which field to set as = 'EVMS 2011'

Ian

 

[kray4660]

If you want more that 1000 rows. You have to put in TOP (number of rows) in the select statement to get more rows. After about 5000 rows, you could get a timeout error.

 

[YANKRAY]

Thanks Ian,

I think I have the hang of it now.

Ray