Banner Reporting

News:

We have posted our Banner DBA postion at http://division.uaex.edu/jobs.htm List of Appointed Positions> Professional.

Argos Sessions will be presented at Evisions Summit 2011 and SunGard HE Summit 2011.

My public and professional life has been dominated by technical projects, most of which have included computer programming or the management of computing projects.

This site joins most of them together:

Tools, References, Methods, and Programmer Help for the Banner Startup is now:
BannerTools is about Oracle SQL*Plus and UNIX Banner Reporting, and mostly contains command line code or scripts written for Oracle 9i/10g most useful for control, reporting, and business intelligence.

BannerArgos is about Evisions' Argos, Ad Hoc Reporting.

BannerAccess is about MS Access Reporting.

BannerAPEX is about Oracle Application Express Notes and Projects.

BannerScripts is a restricted code and presentation repository, password from bknox @t uaex.edu.
(Using IE8? If this link does not prompt you for the password, change your Internet Options to "Allow websites to Prompt for information using scripted windows." Yes, it is using a Javascript for the password checking.)
includes:

Arkansas Sales Tax and Tax Rebates Report
Approval Notices email
Direct Deposit email
PO Scripts
.csv file extract example - (PO Imaging Index Information)
Batch Journal Voucher Scripts
Fund Table Changed Notice
How To Create a new SQL Pass-Through Query for MS Access Reporting
NETC 2005 Making Life Easier for Users
Summit 2006 e~Print - Innovative Ways We Use It - (includes fully automated month end reporting)
ABUG 2006 e~Print - Innovative Ways We Use It - (slightly different than the Summit presentation)
Standard Objects for BannerAccess - (.mdb)
Saved Objects for BannerAccess Class Patches - (.mdb)
Argos related objects in the scripts repository:
maps_users.sql - MAPS Users for creating MAPS Accounts from Banner
access_to_argos.shl - MS Access SQL to Argos Oracle SQL
table_to_argos.sql - Collecting Table and Record Selection Criteria
table_to_v_view.sql - Generate Oracle View Create Script
Table Descriptions for Argos Use.txt - Extension results from table_to_argos.sql
Argos - Moving into the Community - Summit Presentation (ppt)
Argos Tech Track - Argos Pre-Summit (ppt)
Argos Admin and Security Track - Argos Pre-Summit (ppt)
ABUG 2007 Argos - Moving into the Community - (ppt)
Argos Pre-Summit 2008 Presentation - How your Oracle DBA or Programmer Can Improve Your Argos Reporting! (ppt)
Banded Report Standard DataBlock and Report - This is the Bonus Object included in the Pre-Summit 2008 (ppt)
Current Record Set or Special Views - This is the code to create the Oracle Views referenced in the Argos Pre-Summit 2008 (ppt)
TN_Summit_2008_Argos-Moving_into_the_Community.ppt - TN Summit 2008 (ppt)
How Your DBA or Database Programmer Can Improve Your Argos Reporting - TN Summit 2008 (ppt)
Backup MAPS/Argos Server command script - Copy Argos Automated Backup to an Alternate Server
Backup MAPS/Argos Server log example - Backup log file example
Backup MAPS/Argos Server Check Backup - Sends email from Argos with Log File attached
Summit 2009 - Argos - Moving into the Community (ppt)
Argos pre-Summit 2009 - Argos - Moving Rapidly into the Community (ppt)
ABUG 2009 - Moving Rapidly into the Community (ppt)
Argos pre-Summit 2010 and ABUG 2010 - Enabling Functional Users with Argos (pptx)

ePrint is a SunGardHE product that is frequently used by Banner sites. e~Print was the old name; ePrint is the new name. (But, not e-Print.)

ePrint is a secure, web-based, report server. It runs on Linux (Red Hat Enterprise) and is most often completely supported by the SunGardHE ePrint group. You can do the Linux maintenance yourself if you want, but SunGard does a very good job of support for ePrint and support is bundled into the product's annual maintenance.

IMO, ePrint works best for plain text reports which be secured by Fund/Orgn (a.k.a., Banner Security) with individual pages served to the users. The served plain text pages are displayed as PDF or Text. The PDF file display gives the user the ability to print from their PC without the server knowing the Printer being used.

While one can store html, pdf, or many other file types in ePrint, these lose the Fund/Orgn security by page.

We use ftp to send reports from our Banner server to the ePrint server. (Both servers are inside the our FireWall; sftp could be used if needed.) We refresh our "Daily" reports every hour from a cron run script on the Banner server (AIX).

ePrint does not run Oracle, so the usual SunGard reluctance to keep Linux current for Oracle servers is not a factor. We just (spring 2009) replaced our original ePrint server which had run without issues for over five years. The ePrint folks had been urging us to move to RH 5 for months.

The ePrint software migration was done remotely after we locally installed Red Hat 5.3 (about 40 minutes once you have the huge downloaded image from Red Hat).
SunGard requires that we use Red Hat Enterprise Linux for the OS. Be sure to get the Red Hat Enterprise Linux Academic Server Edition which has a very serious discount for education. This version is unsupported by Red Hat, but for ePrint your OS support is already bundled into the ePrint annual maintenance. You download the ico file and burn it to a DVD. ePrint provides step by step instructions for how they want the server to be installed.
The ePrint folks moved the old box' data files, switched our new box's name and IP to the old. Our only problem was with the new Dell box requiring a F1 to continue to boot (strange option for a box sold as a server). It has been running without incident for months.

Banner Reporting makes up a large part of my current job. Reporting today is done in many different ways. While this may still include traditional paper reports or archived report images of them, increasingly, reporting is moving away from these formats.

A report today may be little more than the Subject of an email or a single screen of information. Finally, long after promised, the industry is moving to exception reporting which has started to replace many traditional reports.

“Which Reporting Tools to use with Banner?” is perhaps the most Frequently Asked Question posed on the SunGardHE (a.k.a., SCT) Banner Listserv.

SQL*Plus is our preferred tool for Programmer created reports or processes. SQL*Plus is the command-line interface to Oracle and therefore is available to every Banner site. It provides fast interactive development, the ability to readily format and paginate, and PL/SQL is always available from SQL*Plus if needed.

Other than being required for some Banner provided code, COBOL is most useful for generating reports with complex totals or control break logic. There is nothing wrong with this enduring language other than the fact that it is not free and few new programmers are learning the language. COBOL is compiled and SQL Code run in COBOL is much faster than the same code run in SQL*Plus. All the problems associated with developing in a compiled language are still with COBOL.

For Banner Reporting, C is about the same as COBOL without the extensive formatting that is a built-in part of COBOL. C's greatest advantage is that most new programmers have been introduced to it.

My Oracle programming is still mostly from command line using SQL*Plus. SQL*Plus is powerful, easy to use and to extend. The key to rapid development in any language is a repository of working code.

For example, today I "coded" a rather complex new report in about ten minutes using a report program generator that I wrote once and have used dozens of times to save hundreds of hours of writing the same basic report code again and again. In ten seconds, I had the report structure done. Report headings, footers, pages numbered, primary in-code documentation completed. I grabbed an organization directory table I had previously built to model our management structure, added an In-Line View created for another task giving us our state budgeting job titles from SunGard Higher Education's Banner database and it was all done but making it pretty.

But, the real promise of database systems is giving non-IT end users powerful Ad Hoc reporting. A promise that has been hard to realize until recently.

BannerAccess is a collection of Predefined SQL Pass-Through Queries connecting MS Access to SunGard Higher Education's Banner (Oracle) Database product. We have successfully used BannerAccess for over seven years. Now we are moving to replace the MS Access part of this tool with Evisions' Argos, a web based Ad hoc reporting tool. BannerAccess will continue to be documented on the BannerAccess site since there are a number of people using that site for a reference.

I developed BannerAccess to give a simple to use Ad Hoc reporting tool to our functional end-users of Banner. This tool used pre-defined Queries to make Banner Tables appear less daunting as well as making them easier to combine into more complex reporting objects. BannerAccess uses SQL Pass-Through Queries to make efficient use of the underlying Oracle database in Banner. BannerAccess is easy to use right up to the point of needing real parameters in a Pass-Through Query. At that point one must use Visual Basic to code the parameter logic that would make the Query run efficiently. And, that is its major shortcoming: functional users are very unlikely to ever write this Visual Basic code.

BannerArgos is my newest major programming tools project. Using Argos instead of MS Access will enable BannerArgos to easily surpass BannerAccess in most areas. Creating parameter based Queries in Argos are simple and I believe our functional users will make good use of them. BannerArgos will be documented on the BannerArgos site.

Argos Presentations:
Admin and Security (from Argos pre-Summit 2007 Admin and Security Track)
MS Access Conversion (from Argos pre-Summit 2007 Tech Track)
Moving into the Community (from ABUG 2009)

Extension has moved to Luminis Content Management System and our move has orphaned many of my old URL addresses. The following will provide a crosswalk to the missing files. These include:

http://www.uaex.edu/bknox/BannerTools.htm          --> http://betwinx.com/BannerTools.htm
http://www.uaex.edu/bknox/BannerArgos.htm         --> http://betwinx.com/BannerArgos.htm
http://www.uaex.edu/bknox/BannerArgos_2.5.htm --> http://betwinx.com/BannerArgos_2.5.htm
http://www.uaex.edu/bknox/BannerAccess.htm       --> http://betwinx.com/BannerAccess.htm
http://www.uaex.edu/bknox/BannerAPEX.htm         --> http://betwinx.com/BannerAPEX.htm
http://www.uaex.edu/bknox/Oracle_Dates.htm       --> http://betwinx.com/Oracle_Dates.htm

http://www.uaex.edu/bknox/BannerScripts.htm was my scripts and presentations repository.
The current scripts and presentations repository contains the following files:

http://www.uaex.edu/bknox/taxrpt.sql
http://www.uaex.edu/bknox/fund_changed_notice.sql

http://www.uaex.edu/bknox/update_from_table.sql
http://www.uaex.edu/bknox/banner_approval_notices_email.htm
http://www.uaex.edu/bknox/create_approval_table.sql
http://www.uaex.edu/bknox/create_approval_wk_table.sql
http://www.uaex.edu/bknox/approval_clear.shl
http://www.uaex.edu/bknox/approval_clear.sql
http://www.uaex.edu/bknox/approval_clear.log
http://www.uaex.edu/bknox/approval_gather.shl
http://www.uaex.edu/bknox/approval_gather.sql
http://www.uaex.edu/bknox/approval_periodic.sql
http://www.uaex.edu/bknox/approval_gather2.shl
http://www.uaex.edu/bknox/approval_gather2.sql
http://www.uaex.edu/bknox/approval_periodic2.sql
http://www.uaex.edu/bknox/approval_periodic2_1.sql
http://www.uaex.edu/bknox/approval_periodic2_2.sql
http://www.uaex.edu/bknox/approval_clear_approved.sql
http://www.uaex.edu/bknox/approval_gather.log
http://www.uaex.edu/bknox/approval_listall.sql
http://www.uaex.edu/bknox/approval_errors.log
http://www.uaex.edu/bknox/approval_errors.shl
http://www.uaex.edu/bknox/approval_queue_errors.shl
http://www.uaex.edu/bknox/approval_queue_errors.sql
http://www.uaex.edu/bknox/banner_approval_queue_errors.htm

http://www.uaex.edu/bknox/banner_dd_email.htm
http://www.uaex.edu/bknox/create_dd_audit.sql
http://www.uaex.edu/bknox/dd_audit.sql
http://www.uaex.edu/bknox/dd_email.sql
http://www.uaex.edu/bknox/dd_email_exceptions_print.shl

http://www.uaex.edu/bknox/banner_po_scripts.htm
http://www.uaex.edu/bknox/open_po.sql
http://www.uaex.edu/bknox/po_close.sql
http://www.uaex.edu/bknox/po_open.sql
http://www.uaex.edu/bknox/po_flags.sql
http://www.uaex.edu/bknox/requisition_close.sql

http://www.uaex.edu/bknox/po_imaging.sql
http://www.uaex.edu/bknox/extract_to_csv.sql
http://www.uaex.edu/bknox/oracle_loader.htm

http://www.uaex.edu/bknox/listcol.sql
http://www.uaex.edu/bknox/report_skeleton.sql
http://www.uaex.edu/bknox/report.shl
http://www.uaex.edu/bknox/getprinter.sql
http://www.uaex.edu/bknox/printer_bknox.sql

http://www.uaex.edu/bknox/batch_jv_scripts.htm
http://www.uaex.edu/bknox/bjvfeed_z.shl
http://www.uaex.edu/bknox/bjvfeed_z.sql
http://www.uaex.edu/bknox/create_bjvfinc.sql
http://www.uaex.edu/bknox/bjvfeed_z.ctl
http://www.uaex.edu/bknox/bjvfeed_z.dat
http://www.uaex.edu/bknox/bjv_print.sql
http://www.uaex.edu/bknox/bjv_print_z.lst

http://www.uaex.edu/bknox/cookie_cutter.htm
http://www.uaex.edu/bknox/cookie_cutter.sql
http://www.uaex.edu/bknox/cookie_cutter.shl
http://www.uaex.edu/bknox/cookie_sub_select_code.sql
http://www.uaex.edu/bknox/cookie_new.sql
http://www.uaex.edu/bknox/cookie_new.shl
http://www.uaex.edu/bknox/leave_requests.shl
http://www.uaex.edu/bknox/leave_requests_gather.shl
http://www.uaex.edu/bknox/leave_requests_gather.sql
http://www.uaex.edu/bknox/leave_requests.sql
http://www.uaex.edu/bknox/leave_requests_gather_cron.log

http://www.uaex.edu/bknox/oracle_dates.htm
http://www.uaex.edu/bknox/permissions.htm
http://www.uaex.edu/bknox/Oracle_Notes.htm
http://www.uaex.edu/bknox/Using_CES.email_files.htm
http://www.uaex.edu/bknox/email_with_attachment.htm
http://www.uaex.edu/bknox/formatted_documents_from_oracle_or_unix.htm
http://www.uaex.edu/bknox/Wiki.htm

http://www.uaex.edu/bknox/maps_users.sql
http://www.uaex.edu/bknox/access_to_argos.shl
http://www.uaex.edu/bknox/table_to_argos.sql
http://www.uaex.edu/bknox/table_to_query.sql
http://www.uaex.edu/bknox/table_to_v_view.sql
http://www.uaex.edu/bknox/Table Descriptions for Argos Use.txt
http://www.uaex.edu/bknox/Banner_Current_Record_and_Special_Views.txt
http://www.uaex.edu/bknox/Argos - Moving into the Community.ppt
http://www.uaex.edu/bknox/Argos Tech Track.ppt
http://www.uaex.edu/bknox/Argos Admin and Security Track.ppt
http://www.uaex.edu/bknox/ABUG 2007 Argos - Moving into the Community.ppt
http://www.uaex.edu/bknox/ArgosConfVersion2008_bknox_Session401.ppt
http://www.uaex.edu/bknox/BandedReportStandard20080421.argosexport
http://www.uaex.edu/bknox/TN_Summit_2008_Argos-Moving_into_the_Community.ppt
http://www.uaex.edu/bknox/TN_Summit_2008_DBA_or_Database_Pro.ppt

http://www.uaex.edu/bknox/Summit_2003.htm
http://www.uaex.edu/bknox/NETC 2005 Making Life Easier for Users Presentation.ppt
http://www.uaex.edu/bknox/ABUG 2006 e~Print - Innovative Ways We Use It.ppt
http://www.uaex.edu/bknox/StandardObjectsForBannerAccess.mdb
http://www.uaex.edu/bknox/SavedObjectsForBannerAccess_Class_Patches.mdb
http://www.uaex.edu/bknox/Summit2006Session182.ppt

http://www.uaex.edu/bknox/Session138_Summit2003.ppt
http://www.uaex.edu/bknox/Session303_Summit2003.ppt
http://www.uaex.edu/bknox/Summit_2003_p03.htm
http://www.uaex.edu/bknox/2003_607_MSAccessForBeginners.pps
http://www.uaex.edu/bknox/2003_607_handouts.doc
http://www.uaex.edu/bknox/SavedObjectsForBannerAccess.mdb
http://www.uaex.edu/bknox/SavedObjectsForBannerAccess_for_Summit2002.mdb
http://www.uaex.edu/bknox/applicable_gross.sql
http://www.uaex.edu/bknox/bruce_loving_resources_summit2002.htm
http://www.uaex.edu/bknox/jane_frounfelker's_summit2002_links.htm
http://www.uaex.edu/bknox/dan_debowerT1000.ppt
http://www.uaex.edu/bknox/dan_debower245.ppt
http://www.uaex.edu/bknox/030.ppt
http://www.uaex.edu/bknox/A_Building_Block_Approach.htm
http://www.uaex.edu/bknox/ms_access.htm
http://www.uaex.edu/bknox/ms_access_logon.htm
http://www.uaex.edu/bknox/Oracle8_DSN_and_DBQ.htm
http://www.uaex.edu/bknox/oracle8_odbc_install.htm
http://www.uaex.edu/bknox/ms_access_2000_oracle_long_datatype.htm

http://www.uaex.edu/bknox/Summit_2003.htm
http://www.uaex.edu/bknox/Summit_2003_p02.htm
http://www.uaex.edu/bknox/Summit_2003_p03.htm
http://www.uaex.edu/bknox/Summit_2003_p04.htm
http://www.uaex.edu/bknox/Summit_2003_p05.htm
http://www.uaex.edu/bknox/Summit_2003_p07.htm
http://www.uaex.edu/bknox/Summit_2003_p08.htm
http://www.uaex.edu/bknox/Summit_2003_p09.htm

http://www.uaex.edu/bknox/005_Knox_ArgosMovingQucikly.ppt
http://www.uaex.edu/bknox/0118.ppt

http://www.uaex.edu/bknox/Argos_Designer.htm
http://www.uaex.edu/bknox/Argos_Designer_Training.htm
http://www.uaex.edu/bknox/Argos_Free-Type_Filters_and_Sorts.htm
http://www.uaex.edu/bknox/Argos_Oracle_Applications_User_Setup.htm
http://www.uaex.edu/bknox/Argos_Reporting.htm
http://www.uaex.edu/bknox/Argos_User_Setup.htm
http://www.uaex.edu/bknox/Argos_User_Setup_non_LDAP.htm
http://www.uaex.edu/bknox/Argos_User_Setup_Shortcut.htm
http://www.uaex.edu/bknox/SPOT.htm
http://www.uaex.edu/bknox/SPOT_non_LDAP.htm
http://www.uaex.edu/bknox/SPOT_part2.htm
http://www.uaex.edu/bknox/banner.htm

http://www.uaex.edu/bknox/BannerAccess Oracle SQL.txt
http://www.uaex.edu/bknox/BannerAccess_Class_Notes.htm
http://www.uaex.edu/bknox/BannerArgosNotes.htm
http://www.uaex.edu/bknox/biasedsearch.html
http://www.uaex.edu/bknox/Campus_Wide_Reporting_Tool_Selection.pdf
http://www.uaex.edu/bknox/CheckArgosBackup20090304.argosexport
http://www.uaex.edu/bknox/maps_backup_today.cmd
http://www.uaex.edu/bknox/maps_backup_today.log
http://www.uaex.edu/bknox/open_po_fund.sql
http://www.uaex.edu/bknox/po_check_zero.sql
http://www.uaex.edu/bknox/searchbknox.html
http://www.uaex.edu/bknox/seq_list.txt
http://www.uaex.edu/bknox/siteflavored.html
http://www.uaex.edu/bknox/standardexternalpage.htm
http://www.uaex.edu/bknox/StandardInternalPage.htm

http://www.uaex.edu/bknox/BannerScripts.htm was my scripts and presentations repository.
The current scripts and presentations repository contains the files above.

This is a personal site maintained by Bruce Knox ( bknox at uaex.edu ) documenting University of Arkansas, Division of Agriculture, Cooperative Extension Service IT projects.

Disclaimer
Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)

Any University of Arkansas Division of Agriculture website Disclaimer terms found in conflict with terms of this disclaimer shall over ride and replace the conflicting terms found herein.

Other personal pages/sites that I maintain:
http://www.openMosix.org the website for the openMosix (Open Source) Project hosted on .
Note: The openMosix Project officially closed March 1, 2008. The source code and mail lists archives will continue to be available on SourceForge as reference materials. Archive Links are: FAQ HowTo Wiki SourceForge

Most of the works of art on my pages other than the Extension banner
are used by permission of J. Wilson Spence.

http://betwinx.com is my personal site. betwinx? This site is largely oriented to Oracle Reporting Solutions in the context of generating bespoken reports from SunGard Higher Education's Banner product. My university site is a more restrained version which cannot contain endorsements of products or companies.
(I maintain a number of special topic pages with links from this primary site.)

Public and Professional Projects of Bruce Knox