9.2 Run Time and Effort Connected Query

Purpose:  To execute the run time and effort connected query in ctcLink.

Audience:  Time and Labor Specialists.

You must have all of these local college managed security roles:

  • ZD TL Admin View Time
  • ZD_DS_QUERY_VIEWER
  • ZD_DS_QRY_PAYROLL  
  • ZD_DS_QRY_PAY_GARN_HIGH_SENS

If you need assistance with the above security roles, please contact your local college supervisor or IT Admin to request role access.

Note: This report is only intended to serve the requirement for a certification statement for Time and Effort of faculty and exempt employees where after the fact reporting is not mandated.

Time and Effort Connected Query

Navigation:  NavBar > Navigator > Reporting Tools > BI Publisher > Query Report Scheduler

  1. The Query Report Scheduler search page displays.
  2. Enter an existing Run Control ID or select the Add a New Value tab.
  3. Enter the new Run Control ID.
  4. Select the Add button.
Query Report Scheduler add a new value
  1. The Query Report Scheduler page displays.
  2. Select Data Source Type: Connected Query
  3. Select Report Name: BHC_CO_00002.
  4. The Prompt 1 pagelet displays.
  5. Fill in the following Prompt for Query:
  6. GL Unit
  7. From Pay Period End Date
  8. To Pay Period End Date
  9. ID (this EMPL ID must have Job Data which contains a combo code that is paid from a project/grant in the chartfield).
  10. Select the OK button.
  11. The Prompt 2 pagelet displays.
  12. Repeat the same values in Prompt 2.
  13. Select the OK button.
  14. The Prompt 3 pagelet displays (the connected query is in actuality three queries so the information must be identical to return a valid value).
  15. Repeat the same value in Prompt 3.
  16. Select the OK button.
prompt for query pagelet
  1. The Query Report Scheduler page displays.
  2. Select the Run button.
Query Report Scheduler run
  1. The Process Scheduler Request page displays.
  2. Select the OK button.
  3. The Query Report Scheduler page displays.
  4. Make note of the Process Instance number.
  5. Select the Process Monitor link at the top of the page.
  6. Select the Refresh button until the process PSXPQRYRPT has a Run Status of Success/Posted.
  7. Select the Go Back to Query Report Scheduler link at the bottom of the Process Monitor page.
  8. Select the Report Manager link at the top of the reports page.
  9. In the List tab, select the name of the report (BHC_CO_00002  BHC_CO_00002.rtf) that corresponds to your process instance.
  10. Once inside the report, select the file with .rtf at the end and it will give you the option to open or download depending on your internet browser settings.
  11. The report will display the following fields:
    • Staff Name, Staff Department (Job Data value)
    • Account Number (Finance value)
    • Fund (Finance value)
    • Dept (Job Data value)
    • Department Name (Job Data value)
    • Project ID (Finance value)
    • Activity (Finance value)
    • Activity Description (Finance value)
    • Amount (Payroll value)
    • Budget Effort (Job Data value)
    • Job Nbr (Job Data value)
    • Job Nbr Report Start Date (Payroll Period Begin Date)
    • Job Nbr Report End Date (Payroll Period End Date)
    • Actual Effort (if different)
    • Other Salary Sources
    • Certification Statement
Process Monitor link
Example Faculty/Exempt Staff Effort Certification report

The run time and effort connected query process is now complete.

End of procedure.

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.