Banner Data Outside Banner

1. Banner Data Outside Banner David Calow Senior Software Engineer david.calow@ntu.ac.uk

2. What we will cover today - • When we export data from Banner. • When we import data to Banner. • Why we use these methods. • Some detail of how we transfer the data. • What technologies we use. • The bits that didn’t work.

3. What we will cover today - • NTU Banner Reports. • Data Extracts. • Exports to Microsoft Excel. • Imports from Microsoft Excel. • Exports to Microsoft Word. • HTML & Word Exports. • User Defined Letters. • NTU Mailmerge. • Online Payments Data Transfers.

4. NTU Banner Reports • Oracle Reports. • Main method of reporting used across the university. • Covers all areas of our Banner system:- • Admissions. • Accommodation. • Enrolment. • Placements (VOCAP). • Exam Results. • Graduation. • Used for both reports and simple letters. • Approximately 100 Pre-defined Reports. • Run via GJAPCTL.

5. Process Submission Form - GJAPCTL • Parameters match PARM99 values in the report. • Report specific values available - • Lookups from STVxxxx. • Freetext fields. • Amended by NTU. • New validation form for report specific values - • Yes/No. • Include/Exclude etc. • Easy and flexible to use.

6. Data Extracts • Regular or occasional use. • Generally produce a .csv or .txt file. • One form per Banner area.

7. Sample Data Extract Form (SWADEXP) • This one produces a sequence listing for conferment ceremony programme data. • Uses client_text_io to write to client PC / network drive. • File location specified using Oracle’s client_get_file_name.

8. Extracts to Microsoft Excel • Fully integrated with Excel - • Open an Excel session • Write the data to a spreadsheet • Save the data (PC or network) • Make Excel visible to the user • Exports (and imports) use client_ole2. • Simpler text format for less advanced PCs – • Creates a text file (.csv) • Writes the data to the file. • Save the data (PC or network) • User can open file using Excel as before. • Uses client_text_io.

9. Forms Developer Sample • Requires Webutil library attaching and object group referencing. • Creates a block and canvas within the form. • Only since Banner 7 (Internet Native Banner).

10. Excel Extract (SXASLST) • All performed from a button on the form. • Creates spreadsheet for marking results. • Deals with a variable number of Master and Assessment CRNs. • Works through the screen a record at a time.

11. Sample Spreadsheet • Variable number of CRNs. • Shows “N” in row 6 to indicate not to import.

12. Import from Excel • Imports results data back from spreadsheet into Banner. • Only imports required data (column x = “Y”). • Performs validation checks on data prior to going into Banner– • ERROR: Spreadsheet Sheet 1 does not contain information. • ERROR: Spreadsheet does not relate to current modules / terms displayed. • WARNING: MRN not in exported list (Not Imported). • WARNING: MRN 99999 = N (No Import). • ERROR: Invalid Student ID • ERROR: Duplicate Student ID • ERROR: Student Name mismatch • Inactive or Invalid Grade Code • Unable to overwrite value (MRN 999) • Produces a window in the form to display import success or failure.

13. SXASLST Import Errors • Import Error shown.

14. Exports to Microsoft Word • First used when producing Heads Book for degree ceremonies. • Required more flexibility and formatting than Oracle Reports could provide. • Needed to compare current record with previous record for formatting. • Initial version formatted each line as it was sent over to Word – • Memory problems (kept trying to save in background). • Pitifully slow (10 minutes). • Locked up the PC. • New version writes style alongside the text.

15. Exports to Microsoft Word (continued) • Creates a simple text file (.txt) using client_text_io. • Using a Word Template (.dot) applies formatting to the imported text file. • Finally, saves the new formatted document as a .doc file.

16. Sample Text File • 2 Sections per line – • Word style required for line. (FormatHeading2 etc.) • Text to be printed. (subject, student name etc.)

17. Sample Document • Initially groups by subject title. • Splits the list of students into groups of 4 – or after special award.

18. Word Template • Autonew() macro within template imports the text file. • Performs all possible find and replace operations on it • Saves new document. • Considerably quicker • 4 seconds for 379 names.

19. HTML and Word Exports • Required a method of displaying large text items and formatted tables etc. • First came about because of Paging issues - • Oracle reports could split a line of text across pages e.g. • Outputting to an HTML file for Internet Explorer was little better. • We then decided to open the HTML file with Microsoft Word. • Worked with large text fields • Maintained formatting (using HTML tags) • Kept paragraphs on a page successfully. • Performed using standard client_text_io. • Had to create HTML tags (<doctype>, <head> etc.) within text file.

20. User Defined Letters • Implementation of standard Banner letter generation. • Originally used for Confirmation Letter for new students. • Now used elsewhere within Banner – primarily within Placements system (VOCAP). • Uses GUAPARA, GUALETR and GLRVRBL. • Additional NTU functionality to extract data from Banner. • Functionality within report and database package.

21. GURVRBL – Variables Definitions • This is where run-time variables (*YEAR) are defined. • Notice &PIDM and &TERM • Defined by calling report for each student. • Format trigger in report sets these variables. • No practical limit to the number of variables used. • The SELECT, FROM and WHERE are combined to return the value.

22. GUAPARA – Paragraph setup • Next step is to set up each paragraph. • Each text item (text or variable) to go on a separate line. • Notice *YEAR defined previously.

23. GUALETR – Letter Process • Combines paragraphs together to form the letter. • Sequence number for ordering the paragraphs.

24. GURVRBL – Variables Usage • Report contains srw.set_field_char in the format trigger to produce each paragraph. • srw.set_field_char(0, szre027.create_para(:gubletr_para_code)) • Variable values (&PIDM, &TERM) stored in associative array within a database package. • EXECUTE IMMEDIATE sql_command INTO retval.

25. NTU Mailmerge • First used within Placements. • Originally was intended to be a standard Word Mailmerge. • Word could not easily work with lists. • Original (non Banner) system could. • Implement a local in-house mailmerge. • Following requirements defined: • Must be configurable by system users. • Must allow record selection. • Must be able to save selections. • Must produce Word standard letters. • Must be able to handle lists of data.

26. SWALMRG – New NTU Form • Works in 4 stages: • Retrieving and Saving stored selections. • Show selected output records. • Text editing section combined with fields from database. • Output to text document. • Output text document to be processed by Word on user’s PC.

27. SWALMRG – Stage 1 • Retrieving and Storing searches. • Selection Criteria change depending on “Level”. • Filter used for further selection (user defined).

28. SWALMRG – Stage 2 • Sample query run to return the data. • Query dependent upon Level of report. • Records can be deleted from list if not required.

29. SWALMRS – Link and Token Setup • Setup for different levels. • Defines the FROM and WHERE clauses. • List of tokens for different available fields. • Requires some SQL knowledge. • Defines the next stage.

30. SWALMRG – Stage 3 • Letter text entry. • Formatting available: • New Page • Right Justified • Bold • Italics • Database links. • Tables • Database tokens. • Fields • Lists of data (+)

31. SWALMRG – Stage 4 • Letter output. • Creates text file on PC / Network. • Text file then used by Word template. • Population process uses REF Cursor and cursor variableto find field.

32. Letter Output • Variables added during processing. • List of visits.

33. Word Template • Inserts text file • Find and Replace • Saves final document.

34. Online Payments Data Transfers Online Enrolment, Online Accom. Payment Provider (WPM Education) Sports Channel (& Graduation, cashless campus) Immediate Acknowledgement Updates Banner/NTU Systems Overnight Confirmation Staff Channel (Finance) Updates Banner/NTU Systems

35. Online Enrolment • Final enrolment page for student prior to payment.

36. Online Accommodation • Final accommodation page for student prior to payment.

37. Payment Types • 3 types of payment – • Immediate payments - • One-off payment for tuition/accommodation deposits. • Tuition/Accommodation fees. • Payment for Graduation, student gym membership, cashless campus. • Instalments - • For Tuition and Accommodation fees. • Used by students/parents wishing to pay by debit/credit card. • Any debt over £150 • split into 3 pre-defined amounts. • Direct debits - • User for Tuition/Accommodation fees.

38. Immediate Acknowledgement • Every 5 minutes. • Some database updating. • Allow “instant” record of reservation/transaction.

39. How does it happen? • Process run at 5 minute intervals. • Takes data passed by WPM into holding table. • Trigger then acts on this data depending on transaction type • Accommodation • Graduation • This prevents duplicates for rooms or seats. • Acknowledgements are logged at NTU.

40. Overnight Confirmation • Nightly process. • Back up in case of Immediate Acknowledgement errors. • Further payment detail. • Allows interface to Finance.

41. How does it happen? • Process run overnight. • Loads data passed by WPM into holding tables using SQL*Loader. • Database package then processes transaction - • Enrolment (record made of payment setup allowing student to be FE) • Transaction files produced for finance / sports department.

42. Any questions?