1 / 57

e~Print - Innovative Ways We Use It

e~Print - Innovative Ways We Use It. Presented by: Bruce Knox University of Arkansas Division of Agriculture Cooperative Extension Service. April 24, 2006 10:00 am – 10:40 am Freedom Room. Introduction. Purpose: Automate Report Generation for e~Print

tegan
Download Presentation

e~Print - Innovative Ways We Use It

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. e~Print - Innovative Ways We Use It Presented by: Bruce Knox University of ArkansasDivision of AgricultureCooperative Extension Service April 24, 200610:00 am – 10:40 amFreedom Room ABUG 2006 - Harding April 24th Searcy, AR 1

  2. Introduction • Purpose: Automate Report Generation for e~Print • Benefits: Improved Reporting with Reduced Costs ABUG 2006 - Harding April 24th Searcy, AR 2

  3. Topics of Discussion/Agenda • Secure Distributed Reporting • Our First Efforts • Server Side Automation • A Month End Example • Monitoring the Automation ABUG 2006 - Harding April 24th Searcy, AR 3

  4. Secure Distributed Reporting • We needed a way to deliver timely reports to a geographically distributed management team. ABUG 2006 - Harding April 24th Searcy, AR 4

  5. We Have Faculty in Every County ABUG 2006 - Harding April 24th Searcy, AR 5

  6. e~Print Delivers Our Reports • A Secure Web Server • Page Security by Fund/Orgn (Banner Security) ABUG 2006 - Harding April 24th Searcy, AR 6

  7. Our First Efforts • At First, We Used Only MS Access Reports • Users Developed and Ran the Reports • WS-FTP Transferred the Report Files ABUG 2006 - Harding April 24th Searcy, AR 7

  8. This Was Great For IT • After Some Minor Setup, • We Handed It All to a Super User ABUG 2006 - Harding April 24th Searcy, AR 8

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

  10. Time For Server Side Automation • We Use Pass-Through Queries with MS Access • That Made Reverse Engineeringthe Reports Easy ABUG 2006 - Harding April 24th Searcy, AR 10

  11. 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 ABUG 2006 - Harding April 24th Searcy, AR 11

  12. A Month End Example This is Typically all I see of this Application: An email Notice ABUG 2006 - Harding April 24th Searcy, AR 12

  13. A Month End Example This is Typically all I see of this Application: An email Notice ABUG 2006 - Harding April 24th Searcy, AR 13

  14. The Managers See Only Their Reports ABUG 2006 - Harding April 24th Searcy, AR 14

  15. Our Month End Reports Are Run Automatically ABUG 2006 - Harding April 24th Searcy, AR 15

  16. 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.run SELECT 'HOST fspd_run_closed_reports.shl', 'Closed: '||FTVFSPD_ACTIVITY_DATE||' for', 'FSPD', FTVFSPD_FSPD_CODE FSPD, 'FSYR', FTVFSYR_FSYR_CODE FSYR FROM FTVFSYR,FTVFSPD WHERE FTVFSPD_COAS_CODE = FTVFSYR_COAS_CODE AND FTVFSPD_FSYR_CODE = FTVFSYR_FSYR_CODE AND FTVFSPD_PRD_STATUS_IND = 'C' AND FTVFSPD_PRD_END_DATE < SYSDATE -- Keeps Closing Date in the Past AND TRUNC(FTVFSPD_ACTIVITY_DATE) = TRUNC(SYSDATE); SPOOL OFF START fspd_closed.run ABUG 2006 - Harding April 24th Searcy, AR 16

  17. 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 Past AND TRUNC(FTVFSPD_ACTIVITY_DATE) = TRUNC(SYSDATE); ABUG 2006 - Harding April 24th Searcy, AR 17

  18. We LOG The CRON Job Tue Jan 17 21:32:00 CST 2006 fspd_closed.shl beginning fspd_closed.shl Control Month End Reporting Scripts Tue Jan 17 21:32:00 CST 2006 fspd_closed.shl ending18 (evening before the closing) ABUG 2006 - Harding April 24th Searcy, AR 18

  19. Then The Closing Wed Jan 18 21:32:00 CST 2006 fspd_closed.shl beginning fspd_closed.shl Control Month End Reporting Scripts HOST fspd_run_closed_reports.shl Closed: 18-JAN-2006 for FSPD 06 FSYR 06 Wed Jan 18 21:32:00 CST 2006 (evening of closing) ABUG 2006 - Harding April 24th Searcy, AR 19

  20. 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 ABUG 2006 - Harding April 24th Searcy, AR 20

  21. The Drill Down Gives Each Month ABUG 2006 - Harding April 24th Searcy, AR 21

  22. Each Month End Report Stores Multiple Copies ABUG 2006 - Harding April 24th Searcy, AR 22

  23. Each Month End Report Stores Multiple Copies ABUG 2006 - Harding April 24th Searcy, AR 23

  24. YTD Summary ABUG 2006 - Harding April 24th Searcy, AR 24

  25. YTD Summary ABUG 2006 - Harding April 24th Searcy, AR 25

  26. YTD Summary ABUG 2006 - Harding April 24th Searcy, AR 26

  27. Run: Date-Time and Report Name ABUG 2006 - Harding April 24th Searcy, AR 27

  28. MTD Detail ABUG 2006 - Harding April 24th Searcy, AR 28

  29. MTD Detail ABUG 2006 - Harding April 24th Searcy, AR 29

  30. MTD Detail ABUG 2006 - Harding April 24th Searcy, AR 30

  31. e~Print – Defining Reports ABUG 2006 - Harding April 24th Searcy, AR 31

  32. Defining Reports ABUG 2006 - Harding April 24th Searcy, AR 32

  33. Defining Reports ABUG 2006 - Harding April 24th Searcy, AR 33

  34. Naming the Report for e~Print ABUG 2006 - Harding April 24th Searcy, AR 34

  35. Naming The Report for Users ABUG 2006 - Harding April 24th Searcy, AR 35

  36. Where is the Title on the Report? ABUG 2006 - Harding April 24th Searcy, AR 36

  37. Place the Date and Time on the Menus ABUG 2006 - Harding April 24th Searcy, AR 37

  38. What to do with the 1st Blank Page? ABUG 2006 - Harding April 24th Searcy, AR 38

  39. Page Security - ORGN ABUG 2006 - Harding April 24th Searcy, AR 39

  40. Page Security - FUND ABUG 2006 - Harding April 24th Searcy, AR 40

  41. Optional email Notification ABUG 2006 - Harding April 24th Searcy, AR 41

  42. The Shell Scripts – Let’s See How Simple • All Report Shell Scripts are Run as One Job • Each Report has a Shell Script ABUG 2006 - Harding April 24th Searcy, AR 42

  43. 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 print date print dbr01_cp.shl beginning PATH= ... export PATH export ORACLE_SID=PROD ORAENV_ASK=NO . /usr/lbin/oraenv ABUG 2006 - Harding April 24th Searcy, AR 43

  44. Report Shell Script - Part II Extract and Report # dbr01_cp.shl continued echo "START orgsumm_dbr.sql" #INSERT Extracted Records into Table DBR_01 run_pw_mgr.shl userid orgsumm_dbr.sql echo beginning dbr01.pco dbr01 echo dbr01.lst Report Complete ABUG 2006 - Harding April 24th Searcy, AR 44

  45. 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 100gbsumm echo > 100gbsumm.done ftp -n -i 170.94.62.000<<eof user dbrd pw ascii put dbr01.lst 100gbsumm put 100gbsumm.done 100gbsumm.done quit eof rm -f dbr01.lst rm -f 100gbsumm.done print date print dbr01_cp.shl ending # end of dbr01_cp.shl ABUG 2006 - Harding April 24th Searcy, AR 45

  46. Logging the Run • dbr01_cp.shl > dbr01_cp_cron.log 2>&1 • dbr01_cp_cron.log will contain any Run messages ABUG 2006 - Harding April 24th Searcy, AR 46

  47. Log Snippets - Part I Extract Wed Jan 18 21:32:01 CST 2006 dbr01_cp.shl beginning START orgsumm_dbr.sql FSPD 06 FSYR 06 Run Date 18-JAN-2006 orgsumm_dbr extract beginning 2987 rows created. Commit complete. orgsumm_dbr extract complete ABUG 2006 - Harding April 24th Searcy, AR 47

  48. Log Snippets - Part II Report and FTP beginning dbr01.pco dbr01 CONNECTED TO ORACLE. dbr01 completed dbr01.lst Report Complete ftp put dbr01.lst 100gbsumm Wed Jan 18 21:32:17 CST 2006 dbr01_cp.shl ending ABUG 2006 - Harding April 24th Searcy, AR 48

  49. 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 print date print fspd_run_closed_reports.shl beginning echo Run The YTD Summary and The MTD Versions of Detail Reports for Month End PATH= ... export PATH export ORACLE_SID=PROD ORAENV_ASK=NO . /usr//ora # Set Values in Run_Dates Table was done in fspd_closed.sql run by fspd_closed.shl ABUG 2006 - Harding April 24th Searcy, AR 49

  50. Adding a New Report is Simple at -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>&1 EOF ABUG 2006 - Harding April 24th Searcy, AR 50

More Related