Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.

Cooperative Extension Service

Search | Publications | Jobs | Personnel Directory | Links

Public and Professional Projects of Bruce Knox

Banner Reporting Solutions Answering the frequent question: Which Reporting Tools to use with Banner?   by Bruce Knox  bknox @t uaex.edu    Oracle Database Programmer/Analyst    Copyright 2008      date last updated 05/01/08

visits

  News:  Preparing for Summit 2008

Looking for my "Tools, References, Methods, and Programmer Help for the Banner Startup" site, well, it has been moved to
http://www.uaex.edu/bknox/BannerTools.htm

This site is now a more general one, but still primarily about 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:

Tools, References, Methods, and Programmer Help for the Banner Startup (content from my original site) is now:
BannerTools  (Oracle SQL*Plus and UNIX Banner Reporting)

BannerArgos  (Evisions' Argos, Ad Hoc Reporting)  new: Argos 3.1 and API Reporting are now released!
                                                                                           Pre-Summit 2008 Session ppt and Views available now

BannerAccess  (MS Access Reporting)

BannerAPEX (Oracle Application Express Notes and Projects) 

BannerScripts (a restricted code and presentation repository, password from bknox @t uaex.edu)
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)
  ABUG 2006 Cingular Invoice Processing System by Mark Hubbell
  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.ppt        Summit Presentation
  Argos Tech Track.ppt                                    Argos Pre-Summit
  Argos Admin and Security Track.ppt             Argos Pre-Summit
  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.

e-mail Notices  (Simple SQL solutions for complex Banner Reporting Issues)

Oracle Dates  (Simplifying Oracle's powerful date handling functions for use with Banner)

Oracle Notes  (Collection of FoxPro to Oracle Backend Conversion Notes)  updated

UNIX Permissions  (Elegant but Confusing UNIX file permissions)

mail attachments  (Creating email with a text message and an attachment from standard UNIX)

Formatted Documents  (Creating Rich Text Format files from a UNIX server)

Wiki Notes  (just a brief introduction)

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 at http://www.uaex.edu/bknox/banneraccess.htm 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 at http://www.uaex.edu/bknox/bannerargos.htm
Summit 2007 Session: Argos - Moving into the Community

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.