1 / 53

SQR For FIS

SQR For FIS. Colin Abbott ISR – Technical Services. Goals. After this class the attendees should be able to create a basic SQR program and have the report distributed on Minerva. Brio SQR for McGill . McGill Reporting Strategy SQR Language Naming conventions for Appworx

Jimmy
Download Presentation

SQR For FIS

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. SQR For FIS Colin Abbott ISR – Technical Services

  2. Goals • After this class the attendees should be able to create a basic SQR program and have the report distributed on Minerva

  3. Brio SQR for McGill • McGill Reporting Strategy • SQR Language • Naming conventions for Appworx • Distributing with Minerva • Getting to Production • Tips & Tricks • More Info.

  4. McGill Reporting Strategy • Ad-Hoc Reporting using warehouse views • On-Line Crystal reports • Crystal templates • Distributed reports with Brio SQR • Client / Web4 products • Banner Baseline Reports • PL/SQL & Emailed UTL Files (Try to Avoid)

  5. SQR • Naming Standards • Types of SQR programs • Reserve a program name • Start with SQL • Use a Template, Program Sections • BEGIN-SELECT • Dynamic SQL • Procedures • Procedures with Parameters • PL/SQL Procedures

  6. SQR • Headers and Footers • Current version 6.1.3 ($sqr-ver) • Running the program • Setting up your Unix environment • Using variables (Ask vs. Input) • Using Libraries • On-Break processing • .csv and .slk files • Writing to a text file • Using Arrays

  7. Sample Reports

  8. Report Naming Standards • FZRG0001A • First Character F = Finance, S=Student • Second Character Z for custom • Third Character R for Report • Fourth Character G=GL, P=Purchasing, B=budget ... • 4 digits for a report number • Optionally a multiple report can end in a letter A-Z

  9. Types of SQR Programs • Simple Report - is a report that generates only one output report, it can generate many formats of the same report (.csv, .slk, .pdf) • Multiple Reports - one report that generates several different outputs, each one can have several formats • Bursted Report - A simple report that is separated into different physical files by a key field (ie. Fund/Orgn) • !!Avoid having a bursted multiple report !!

  10. Reserve a program name • Finance has a spread sheet of program names. Before starting a program reserve a name on this spread sheet • 1_Finance\COMMON\Technical\Stophere\program names\Program Names.xls

  11. Start with SQL • The first step in attacking a SQR report should be to develop the main cursor in SQL (TOAD). • Use existing packages where appropriate documented in \1_Finance\COMMON\Technical\Stophere\package names

  12. Use a Template • Start a SQR program with fzrgskel.sqr Template for a basic report and .slk file • Take a look at the template

  13. Use-Report vs. New-Report • Use Report • Need to predefine your reports • Creates filename.spf, filename_01.spf • Need to use SQRP to “name” them • You can toggle between reports • New-Report • You can create/name the report on the fly • You can not toggle between reports

  14. Begin-Select • BEGIN-SELECT END-SELECT • Column alias • Working storage variables • Processes SQR commands for each row returned • Use Inline Views • on-error, loops and distinct • Use SQL to constrain rows returned, discarding rows will effect break processing

  15. Dynamic SQL • You can build a where clause based on input parameters or program logic • let $where_clause = … • in Begin-Select you can use [$where_clause] Must be enclosed in square brackets • (see FZRG0024 for example) determines by input parameter if we are selecting academic or non-academic budget sheets.

  16. Using Variables • Variable prefixes • Variable scope • Define variables • Bugs due to variable spelling typos

  17. Print • Print ‘Fund Total’ (1,1) • Print ‘Fund Total (+1,1) • Print ‘Fund Total (,1) • Print #amt (1,1) edit ‘999.99’ • next-listing need=1

  18. Brio Procedures • Call a procedure with the DO command • do p_print_info • do p_get_vendor_by_pidm($pidm)

  19. Procedures with Parameters • Passing variables by value and by reference • Side effects (watch out for Libraries)

  20. Calling PL/SQL Procedures • PL/SQL Blocks • Calling PL/SQL Procedures

  21. Headers and Footers • Standard Header (library1) • Graphic Header (gzrlibr) • Line numbering • Separate from body

  22. Using Libraries • Common libraries (library1, gzrsylk) • Shared libraries (gzrlibr, fzrlibr, nzrlibr) • Pre-processor directives • Include a library with #INCLUDE • Include a procedure with #DEFINE • Adding procedures to a library (use #IFDEF and #END-IF

  23. Setting up your Unix Environment • Copy someone’s .profile file from Venus • .profile sets up environment variables for Brio and Oracle • keep in mind that if you depend on settings in your .profile Appworx may not have the same settings (look at SQRP_MC2)

  24. Running the Program • Create program in notepad or your favourite editor. (UltraEdit has SQR colour coding) • Copy the File to Venus using ssh file transfer • .SPF Files • Run the program from the command line sqr filename.sqr -I$PROCLIB -printer:pd sqr filename.sqr -I$PROCLIB -keep -nolis • View output from ssh file transfer

  25. Example #1 • Simple Report

  26. Break

  27. On-Break Processing • Before and After procedures • Level • Order by the same as the break levels • Break fields must appear in order of level= • **Keep in mind that the before procedure has not set the working storage variables yet**

  28. .csv and .slk files • Brio can generate .csv files for you (but don’t use it) • Problems with .csv files • Use gzrsylk.sqr Library to easily create .slk files

  29. Writing to a file • Create a File • do p_generate_file_name ($appworx_module_name, $appworx_chain_id, $appworx_seq_id, '', '.slk', $report_file_name) • Open $report_file_name as 1 for-writing record=500 • status=#filestat • if #filestat != 0 • let $disp = 'SQR- Error opening file for writing ' || $report_file_name • display $disp • end-if • Write to a file • let $printl = ‘Hello World’ • write 1 from $printl • Close File • close 1

  30. Using Arrays • Define Array • Put Data into Array • Get Data from Array • (FZRG0051 Good example of Arrays)

  31. Integrating With Appworx • File Naming Conventions • Using DB Links • Using Environment Variables • File Retention • Program options • How Appworx runs the program • Conditions • Chains • Appworx Variables

  32. File Naming Conventions • Module~Burst_Jchain-jjobid • ex. FZRG0024~100001_J123456-j123456.pdf • do p_appworx_info ('{appworx_file_name}', $appworx_module_name ,$appworx_chain_id, $appworx_seq_id) • do p_generate_file_name ($hold_appworx_module_name, $appworx_chain_id, $appworx_seq_id, '', '.slk', $report_file_name)

  33. Database Links • Do not hard code Database links (It will fail when Appworx runs it) • Accept Database links as ASK parameters

  34. Using Environment Variables • Use getenv() function • BEGIN-PROGRAM • let $path = getenv('IMAGE_LIB') • let $gif_file = $path || '/mcgill.gif' • display $gif_file • END-PROGRAM • IMAGE_LIB is available to jobs run from Appworx

  35. File Retention • Appworx will expire and delete a file after its retention period has passed • Specify retention period in module documentation • Monthly reports 37 months • Weekly reports 3 months • Daily reports, depends on report • No Archiving plan in effect yet, once Appworx deletes a file it can not be retrieved

  36. Program Options • Appworx has many program options • a Program option is basically just a shell script that controls how Appworx will run the program • for SQR the only program option is SQRP_MC2 this needs to be specified on program documentation

  37. How Appworx runs the program • Appworx will run based on the commands in the program option file • SQRP_MC2 knows what type of files SQR should generate, builds a command line call to SQR, copies files from the run directory to the /opt/approx/out/FIS directory • New file type, Vince needs to modify SQRP_MC2 File in \\campus1\data\projwork\TechServ\Production\Production_Libraries\Source_Library\AppWorx Code\VENUS

  38. Conditions • Can be based on successful/unsuccessful completion of another job/chain • Can be based on database logic • DWH is UP

  39. Chains • Several chains already exist for FIS reports • FIS-Rpts_ME_Chain Month End Reports • FIS-RPTS_WKLY Weekly Reports • FIS-Rpts_Daily_Chain • FIS-Rpts_Monthly_Ledgers • FIS-Rpts_Accrual_Chain • FIS-Rpts_Fund_Cleanup

  40. Appworx Variables • Variables are documented in the file “Finance Variables for Appworx.doc” • Dynamic variables based on Database logic in FZKBO003 (DWH1 tables)

  41. Getting to Production • Documenting the program • Documenting the Chain • Module_objects.txt • Request ISRops to move it • Moving a new version • Moving a modification

  42. Integrating with Minerva • Minerva Architecture • Minerva Demo (Phase I) • Minerva Demo (Phase II) • Security Classes • Security Methods • Bursted Reports

  43. Minerva Architecture • Report Server - Venus • Jobs run by Appworx • Output in /opt/appworx/out/FIS • GZRREPT (populated at 6AM daily) • BZGKRTRE PL/SQL web package • Links to files on Venus

  44. Minerva Phase I • Also used by Student Module • No Fund Security • Can only access .pdf .csv .txt and .html • .slk files and .tar files can not be accessed in this version

  45. Minerva Phase II • Drill down capability • Burstable reports with security • Support for all file types

  46. Security Classes • MCGILL_FIS_RPT_PUBLIC_C • MCGILL_FIS_RPT_EXECUTIVE_C • MCGILL_FIS_RPT_CENTRALSTAFF_C • MCGILL_FIS_RPT_ACCOUNTANT_C • MCGILL_FIS_RPT_FINSTMT_C • MCGILL_FIS_RPT_PURCHASING_C • MCGILL_FIS_RPT_ISR_C

  47. Security Methods • Security Methods (Defined in GZBSECM) • Each report is assigned to a security method • Security method is really a PL/SQL procedure, generates an addition to the where clause that returns list of reports. • Fund, Fund/Orgn, ID, Fund/Orgn NHIDIST, Fund/Orgn Budget Admin

  48. Simple Reports • Have Richard ask Steve/Danny to enrol the object in a FIS_RPT class • Once in a class create an extender record in GZBOBJS • Report can be set active or inactive in the extender table. • Setting a report as inactive in MCGP but active in FIS1 is a good way to test distribution and security methods.

  49. Tips & Tricks • Using Optimiser hints • Use Ask Variables when the value is a key field in your main cursor (ie. Fiscal year, chart code) this improves performance • use the -s flag to see generated SQL and statistics • Slow SQL ask Ross for help

  50. More Tips & Tricks • Use GZRPARA to store information like Last Run Date. • Use GZBLPSC to store a fund pop selection where the fund selection criteria is volatile.

More Related