Banner Reporting Solutions
  by Bruce Knox  bknox @t uaex.edu     Oracle Database Programmer/Analyst                                       Copyright 2011     last updated 11/12/11               

Public and Professional Projects by Bruce Knox

Bruce Knox 2007 CES Directory Photo  bknox @t uaex.edu  Oracle Database Programmer/Analyst


Answering the frequent question: Which Tools to use with Banner?

News: 

is a Google Custom Search for my documents and presentations on Banner Reporting Solutions.

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: Oracle SQL*Plus and UNIX Reporting, Evisions' Argos, Ad Hoc Reporting, MS Access Reporting, Oracle Application Express Notes and Projects.

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, as are the advantages.

For Banner Reporting, C is about the same as COBOL without the extensive formatting capabilities that are 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.  For most of us, 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 very 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 nine years.  Now we are moving to replace the MS Access part of this tool with Evisions' Argos, a web based Ad hoc reporting tool.

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: many 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.  Argos is a product that will find be easier to support, faster, and more secure than MS Access.  The product has features that both end-users and programmer will find useful.

Argos Presentations:
  Admin and Security
  MS Access Conversion
  Moving into the Community
 

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 replacement 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.

ePrint Presentations
  Summit 2006 - ePrint Innovative ways we use it   (ABUG version)

Wiki Notes were collected from representative Wiki systems to serve as a model for use by Extension.

Banner Reporting Sites:

    Calvin Deiterich's Argos Reporting
    Zach Heath's Banner Reporting Blog

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.   It is oriented to Oracle Reporting Solutions in the context of generating bespoken reports from SunGard Higher Education's Banner product. 

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 SourceForge.net Logo.
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 used on my pages and presentations, other than the Extension banner and Spot Logo, are used by permission of J. Wilson Spence.

My personal work site is http://www.uaex.edu/bknox.
My personal site is http://betwinx.com.  Copyright 2009, 2010 Bruce Knox









 

 

 

 

 

 

Hit Counter visits

 

Extension is moving to Luminis Content Management System soon and our move will orphan 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://www.uaex.edu/bknox/BannerArgos.htm
http://www.uaex.edu/bknox/BannerArgos_2.5.htm

http://www.uaex.edu/bknox/BannerAccess.htm

http://www.uaex.edu/bknox/BannerAPEX.htm

My scripts and presentations repository contains scripts and presentations.  This repository requires the scripts password.
Currently 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/115_BruceKnox_EnablingFunctionalUserswith Argos.pptx

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_p06.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


The scripts and presentations repository contains the files above.  This repository requires the scripts password.





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:)