Programmers Report Engine Chris Leuer Lead Software Engineer

Programmer’s Report Engine Chris Leuer – Lead Software Engineer California Community College Solution Center Sun. Gard Higher Education

Scope • How to define a report • How to code a report • Improvements/Questions

Skill set • Banner Report Engine interface • Query language SQL • Programming PL/SQL • XML • Knowledge of MIS data dictionary

Setup • transform XML repository setup modify dbms_xdb. createfolder() in createresource. sql • Security setup (7. 5) Run rept_secr_roles_asgn. sql and rept_secr_class_asgn. sql or use GUASECR BAN_REPT_C • Oracle job setup (7. 5) JOB_QUEUE_PROCESSES=5 JOB_QUEUE_INTERVAL=60 (replaced by _job_queue_interval)

Oracle XML Repository

Extract XML top element parent element <STUDENT_ROW> <First Name>Francois</First Name> <ID>210009406</ID> <Last Name>Pare</Last Name> <Street>18 Westward Way </Street> <ZIP_Code>19382 </ZIP Code> <State> PA </State> </STUDENT_ROW_> <STUDENT> child element Banner report name + row <STUDENT_ROW> <First Name>Bob</First Name> <ID>210009406</ID> <Last Name>Barker</Last Name> <Street>2457 Queens Way </Street> <ZIP_Code>19902 </ZIP Code> <State> NY </State> </STUDENT_ROW> Banner report name </STUDENT>

Transformed XML top element <STUDENT> parent element <STUDENT_ROW> <Name>Francois Pare</Name> new child element <Ident>210009406</Ident> <State>Pennsylvania</State> </STUDENT_ROW> <Name>Bob Barker</Name> <Ident>210009406</Ident> <State>California</State> </STUDENT_ROW> </STUDENT> Banner function element Banner direct element Banner map element

Transform elements Direct - direct element value to element value relationship Function - value derived from user created function Map - maps to value in mapping form GVATRMP

Define a report • • • define report GVARPDF create query GVARSQL define column mapping GVAMAPD define parameters GVAPDEF define final elements GVATREL Define any element maps GVATRMP demo. .

Banner map MIS element SB 11 Education level

Use GVATRMP

GVATREL Function MIS item SB 00

Transform function -- support cursor in gv_mis_global_bp CURSOR Get_SPBPERS_C(p_pidm SPRIDEN_PIDM%TYPE) IS SELECT SPBPERS_SSN FROM SPBPERS WHERE SPBPERS_PIDM = p_pidm;

Function (cont) FUNCTION f_get_identifier(rowofelements IN rept_rowofelements) RETURN VARCHAR 2 IS lv_extract_pidm gvbtrel_element%TYPE : = 'PIDM'; lv_pidm SPRIDEN_PIDM%TYPE; lv_extract_id gvbtrel_element%TYPE : = 'ID'; lv_id SPRIDEN_ID%TYPE; lv_ssn SPBPERS_SSN%TYPE; . .

Function (cont) OPEN Get_SPBPERS_C(lv_pidm); FETCH Get_SPBPERS_C INTO lv_ssn; IF Get_SPBPERS_C%NOTFOUND OR lv_ssn IS NULL OR F_is_all_numeric(lv_ssn) = 'N' OR LENGTH(lv_ssn) <> 9 THEN CLOSE Get_SPBPERS_C; RETURN lv_id; ELSE CLOSE Get_SPBPERS_C; RETURN lv_ssn; END IF; END f_get_identifier;

Function (cont) BEGIN -- find element PIDM lv_pidm : =gv_rept_xml. f_find_element_value_inrow(lv_extract_pidm, rowofelements); lv_id : =gv_rept_xml. f_find_element_value_inrow(lv_extract_id, rowofelements); . .

Banner map/function combo MIS element SB 22 Academic Standing

GVATRMP

Use F_mapvalue() OPEN Get_SHRTTRM_C(lv_pidm, lv_term); FETCH Get_SHRTTRM_C INTO lv_cast_code; . . RETURN gv_rept_xml. F_Map. Value(lv_report, 'SB 22', lv_cast_c ode);

Tips • Always use COPY feature on GVVRPDF • Copy existing CALB packages ex: sv_mis_sx to sz_mis_sx • Add a pidm to WHERE CLAUSE in GVARSQL for when missing data • Run GVPREPG to purge old XML • Run SQL script (calling gv_rept_engine) for full error

Improvements • Function to return seqno • Function to return parameters found in GVBTRPR • Improvements to performance by by-passing XMLDom implementation for large data volumes • Delete capability • Suggestions?

End • Question? • Thanks for attending! Chris Leuer, Sun. Gard chris. leuer@sungardhe. com