Scheduled Reports Table (Clarity)

[Anon Anon]

From which table could I pull the list of all reports instances which are currently scheduled to run in the Clarity?

 

"cmn_sch_jobs" seems to only have the data for scheduled jobs.

 

Thanks,

Marlon

[Anon Anon]

Scheduled 'reports' are just a type of scheduled 'job' - information is all in the same tables.

 

(I recall a recent thread about those tables that you should be able to find)

[dennis_itdesign]

Reports are stored the same in the database than jobs.

You can join CMN_SCH_JOBS.JOB_DEFINITION_ID on CMN_SCH_JOB_DEFINITIONS.ID

CMN_SCH_JOB_DEFINITIONS.JOB_EXTENDED_TYPE_CODE will then show a matching Extension when it's a report, for example "BO_CRYSTAL" for Crystal Reports.

[navzjoshi00]

Check this -

TIP: ** Bonus Tip** Query: What Jobs/Reports Ran Over the Last n Days?

https://communities.ca.com/message/98899493#98899493

 

NJ

[Deactivated User]

Are after the actual schedules or when the instances were run?

[Robert_Ensinger]

Here's my contribution back to this thread. Business Problem to Solve: I need to ensure users are migrating over to Jaspersoft for their Reports. I need to identify all Business Objects Scheduled Reports and their creators.

 

SELECT
R.FULL_NAME
, J.NAME
, J.STATUS_CODE
, D.EXECUTABLE
, D.JOB_EXTENDED_TYPE_CODE
FROM CMN_SCH_JOBS J
JOIN CMN_SCH_JOB_DEFINITIONS D ON D.ID = J.JOB_DEFINITION_ID
JOIN SRM_RESOURCES R ON R.USER_ID = J.CREATED_BY
WHERE J.STATUS_CODE = 'SCHEDULED'
AND D.JOB_EXTENDED_TYPE_CODE LIKE '%BO%'
ORDER BY R.FULL_NAME

 

[Robert_Ensinger]

The full set we're using...

-- All Scheduled Reports
SELECT
R.FULL_NAME
, J.NAME
, J.STATUS_CODE
, D.EXECUTABLE
, D.JOB_EXTENDED_TYPE_CODE
FROM CMN_SCH_JOBS J
JOIN CMN_SCH_JOB_DEFINITIONS D ON D.ID = J.JOB_DEFINITION_ID
JOIN SRM_RESOURCES R ON R.USER_ID = J.CREATED_BY
WHERE J.STATUS_CODE = 'SCHEDULED'
AND D.JOB_EXTENDED_TYPE_CODE LIKE 'BO%'
ORDER BY R.FULL_NAME

-- All Reports Run in the Last 30 Days
SELECT
R.FULL_NAME
, J.NAME
, JR.START_DATE
, JR.END_DATE
, JR.JOB_HOSTNAME
, D.EXECUTABLE
FROM CMN_SCH_JOB_RUNS JR
JOIN CMN_SCH_JOBS J ON J.ID = JR.JOB_ID
JOIN CMN_SCH_JOB_DEFINITIONS D ON D.ID = J.JOB_DEFINITION_ID
JOIN SRM_RESOURCES R ON R.USER_ID = J.CREATED_BY
WHERE D.JOB_EXTENDED_TYPE_CODE LIKE 'BO%'
AND JR.START_DATE > GETDATE() - 30

-- All Scheduled Crystal Reports - these users need to move to Jaspersoft Nao
SELECT
R.FULL_NAME
, J.NAME
, J.STATUS_CODE
, D.EXECUTABLE
, D.JOB_EXTENDED_TYPE_CODE
FROM CMN_SCH_JOBS J
JOIN CMN_SCH_JOB_DEFINITIONS D ON D.ID = J.JOB_DEFINITION_ID
JOIN SRM_RESOURCES R ON R.USER_ID = J.CREATED_BY
WHERE J.STATUS_CODE = 'SCHEDULED'
AND D.JOB_EXTENDED_TYPE_CODE LIKE 'BO_CRYSTAL'
ORDER BY R.FULL_NAME

-- Crystal Reports run in the last 30 days - these users need to move to Jaspersoft Nao
SELECT
R.FULL_NAME
, J.NAME
, JR.START_DATE
, JR.END_DATE
, JR.JOB_HOSTNAME
, D.EXECUTABLE
FROM CMN_SCH_JOB_RUNS JR
JOIN CMN_SCH_JOBS J ON J.ID = JR.JOB_ID
JOIN CMN_SCH_JOB_DEFINITIONS D ON D.ID = J.JOB_DEFINITION_ID
JOIN SRM_RESOURCES R ON R.USER_ID = J.CREATED_BY
WHERE D.JOB_EXTENDED_TYPE_CODE LIKE 'BO_CRYSTAL'
AND JR.START_DATE > GETDATE() - 30

-- Our Custom Webi Reports OTHER than Project Reports we need to Migrate
SELECT
R.FULL_NAME
, J.NAME
, JR.START_DATE
, JR.END_DATE
, JR.JOB_HOSTNAME
, D.EXECUTABLE
FROM CMN_SCH_JOB_RUNS JR
JOIN CMN_SCH_JOBS J ON J.ID = JR.JOB_ID
JOIN CMN_SCH_JOB_DEFINITIONS D ON D.ID = J.JOB_DEFINITION_ID
JOIN SRM_RESOURCES R ON R.USER_ID = J.CREATED_BY
WHERE D.JOB_EXTENDED_TYPE_CODE LIKE 'BO_WEBI'
AND D.EXECUTABLE NOT LIKE '%Project%'
AND JR.START_DATE > GETDATE() - 30