Sqr for fis
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)

Sqr for fis

  • 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

Sqr for fis

  • 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