Reports based on particular file based event

[Former Member]

Hi Experts,

I have a file based event , Eg: test.txt and have associated it to one of the reports.

Now I want to list all the reports which are utilizing the file based event (test.txt)

Could you please help me out with the query builder query ?

Thanks in advance.

Regards,
Diego.

[former_member209323 · Accepted solution]

Hi Diego,

You can refer to KBA note 1200136

Regards,

~Shree

[Former Member · Accepted solution]

Hi Shree,

Could you please post the query builder query from KBA 1200136 as at the moment I do not have access to Service Market Place.

Regards,
Diego

[former_member209323 · Accepted solution]

Log on to the Query Builder and use of one of the following SQL Queries below:

The SQL below will return the name of the report object, the schedule status (9=dependency), SI_SCHEDULEINFO.SI_DEPENDENCIES list the dependency event or events needed for the report to be scheduled:

SELECT SI_NAME, SI_SCHEDULE_STATUS, SI_SCHEDULEINFO.SI_DEPENDENCIES
FROM CI_INFOOBJECTS
WHERE SI_SCHEDULE_STATUS = 9
ORDER BY SI_SCHEDULE_STATUS DESC

From the sample SI_DEPENDENCIES section below, this report object has one dependency and the EVENT dependency of 69448

For example:

SI_DEPENDENCIES        SI_TOTAL                1
               1                                                            69448

3. Use the following SQL which also filters the type of report object with event using the SI_KIND property (i.e. Report object can be Crystal Reports, Web Intelligence, Full Client/Desktop Intelligence)

SELECT SI_NAME, SI_SCHEDULE_STATUS, SI_SCHEDULEINFO.SI_DEPENDENCIES
FROM CI_INFOOBJECTS
WHERE SI_SCHEDULE_STATUS = 9 AND SI_KIND='CRYSTALREPORT'
ORDER BY SI_SCHEDULE_STATUS DESC

4. The following SQL will return all information about the object with the event (i.e. InfoObject, Processing, & Scheduling Info Properties):

SELECT * FROM CI_INFOOBJECTS
WHERE SI_SCHEDULE_STATUS = 9
ORDER BY SI_SCHEDULE_STATUS DESC

5. The following SQL will return all EVENTS in from the SYSTEM table:

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='Event'

6. If you would like to learn more about a specific event, once you find the ID for the event you can perform the following SQL in the Query Builder to find the information needed:

SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=69448

[Former Member · Accepted solution]

Thanks Shreejit,

I will try these queries.

Regards,
Diego

[Former Member]

Incase the above note doesn't open

1200136 - How to identify all objects based on events using the Query Builder

[Former Member]

Hi Siddharth,

Thanks for responding.

Can you please post the query as I dont have access to SMP.

I need a query which will return the data only for those reports which are associated to a particular file based event (test.txt)

Regards,
Diego

[former_member185603]

SELECT SI_NAME, SI_SCHEDULEINFO  FROM CI_INFOOBJECTS
WHERE SI_RUNNABLE_OBJECT = 1 AND SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL >
0

[Former Member]

Hi Jawahar,

The above query is listing all the reports which are either based on file, schedule or custom.

Can we filter it only for a particular File Based Event ?

Regards,
Diego