SQR For FIS

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.