e~print - innovative ways we use it

ABUG 2006 - Harding April 24 th Searcy, AR 1

e~Print - Innovative Ways We Use It

Presented by: Bruce KnoxUniversity of ArkansasDivision of Agriculture

Cooperative Extension Service

April 24, 200610:00 am – 10:40 am

Freedom Room


Introduction

• Purpose: Automate Report Generation for e~Print

• Benefits: Improved Reporting with Reduced Costs


Topics of Discussion/Agenda

• Secure Distributed Reporting

• Our First Efforts

• Server Side Automation

• A Month End Example

• Monitoring the Automation


Secure Distributed Reporting

• We needed a way to deliver timely reports to a geographically distributed management team.


We Have Faculty in Every County


e~Print Delivers Our Reports

• A Secure Web Server• Page Security by Fund/Orgn

(Banner Security)


Our First Efforts

• At First, We Used Only MS Access Reports

• Users Developed and Ran the Reports

• WS-FTP Transferred the Report Files


This Was Great For IT

• After Some Minor Setup,• We Handed It All to a Super User


Why We Replaced The Original Reports

• Our Super User Created a Lot of Reports• More Reports Than She had Time to Run

ABUG 2006 - Harding April 24th Searcy, AR 10

Time For Server Side Automation

• We Use Pass-Through Queries with MS Access

• That Made Reverse Engineeringthe Reports Easy


Summary, Detail, Period End Reporting

• YTD Summary Reports Run Every Hour• YTD Detail Reports Run Twice A Day• Summary, Detail, and Month End Each Evening


A Month End Example

This is Typically all I see of this Application: An email Notice


The Managers See Only Their Reports


Our Month End Reports Are Run Automatically


This is Done Using the Banner Calendar

A cron run shell script runs a SQL*Plus script to check if the Month End Closed Today.

SPOOL fspd_closed.runSELECT'HOST fspd_run_closed_reports.shl', 'Closed: '||FTVFSPD_ACTIVITY_DATE||'

for', 'FSPD', FTVFSPD_FSPD_CODE FSPD, 'FSYR', FTVFSYR_FSYR_CODE FSYRFROM FTVFSYR,FTVFSPD WHERE FTVFSPD_COAS_CODE = FTVFSYR_COAS_CODEAND FTVFSPD_FSYR_CODE = FTVFSYR_FSYR_CODEAND FTVFSPD_PRD_STATUS_IND = 'C'AND FTVFSPD_PRD_END_DATE < SYSDATE -- Keeps Closing Date in the

PastAND TRUNC(FTVFSPD_ACTIVITY_DATE) = TRUNC(SYSDATE);SPOOL OFFSTART fspd_closed.run


Check if the Month End Closed Today

AND FTVFSPD_PRD_STATUS_IND = 'C'AND FTVFSPD_PRD_END_DATE < SYSDATE -- Keeps Closing Date (and Time) in the PastAND TRUNC(FTVFSPD_ACTIVITY_DATE) =

TRUNC(SYSDATE);


We LOG The CRON Job

Tue Jan 17 21:32:00 CST 2006fspd_closed.shl beginningfspd_closed.shl Control Month End Reporting

ScriptsTue Jan 17 21:32:00 CST 2006fspd_closed.shl ending18

(evening before the closing)


Then The Closing

Wed Jan 18 21:32:00 CST 2006fspd_closed.shl beginningfspd_closed.shl Control Month End Reporting

ScriptsHOST fspd_run_closed_reports.shl Closed: 18-JAN-2006 for FSPD 06 FSYR 06Wed Jan 18 21:32:00 CST 2006(evening of closing)


Finally, The Reports

• fspd_run_closed_reports.shl beginning• Run The YTD Summary and The MTD

Detail Reports for Month End• Wed Jan 18 21:32:00 CST 2006• fspd_run_closed_reports.shl ending• Wed Jan 18 21:32:00 CST 2006


The Drill Down Gives Each Month


Each Month End Report Stores Multiple Copies


YTD Summary


Run: Date-Time and Report Name


MTD Detail


e~Print – Defining Reports


Defining Reports


Naming the Report for e~Print


Naming The Report for Users


Where is the Title on the Report?


Place the Date and Time on the Menus


What to do with the 1st Blank Page?


Page Security - ORGN


Page Security - FUND


Optional email Notification


The Shell Scripts – Let’s See How Simple

• All Report Shell Scripts are Run as One Job• Each Report has a Shell Script


Report Shell Script Example– Part I Setup

#!/bin/ksh# dbr01_cp.shl # Closed Period Reports Run 01: Orgn Summary Daily

# Run orgsumm_dbr.sql to create extract, dbr01.pco to create report,# ftp the dbr01.lst report as 100gbsumm, then # ftp 100gbsumm.done to e~Print dbrd Repository

printdateprint dbr01_cp.shl beginning

PATH= ...export PATHexport ORACLE_SID=PRODORAENV_ASK=NO. /usr/lbin/oraenv


Report Shell Script - Part II Extract and Report

# dbr01_cp.shl continued

echo "START orgsumm_dbr.sql"

#INSERT Extracted Records into Table DBR_01run_pw_mgr.shl userid orgsumm_dbr.sql

echo beginning dbr01.pcodbr01

echo dbr01.lst Report Complete


Report Shell Script – Part III Transfer to e~Print

# put dbr01.lst e~Print Repository dbrd dbr01_cp.shl continued

print ftp put dbr01.lst 100gbsummecho > 100gbsumm.doneftp -n -i 170.94.62.000<<eofuser dbrd pwasciiput dbr01.lst 100gbsummput 100gbsumm.done 100gbsumm.donequiteofrm -f dbr01.lstrm -f 100gbsumm.doneprintdateprint dbr01_cp.shl ending # end of dbr01_cp.shl


Logging the Run

• dbr01_cp.shl > dbr01_cp_cron.log 2>&1• dbr01_cp_cron.log will contain any Run

messages


Log Snippets - Part I Extract

Wed Jan 18 21:32:01 CST 2006dbr01_cp.shl beginningSTART orgsumm_dbr.sql

FSPD 06 FSYR 06 Run Date 18-JAN-2006orgsumm_dbr extract beginning

2987 rows created.Commit complete.

orgsumm_dbr extract complete


Log Snippets - Part II Report and FTP

beginning dbr01.pcodbr01 CONNECTED TO ORACLE. dbr01 completed dbr01.lst Report Completeftp put dbr01.lst 100gbsumm

Wed Jan 18 21:32:17 CST 2006dbr01_cp.shl ending


Report Shell Scripts are Run as One Job

#!/bin/ksh

#fspd_run_closed_reports.shl Run The YTD Summary and The MTD Detail Reports for Month End

# This .shl file is intended to be run ONLY by fspd_closed.shl/fspd_closed.sql

printdateprint fspd_run_closed_reports.shl beginning

echo Run The YTD Summary and The MTD Versions of Detail Reports for Month End

PATH= ...export PATHexport ORACLE_SID=PRODORAENV_ASK=NO. /usr//ora

# Set Values in Run_Dates Table was done in fspd_closed.sql run by fspd_closed.shl


Adding a New Report is Simpleat -q b > fspd_closed_reports_at.log 2>&1 <<EOF dbr01_cp.shl > dbr01_cp_cron.log 2>&1 dbr02_cp.shl > dbr02_cp_cron.log 2>&1 dbr03_cp.shl > dbr03_cp_cron.log 2>&1 dbr04_cp.shl > dbr04_cp_cron.log 2>&1 dbr09_cp.shl > dbr09_cp_cron.log 2>&1 dbr05_cp.shl > dbr05_cp_cron.log 2>&1 dbr14_cp.shl > dbr14_cp_cron.log 2>&1 dbr13_cp.shl > dbr13_cp_cron.log 2>&1 dbr06m_cp.shl > dbr06m_cp_cron.log 2>&1 dbr07m_cp.shl > dbr07m_cp_cron.log 2>&1 dbr08m_cp.shl > dbr08m_cp_cron.log 2>&1 dbr10m_cp.shl > dbr10m_cp_cron.log 2>&1 dbr11m_cp.shl > dbr11m_cp_cron.log 2>&1 dbr12m_cp.shl > dbr12m_cp_cron.log 2>&1 dbr15m_cp.shl > dbr15m_cp_cron.log 2>&1 dbr16m_cp.shl > dbr16m_cp_cron.log 2>&1 dbr17m_cp.shl > dbr17m_cp_cron.log 2>&1EOF


The Audit Script – Snippet I

# Any Errors?awk '{print FILENAME, $0}' dbr??_cron.log | awk 'length >15' > dbr_audit_combined_logs.txt

egrep -i 'A file or directory in the path name does not exist.|ksh*not found.|netout: write returned 0?| 0 rows created.' dbr_audit_combined_logs.txt > dbr_audit_error_checking.txt


Audit Script – Snippet II

# Chose the email Subject Textif [ -s "/home/common/dbr_audit_error_checking.txt" ]then mail -s "dbr_audit.shl - Errors Detected"

[email protected] < dbr_audit_summary_plus.txtelse mail -s "dbr_audit.shl - No Errors Detected"

[email protected] < dbr_audit_summary_plus.txtfi


Monitoring via email


oops!

From: <[email protected]>To: <[email protected]>Date: 2/15/2006 10:18:36 PMSubject: dbr_audit.shl - Errors

DetectedAudit Report of dbrnn Logs. . .


Summary

• e~Print Can Deliver More Than You Can Report

• Simple Automation Can Bring Great Rewards

• Label Your Report Pages• Use a Simple Modular Design• Automate Your Monitoring


Questions?


Thank You!

Bruce Knox

[email protected]

http://www.uaex.edu/bknox

http://abug.uaex.edu/

e~print - innovative ways we use it

Documents