Reporting Strategy Bill Pearce Ventura County CCD In

Reporting Strategy Bill Pearce Ventura County CCD

In honor of STAR WARS III “Revenge of the Sith” This presentation is brought to you by the evil… 2

DARTH TATER! 3

What is thy bidding, my Master Considerations • Replace Oracle Reports • Use GSASECR security • Run through job submission – parameter sets • Create text files for download to Excel • Create Adobe files for security • $$$$$ COST $$$$$ • Decided on sql*plus! 4

Impressive. Most Impressive Process Description • x. YSxxxx Local Reports can be printed, output to DATABASE, pdf, or text • All Baseline C reports can be printed, output to DATABASE, or pdf • COBOL reports can be printed, output to DATABASE, or selectively to pdf • Uses text 2 pdf. c program • DATABASE, pdf, text create GUBOUTP only 5

Don’t be too proud of this technological terror you’ve constructed database or text x. YSxxxx Local Report pdf Baseline C, COBOL Reporting Schematic text 2 pdf GUBOUTP Header Record Only Web Report Viewer database Report File On server 6

This facility is crude but it should be adequate to freeze Skywalker for his journey to the Emperor. SQL*Plus Reports Created long ago in a galaxy far, far away (and enhanced by CLPCCD) – changes: • Defined as “Procedure” in GJAJOBS • Gjajobs. shl calls common grunsql. shl (unless. shl of same name is found) • Same select creates all 3 types of reports – creates/calls. sql script during runtime to change settings, breaks, totals, etc. for. lis, . pdf, . txt file • Performance much improved over OR 7

I am altering the deal. Pray I don’t alter it any further. column c_text_file noprint new_value cn_text_file select lower('&cn_job_name') || '_' || &cn_one_up_no || '. sql' c_text_file from dual; spool $HOME/&cn_text_file select 'clear breaks' from dual where upper('&cn_printer') = 'TEXT' union all select 'clear computes' from dual where upper('&cn_printer') = 'TEXT' union all select 'set pagesize 50000' from dual where upper('&cn_printer') = 'TEXT' union all select 'ttitle off' from dual where upper('&cn_printer') = 'TEXT' union all select 'set underline off' from dual where upper('&cn_printer') = 'TEXT' union all select 'set pagesize 66' from dual where upper('&cn_printer') = 'PDF' union all select 'ttitle on' from dual where upper('&cn_printer') != 'TEXT'; spool off start $HOME/&cn_text_file !rm $HOME/&cn_text_file 8

Indeed you are Powerful. Web Report Viewer • Part of Employee Self-Service • Report retention determined by GTVSDAX • Report can be copied to other user(s) or manually deleted • Clock starts over when report is copied to another user • Nightly cleanup script deletes GUBOUTP, server files (uses external tables) 9

There’ll be noone to stop us this time. Web Report Viewer (continued) • Will replace email to “push” reports • Used to deliver reports from Workflow automatically • Plans to add link to Banner 7. 0 menu to sign on automatically to Report Viewer 10

I sense something. A presence I’ve not felt since… 11

Several fighters have broken off from the main group. Come with me. 12

13

14

All too easy… 15

There’s no escape. Don’t make me destroy you. 16

I must obey my Master. 17