sqr for fis
Skip this Video
Download Presentation

Loading in 2 Seconds...

play fullscreen
1 / 53

SQR For FIS - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'SQR For FIS' - Jimmy

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sqr for fis


Colin Abbott

ISR – Technical Services

  • 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
Brio SQR for McGill
  • McGill Reporting Strategy
  • SQR Language
  • Naming conventions for Appworx
  • Distributing with Minerva
  • Getting to Production
  • Tips & Tricks
  • More Info.
mcgill reporting strategy
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)
  • Naming Standards
  • Types of SQR programs
  • Reserve a program name
  • Start with SQL
  • Use a Template, Program Sections
  • Dynamic SQL
  • Procedures
  • Procedures with Parameters
  • PL/SQL Procedures
  • 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
report naming standards
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
types of sqr programs
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 !!
reserve a program name
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
start with sql
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
use a template
Use a Template
  • Start a SQR program with fzrgskel.sqr Template for a basic report and .slk file
  • Take a look at the template
use report vs new report
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
begin 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
dynamic sql
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.
using variables
Using Variables
  • Variable prefixes
  • Variable scope
  • Define variables
  • Bugs due to variable spelling typos
  • 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
brio procedures
Brio Procedures
  • Call a procedure with the DO command
  • do p_print_info
  • do p_get_vendor_by_pidm($pidm)
procedures with parameters
Procedures with Parameters
  • Passing variables by value and by reference
  • Side effects (watch out for Libraries)
calling pl sql procedures
Calling PL/SQL Procedures
  • PL/SQL Blocks
  • Calling PL/SQL Procedures
headers and footers
Headers and Footers
  • Standard Header (library1)
  • Graphic Header (gzrlibr)
  • Line numbering
  • Separate from body
using libraries
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
setting up your unix environment
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)
running the program
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
example 1
Example #1
  • Simple Report
on break processing
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**
csv and slk files
.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
writing to a file
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
using arrays
Using Arrays
  • Define Array
  • Put Data into Array
  • Get Data from Array
  • (FZRG0051 Good example of Arrays)
integrating with appworx
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
file naming conventions
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)
database links
Database Links
  • Do not hard code Database links (It will fail when Appworx runs it)
  • Accept Database links as ASK parameters
using environment variables
Using Environment Variables
  • Use getenv() function
  • let $path = getenv(\'IMAGE_LIB\')
  • let $gif_file = $path || \'/mcgill.gif\'
  • display $gif_file
  • IMAGE_LIB is available to jobs run from Appworx
file retention
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
program options
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
how appworx runs the program
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
  • Can be based on successful/unsuccessful completion of another job/chain
  • Can be based on database logic
  • DWH is UP
  • 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
appworx variables
Appworx Variables
  • Variables are documented in the file “Finance Variables for Appworx.doc”
  • Dynamic variables based on Database logic in FZKBO003 (DWH1 tables)
getting to production
Getting to Production
  • Documenting the program
  • Documenting the Chain
  • Module_objects.txt
  • Request ISRops to move it
  • Moving a new version
  • Moving a modification
integrating with minerva
Integrating with Minerva
  • Minerva Architecture
  • Minerva Demo (Phase I)
  • Minerva Demo (Phase II)
  • Security Classes
  • Security Methods
  • Bursted Reports
minerva architecture
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
minerva phase i
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
minerva phase ii
Minerva Phase II
  • Drill down capability
  • Burstable reports with security
  • Support for all file types
security classes
Security Classes
security methods
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
simple reports
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.
tips tricks
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
more tips tricks
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.
review existing programs
Review existing programs
  • FZRG0004 Simple report uses date logic, graphics header, library functions, csv and slk files, warehouse views
  • FZRG0010 Uses GZRPARA to store an audit date, uses in-line Analytical views, uses BEGIN-SQL to update tables, uses explicit commit and rollback commands, uses Audit and update mode concept.
review existing programs1
Review Existing Programs
  • FZRG0011 Uses an optimiser hint
  • FZRG0014 Generates Multiple reports, uses the “call system” command to generate multiple reports
  • FZRG0017 Uses a Database Link
  • FZRG0024 Budget Application sheets bursted by Fund/Orgn
  • FZRG0045 Creates GZRUPLD Files
more information
More Information
  • Brio Listserv - http://www.sqrug.org/
  • http://www.is.mcgill.ca/brio/developerguide.pdf
  • /opt/brio/ora/sample and /opt/brio/ora/tutorial on VENUS