Brio SQR for McGill by yurtgc548


									   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
•   McGill Reporting Strategy
•   SQR Language
•   Naming conventions for Appworx
•   Distributing with Minerva
•   Getting to Production
•   Tips & Tricks
•   More Info.
      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
•   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
Sample Reports
     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
• 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
• 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
• The first step in attacking a SQR report
  should be to develop the main cursor in
• Use existing packages where appropriate
  documented in
  package names
            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
• 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
• Column alias
• Working storage variables
• Processes SQR commands for each row
• Use Inline Views
• on-error, loops and distinct
• Use SQL to constrain rows returned,
  discarding rows will effect break processing
            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
•   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
• Call a procedure with the DO command
• do p_print_info
• do p_get_vendor_by_pidm($pidm)
    Procedures with Parameters
• Passing variables by value and by reference
• Side effects (watch out for Libraries)
   Calling PL/SQL Procedures
• PL/SQL Blocks
• Calling PL/SQL Procedures
          Headers and Footers
•   Standard Header (library1)
•   Graphic Header (gzrlibr)
•   Line numbering
•   Separate from body
              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
• 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
• Create program in notepad or your favourite
  editor. (UltraEdit has SQR colour coding)
• Copy the File to Venus using ssh file
• .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
• Simple Report
          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
           .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
                                  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 ' ||
•       display $disp
•     end-if

• Write to a file
•   let $printl = ‘Hello World’
•   write 1 from $printl

• Close File
•   close 1
              Using Arrays
•   Define Array
•   Put Data into Array
•   Get Data from Array
•   (FZRG0051 Good example of Arrays)
      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
• Module~Burst_Jchain-jjobid
• ex. FZRG0024~100001_J123456-
•   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',
            Database Links
• Do not hard code Database links (It will fail
  when Appworx runs it)
• Accept Database links as ASK parameters
    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
             File Retention
• Appworx will expire and delete a file after
  its retention period has passed
• Specify retention period in module
• 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
           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
• 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
  pWorx 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
• Variables are documented in the file
  “Finance Variables for Appworx.doc”
• Dynamic variables based on Database logic
  in FZKBO003 (DWH1 tables)
        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
•   Minerva Architecture
•   Minerva Demo (Phase I)
•   Minerva Demo (Phase II)
•   Security Classes
•   Security Methods
•   Bursted Reports
         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
•   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
• Drill down capability
• Burstable reports with security
• Support for all file types
          Security Classes
           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
• Have Richard ask Steve/Danny to enrol the
  object in a FIS_RPT class
• Once in a class create an extender record in
• 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
• 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
• Slow SQL ask Ross for help
         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
• 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 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
• Brio Listserv -
• /opt/brio/ora/sample and
  /opt/brio/ora/tutorial on VENUS

To top