Contents of the Book

View a presentation about the new edition of Harnessing SQR.

Background

Section 1 – Review of Essential Syntax, Coding Constructs & Commands

Chapter 1 – Input, Output, & Program File Types

  • Input Files
  • Output Files
  • Program Files
  • Other Files
  • Default Output File Naming Conventions

Chapter 2 - The SQR Command Line

  • Command Line Structure
  • Example SQR Command Line Accessing an Oracle RDBMS
  • SQR Command Line Flags
  • File Naming & Relocating
  • SQRP

Chapter 3 – Variables

  • Programmer Defined Variables
  • Declaring Variables Explicitly
  • Avoiding Ambiguous Variable Assignments
  • Out of the Box Functions (String)
  • Out of the Box Functions (Numeric Functions)
  • Out of the Box Functions (Date)
  • Establishing Parameters as Variables
  • Reserved Variables
  • Variable Scope

Chapter 4 – Procedures

  • DML Procedures – BEGIN-SELECT
  • DML & DDL Procedures – BEGIN-SQL
  • Other Procedures
  • Expansion of the BEGIN-SELECT Paragraph
  • Early Exiting
  • Expansion of the BEGIN-SQL Paragraph
  • Procedures & Variable Scope
  • Altering Variable Scope

Chapter 5 – Printing

  • Report Grid
  • Report Cursor & Print Positions
  • Logical Print Areas
  • Explicit Printing
  • Implicit Printing
  • NEXT-LISTING Command
  • Group Breaks
  • The POSITION Command
  • Print Attributes
  • Edit Masks
  • The GRAPHIC Command
  • The ALTER-PRINTER Command
  • Changing Colors
  • Newspaper Style Printing
  • Printing Non-textual Images

Chapter 6 – Miscellaneous Areas of Interest

  • Memory Data Structures
  • ASCII File Access and Manipulation
  • File Functions
  • Interacting with the OS
  • Table of Contents (TOC)
  • Event Driven Programming
  • Miscellaneous Functions
  • Pausing SQR Execution
  • Miscellaneous Programming Tips Related To Arrays

Section 2 – A Practical & Proven Methodology for Developing An Enterprise-wide Report Suite

Chapter 7 – SQR Report Suite Development

  • Overview of the SDLC
  • A Practical & Proven SQR SDLC
  • Typical Results of Any Project's SDLC
  • Staff, Scope, and Schedule – the Three Project Drivers
  • Darrin Miller's Laws of Software Engineering
  • The Psychology of the SDLC
  • Avoiding the Pitfalls
  • Putting It All in Perspective

Chapter 8 – SQR Enterprise Report Suite Requirements

  • Parameterized Reporting that Provides Control over Report Content
  • Flexible Querying Capabilities
  • Crisp and Clean Output
  • Multiple Types of Output (HTML, PDF, etc.)
  • Ability to Export Data Queried to Other Applications (MS Excel, Brio.Intelligence, SAS, etc.)
  • The Ability to Implement and Provide Robust Auditing and Error Handling
  • Ease of Maintenance

Chapter 9 – SQR Report Suite Design

  • Output – At a Glance
  • Output – A More Detailed View
  • Input – At A Glance
  • Input – A Closer Look
  • Layers and Interdependencies of the SQR Report Suite
  • Process
  • Design Aides

Chapter 10 – SQR Report Suite Development

  • Putting the "Cookie Cutter" Approach in Action
  • Development Standards
  • Configuration Management
  • Staff Members & Their Roles
  • Environmental Assumptions & Deployment
  • Recommended Process Flow

Section 3 – Advanced Topics

Chapter 11 – Bursted Reports

  • HTML Bursting
  • Typical Bursting Requirements
  • Secure Bursting
  • Physical Bursting
  • BP Access to Physically Bursted Report Output
  • Physical Bursting Efficiency

Chapter 12 – Spreadsheet Style Reports

  • Typical Accounting Database Schema
  • Typical Reporting Requirements
  • Code Review
  • Sample Report Output
  • Time to Market Vs Time to Execute

Chapter 13 – Designing SQR Programs for Multiple Languages

  • Supporting Illustration and Clarification
  • Universal Translator
  • The ALTER-LOCALE Command
  • Required Database Schema
  • Code Review

Chapter 14 – Accessing Data From Multiple Databases

  • DML
  • DDL/DML
  • Alternative Methods to Change Database Connectivity
  • Multi-database Connectivity Via the Database Vendor
  • Multi-database Connectivity Via SQR
  • Performance Issues
  • Arrays
  • Load-Lookup Data Structures
  • Creation & Population of Temporary Database Tables

Chapter 15 – Using SQR for ETL

  • Extraction
  • Transformation
  • Loading
  • Additional SQR ETL (Efficiency Through Language) Topics

Chapter 16 – Designing SQR Programs that are Portable

  • DML
  • Designing an SQR Program to be Portable Across Multiple RDBMSs
  • Designing an SQR Program to be Portable Across Multiple Platforms

Chapter 17 – Calling Stored Procedures

  • Calling Procedures Stored within an Ingres RDBMS
  • Calling Procedures Stored within a MS SQL Server or Sybase Database
  • Calling Procedures Stored within an Oracle Database
  • Integrating with an Oracle Stored Procedure – Code Relocation
  • SQR Program Encapsulation
  • SQR Program as the Wrapper

Chapter 18 – Tuning SQR Programs

  • Database Tuning
  • Developer Responsibilities
  • SQR & Database Specific Tuning Approaches
  • SQR & SQL Tuning
  • SQR Tuning Through Design

Section 4 – Integration Between SQR & Brio.Portal

Chapter 19 – Publishing an SQR Program into Brio.Portal

Step 1 – Isolate All Files Needed

Step 2 – Create BP Categories

Step 3 – Publishing Dependent Files

Step 4 – Print Report Cover Sheet

Step 5 – Publish SQR Report

Step 6 – Execute the SQR Program

The "Run" Button & the JF

Chapter 20 – Enhancing HTML Parameter Collection Forms

  • Text Fields
  • Static Choice
  • Dynamic Choice
  • Making "Static Choices" More Manageable
  • Constraining Dynamic Choices By User
  • The Multi-Language Twist
  • Providing "User Friendly" Dynamic Choices

Chapter 21 – Achieving Single Sign On

  • Job Factories (JF)
  • Three Sign On Possibilities for SQR Programs
  • Single Sign On – Level I
  • Single Sign On – Level II
  • Things to Consider

Chapter 22 – Interactive Master-Detail Reporting

  • Review of Functionality & Process Flow
  • Benefits of Master-Detail Reports
  • Steps to Implement Master-Detail Reports

Chapter 23 – Exception Reporting

  • Application Driven Exception Reporting
  • User Driven Exception Reporting
  • End Results & User Experience
  • SQR Modifications
  • Modifications to the SQR Program Attributes within Brio.Portal
  • User Options

Chapter 24 – Dynamic Image Files

  • Overview of Printing Image Files within SQR
  • Image File Assignment
  • All Image Files Loaded within Brio.Portal

Appendices

  1. Example of Sorting Arrays within an SQR Program
  2. Application Modification Impact Analysis Document
  3. Recording Report Execution Statistics
  4. Sample Programs & Database
  5. SDLC Planning Guide & Requirements Definition Questionnaire