Running Argos Reports from Banner Job Submission San Mateo County Community College District Ted Nguyen – Database Admin

1. Running Argos Reports from Banner Job Submission San Mateo County Community College District Ted Nguyen – Database Administrator Edgar Coronel – Associate Director - ITS

2. Agenda • Introduction • Objectives • Banner Modifications and One-time Configuration • Argos Report Setup • Banner Report Setup • Report Execution Demo • Argos Design Considerations • Questions

3. Introduction • San Mateo County Community College District Consists of there colleges: • College of San Mateo • Cañada College • Skyline College • Located in the San Francisco Bay Area • Enrolls over 25,000 Students • Banner users since 1993 • Argos users since 2007 • API Interface Released in Version 3.1

4. Objectives • Replace Oracle Reports • Replace SDA (Simplified Data Access) • Minimize changes to users and having to train them on a new application • Minimize security maintenance in the MAPS server • Provide users with multiple output format options

5. Banner Modifications and One Time configuration

6. Banner Setup • Create a local table GTZARGS to store Argos Report Unique Identifier ------------------------------------------- -- GTZARGS -- -- Create a table to store Banner job and -- Argos Report Unique Identifier ------------------------------------------- CONNECT BANINST1/&baninst1_passw DROP PUBLIC SYNONYM GTZARGS; DROP TABLE BANINST1.GTZARGS; Insert into gtzargs (gtzargs_name,gtzargs_report_id) CREATE TABLE BANINST1.GTZARGS values ('FARABAL','II2F275LJ2EKLUVMIOYR4DWXYUSBCAKHI7WBG44XI65AG2M4ZEUXWYDJ3N572TQAJICOIKA4TGKUM') ( GTZARGS_NAME VARCHAR2(30) NOT NULL, GTZARGS_REPORT_ID VARCHAR2(200) NOT NULL, GTZARGS_ACTIVITY_DATE DATE ); COMMENT ON TABLE GTZARGS IS 'Used for Argos Reports'; GRANT SELECT ON BANINST1.GTZARGS TO BAN_DEFAULT_M; GRANT INSERT ON BANINST1.GTZARGS TO BAN_DEFAULT_M; GRANT UPDATE ON BANINST1.GTZARGS TO BAN_DEFAULT_M; GRANT DELETE ON BANINST1.GTZARGS TO BAN_DEFAULT_M; CREATE PUBLIC SYNONYM GTZARGS FOR BANINST1.GTZARGS;

7. Modify Form GUAUPRF GUAUPRF.fmb to added the MAPS Server Data Blocks > GURUPRF_DIR > Triggers > POST-QUERY ELSIF :GURUPRF_DIR.GURUPRF_GROUP = 'ARGSRPT' THEN :GURUPRF_DIR.DEFLT_KEY := 'Enter the name of your Argos Reports Server.' ; Enter Argos URL in GUAUPRF Form as Baseline user

8. New Function to Get MAPS URL

9. Modify Form GUAUPRF ALTERNATIVE • FYI! • If your institution has never modified this form, then I would suggest adding a column to the GTZARGS table to store the MAPS server URL. This would eliminate modification of the GUAUPRF form and the I$_GET_UPRF_ARGSVALUE function. • Example: • The last two slides show how to apply the changes. GTZARGS_MAPS_SERVER

10. Modify Form GJAJOBS Add the “A” for Argos Report Type to GJAJOBS.fmb Modify gjajobs.shl and add the “A” for Argos report type to run job submission process Data Blocks > GJAJOBS > Items > GJBJOBS_JOBS_TYPE_IND > Functional > Elements in List In "List Elements" Enter "Argos Report“ In "List Item Value" Enter "A“ > Help > Hint Enter "Argos Report"

11. Modify Form GJAPCTL Insert the default values for parameter 91 Data Blocks > KEY_BLOCK > Items KEY_BLOCK > Items > KEYBLCK_JOB > Triggers > WHEN-VALIDATE-ITEM

12. Modify Form GJAPCTL Cont. Insert the default values for parameter 91 Cont. Data Blocks > DUMMY_BLK Program Units > PUT_ARGS_REPORTS_PARMS > Triggers > PRE-INSERT Data Blocks > GJBPRUN > Triggers > GET_ARGOS_ DEFINITIONS_OR_STUB Data Blocks > GJBPRUN > Triggers > POST-QUERY

13. Modify Form GJAPCTL Cont. Check if parameter 91 is defined on GJAPDEF Data Blocks > GJBPRUN > Items > GJBPRUN_NUMBER > Triggers > WHEN-VALIDATE-ITEM ELSIF :HOLD_JOB_TYPE_IND = 'A' AND :HOLD_ARGS_REPORT_TYPE_IND = 'L' AND :GJBPRUN.GJBPRUN_NUMBER = '91' THEN EXECUTE_TRIGGER( 'GET_ARGOS_DEFINITIONS_OR_STUB' ); G$_CHECK_FAILURE ; EXECUTE_TRIGGER( 'DISPLAY_PARM_MSG' ); G$_CHECK_FAILURE;

14. Form GJAPCTL Example

15. Form GJAPCTL Example Cont.

16. Modify Form GJAPCTL Cont. Data Blocks > SUBMIT_BLK > Triggers > KEY_COMMIT IF :HOLD_JOB_TYPE_IND = 'A' THEN RUN_ARGOS_REPORTS(RUN_REP_RETURN); IF NOT RUN_REP_RETURN THEN RAISE FORM_TRIGGER_FAILURE; END IF; GOTO DO_CLRFRM; END IF; When user submits the job, it executes the RUN_ARGOS_REPORTS procedure.

17. New Procedure to RUN_ARGOS_REPORTS

18. New Function I$_GET_UPRF_ARGSVALUE • This function returns the RUI

19. New Procedure I$_RUN_ARGOS_REPORT_ONLINE • This Procedure Creates the API • API – This is the URL being sent to the MAPS server: http://argos2:8080/mrr?report=IRZQFO556RPHCYGROQOAVZK6TNH3ZKDO3PVN74DOVH75IIR4E7OBHHEP3S6WWANUM26TZPRCQSJQS&reportformat=PDF&filename=FYRTEST_847874.PDF&PARM01=08 • Report Output – This is the URL returned from the MAPS server: http://argos2:8080/ReportFiles/RSHXnpxTMAgGIoq/FYRTEST_847874.PDF

20. Form GUAUPRF ALTERNATIVE • Remove the I$_GET_UPRF_ARGSVALUE function • Change GTZARGS_C cursor to include the MAPS server value. • SELECT GTZARGS_MAPS_SERVER || GTZARGS_REPORT_ID

21. Form GUAUPRF ALTERNATIVE • This Procedure Creates the API 3. Remove p_report_server from this assignment. It is already included in the I$_GET_ARGOS_APIVALUE function .

22. Argos report – api setup

23. ARGOS API • Detail Instructions in Argos Version 3.1 Release Guide • Develop Argos Report as normal (with a few design considerations to be discussed later)

24. Argos Report Setup • Check the “Make this Report Accessible to API” in the API tab

26. Report Setup • Check the “Make this Report Accessible to API” in the API tab • Note the Report Unique Identifier – it will be used later in the Job Submission Setup

28. Report Setup • Check the “Make this Report Accessible to API” in the API tab • Note the Report Unique Identifier – it will be used later in the Job Submission setup • Enter the User Name that Banner will use to connect to Argos (will discuss security later)

30. Report Setup • Check the “Make this Report Accessible to API” in the API tab • Note the Report Unique Identifier – it will be used later in the Job Submission setup • Enter the User Name that Banner will use to connect to Argos (will discuss security later) • Note the Variables Names as Parm01, Parm02, etc. (will discuss later)

32. Argos Security • Created “generic” User Name • “Generic” User Name belongs to a Group that connects to the database with a “generic” Id • The API can pass Banner User Id but we chose not to use it at this point

33. Banner job submission setup

34. Report Setup • Insert the Banner Report Name and the RUI into the GTZARGS table Insert into gtzargs (gtzargs_name,gtzargs_report_id) values ('FARABAL','II2F275LJ2EKLUVMIOYR4DWXYUSBCAKHI7WBG44XI65AG2M4ZEUXWYDJ3N572TQAJICOIKA4TGKUM')

35. Report Setup • Create job in GJAJOBS

36. Report Setup • Add Object and Authorize Users in GSASECR

37. Report Setup • Create Parameters in GJAPDEF • The variables created in the ARGOS report must match the parameters defined here

38. Report execution demo

39. Execute Report

40. Report Output

41. Argos Design Considerations • Variables must match Parms • If you are developing a report that will run only through Job Submission, you may not need to develop a quick view or elaborate Form Interface

42. Questions? • Contact Information • Ted Nguyen – nguyent@smccd.edu • Edgar Coronel – coronele@smccd.edu