Programmer’s Report Engine

Chris Leuer – Lead Software EngineerCalifornia Community College Solution Center

SunGard 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 setupmodify 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=5JOB_QUEUE_INTERVAL=60 (replaced by _job_queue_interval)

Oracle XML Repository

Extract XML

<STUDENT> <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_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></STUDENT>

Banner report name + row

top elementparent element

child element

Banner report name

Transformed XML

<STUDENT> <STUDENT_ROW> <Name>Francois Pare</Name> <Ident>210009406</Ident> <State>Pennsylvania</State></STUDENT_ROW>

<STUDENT_ROW><Name>Bob Barker</Name> <Ident>210009406</Ident><State>California</State></STUDENT_ROW></STUDENT>

top elementparent element

new child element

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 elementSB11Education level

Use GVATRMP

GVATREL Function

MIS item SB00

Transform function

-- support cursor in gv_mis_global_bp

CURSOR Get_SPBPERS_C(p_pidm SPRIDEN.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 VARCHAR2

IS

lv_extract_pidm gvbtrel.gvbtrel_element%TYPE := 'PIDM'; lv_pidm SPRIDEN.SPRIDEN_PIDM%TYPE; lv_extract_id gvbtrel.gvbtrel_element%TYPE := 'ID'; lv_id SPRIDEN.SPRIDEN_ID%TYPE; lv_ssn SPBPERS.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 elementSB22 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_MapValue(lv_report,'SB22',lv_cast_code);

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, SunGard

chris.leuer@sungardhe.com