1 / 42

Banner Data Outside Banner

Banner Data Outside Banner. David Calow Senior Software Engineer david.calow@ntu.ac.uk. 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.

jam
Download Presentation

Banner Data Outside Banner

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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?

More Related