structured query reporting
TRANSCRIPT
-
8/6/2019 Structured Query Reporting
1/21
STRUCTURED QUERY REPORTING
ADVANTAGES
Used for Reporting.
Used for background Sql processing
Used to migrate/ move data from legacy system to PeopleSoft system.
Used to perform file integration.
Used where programming logic is more.
This can be used as both GUI and CUI(character use interface)( GUI issupported only till 7.5 version, it is also called work bench).
COMMANDS IN SQR
They are of three types
1. Sections
2. Paragraphs
3. Other commands
1. SECTIONS
a. BEGIN-SETUP
b. BEGIN HEADING
c. BEGIN-FOOTING
d. BEGIN-PROGRAM
e. BEGIN-PROCEDURE
a. BEGIN-SETUP:
HEADING
DATA/BODY
FOOTING
SQR page
-
8/6/2019 Structured Query Reporting
2/21
This is not mandatory
This is used to perform output page setup like page size,
margins, font size etc.
We can have only 1 setup section in SQR program
The page setup done will be applied to all output pages.
Syntax:
// THESE ARE SPACE SENSITIVE.THE CODE WILL BE TYPED IN NOTEPAD
OR WORDPAD AND SAVED AS .SQRBEGIN-SETUP
OTHER COMMANDS
END-SETUP
b. BEGIN-HEADING
This is not a mandatory section.
This is used to design heading region of O/p page.
We can have only one heading region.
The heading design will be applied to all the O/P pages.
Syntax:
BEGIN-HEADING #
// THESE ARE SPACE
SENSITIVE.THE CODE WILL BETYPED IN NOTEPAD OR
WORDPAD AND SAVED AS
.SQR
# IS USED TO SPECIFY NO.OF
LINES REQUIRED FOR
HEADING REGION.
-
8/6/2019 Structured Query Reporting
3/21
PRINT COMMANDS
END-HEADING
c. BEGIN-FOOTING
This is not a mandatory section.
This is used to design footing region.
We can have only 1 footing section.
The footing design will be applied to all the O/P pages.
Syntax:
BEGIN-FOOTING #
PRINT COMMANDS
END- FOOTING
d. BEGIN-PROGRAMM
This is a mandatory section in SQL program.
This is used to design data region of O/p page.
We can have only program section in SQR program
The SQR program execution starts from program section.
Syntax:
-
8/6/2019 Structured Query Reporting
4/21
BEGIN-PROGRAM/REPORT
OTHER COMMANDS
END-PROGRAM
e. BEGIN-PROCEDURE
This is not a mandatory section.
This is used to design data or body region on O/p page.
We can have any number of procedures
Every procedure has unique procedure name.
Procedures are functions in SQR which can be re-used.
We do not write actual coding in program section, instead we
write it in procedure section & call them in program section.
Syntax:
BEGIN-PROCEDURE
OTHER COMMANDS
END-PROCEDURE []
2. PARAGRAPHS
a. BEGIN-SELECT
-
8/6/2019 Structured Query Reporting
5/21
This is used to retrieve data from database which is
required to generate output.
This is used to implement for looping in SQR programs.
Syntax
BEGIN-SELECT
Field1
.
From
[where]
[group by[]]
[order by]
END-SELECT
B. BEGIN-SQL
This is used to execute all SQL statements like DML,DDL,DCL
statements except select.
Syntax
BEGIN-SQL
SQL-STATEMENT
END-SQL
c. BEGIN-DOCUMENT
IF THERE MULTIPLE SQL-
STATEMENTS, USING ; AT
END OF EVERY STATEMENT.
-
8/6/2019 Structured Query Reporting
6/21
This is used to combine static text with data from database.(EG:- while
giving offer letter or any greetings, the text is same , only the name
changes.)
Syntax
BEGIN-DOCUMENT (1,1)
OTHER COMMANDS
END-COMMANDS
SQR PROGRAMMING STRUCTURE
OTHER COMMANDS.
i) PRINT-COMMAND
This is mandatory for any SQR report.
They are of two types
Implicit: no commands required, used for printingstatic text.
Explicit: print command is required.
Formatting options
SECTIONS
PARAGRAP
OTHER
OTHER
Bold - Center - Underline
Box - Shape - Wrap
On-break
edit ( for specifying multiple formatting
options)
-
8/6/2019 Structured Query Reporting
7/21
ON-BREAK
Used for redundancy of printing
Group headings
Group footings
Options: change,top-page,never,always,before,after,save
ON-BREAK = NEVER ( NEVER CHANGE THE VALUE)
= ALWAYS(PRINT VALUE ALWAYS)
= AFTER (USED FOR GROUP FOOTING)
SYNTAX:
IMPLICIT PRINT
&([]
EXPLICIT PRINT
PRINT ([])
()
EG: PRINT DETAIL (1,20)
ON-BREAK:
REDUNDANCY OF PRINITNG
& COUNTRY(1,1) ON-BREAK
&CITY (1,5) ON-BREAK
& EMPID(1,7)
-
8/6/2019 Structured Query Reporting
8/21
DIFFERENT FILE TYPES
.SQR -> SOURCE CODE FILE (EXECUTABLE FILE)
.SQT-> INTERMEDIATE FILE
.LIS-> LIST FILE o/p FILE
.SQC-> FUNCTION LIBRARY FILE
.SPF-> PORTABLE FILE FORMAT
.INI-> INITIALIZATION/CONFIGURATION
.MAX-> MAXIMAZATION FILE( INCREASE BUFFER FILE)
.HTML
.PDF O/P FILE FORMATS
.CSV
.ERR -> ERROR FILE
.LOG-> LOG FILE
DATA TYPES
- TEXT/ CHARACTER
- DATE
- NUMBER -> INTEGER, DECIMAL
VARIABLES
- COLUMN VARIABLES :
These are columns in database ie, fields from database tables.
These variable names will be staring with &
SQR VARIABLES :
-
8/6/2019 Structured Query Reporting
9/21
o USER DEFINED VARIABLES
o These are program specific variables
o Text values $
o Numeric variables #
o Date variables $
Reserved variables
o System variables
o Variable name consists ofhyphen -
o Developer cannot use these names for user defined variables.
Document markers
o These are used only in BEGIN-DOCUMENT paragraph.
o These variable names start with @.
Substitution variables
oIn these variables, values are stored at start of program and
remain till end of program.
oThe variables are enclosed in { }
Scope of variable
Global ( default scope is global)
Local
-
8/6/2019 Structured Query Reporting
10/21
TYPES OF RECORDS
TABULAR REPORTS: This is used to generate report from a single table.
MASTER DETAIL REPORT: This is to generate reports from more than one
table.
FORM REPORTS: In this report, for each and every row we will have a
separate output page.
EG: PAY SLIPS
MAILING LABELS: In this we partition the output page vertically and
generate an output .
EG: ADRESS PRINITNG
LETTERS AND DOCUMENTS: In this report we combine static text. With the
column variables.
EG: OFFER LETTERS AND RELIEVING LETTERS
CROSS TABULAR REPORTS: In this report we perform both row wise as well
as column wise calculations.
EG: FINDING ALL THE TOTALS IN BELOW TABLE
TOTAL
TA DA SALARY
TOTAL TOTAL TOTAL TOTAL
-
8/6/2019 Structured Query Reporting
11/21
GRAPHICAL REPORTS: In this report, we represent the data in a graphical
format.
THE EXECUTABLE FILE TO RUN AN sqr PROGRAM IS sqrw.EXE.
THIS IS LOCATED AT
:\PEOPLETOOLS
8.X\bin\sqr\\BINW\sqrw.exe
Simple program
BEGIN-PROGRAM
PRINT HELLO (3,10) BOLD BOX
END-PROGRAM
! Tabular report
! This report gets the data from employee table and generates output for all
employees.
BEGIN - HEADING 2
PRINT Employee detail report (1, 20) BOLD BOX
PRINT Employee id (2, 1) BOLD UNDERLINE
PRINT EmpName (2, 15) BOLD UNDERLINE
PRINT Country (2, 60) BOLD UNDERLINE
PRINT Salary (2, 70) BOLD UNDERLINE
-
8/6/2019 Structured Query Reporting
12/21
END-HEADING
BEGIN - FOOTING 2
PRINT ** Confidential information** (2, 10) BOLD
END - FOOTING
! This is main section which calls tabular procedures.
BEGIN - PROGRAM
DO TABULAR
END - PROGRAM
! This is a procedure to get the data and print
BEGIN - PROCEDURE
BEGIN -SELECT
EMPLID (+1,1) ! +1: AUTO INCREMENTS ROWS
NAME ( ,15)
COUNTRY ( ,60)
COMPRATE ( ,70)
FROM PS_EMPLOYEES
END - SELECT
END PROCEDURE
-
8/6/2019 Structured Query Reporting
13/21
MASTER DETAILS REPORT
1. USING JOINS
2. USING PROCEDURES
3. USING LOAD LOOKUP
USED TO GENERATE REPORTS FROM MULTIPLE TABLES.
!** master detail report using joins
BEGIN-PROGRAM
DO MAIN ! CALLING PROCEDURE
END-PROGRAM
BEGIN - PROCEDURE MAIN ! MAIN IS PROCEDURES NAME
BEGIN - SELECT
A. EMPLID (+1, 1)
A. BIRTHDATE ( ,10)
B. EMPLID ! IT WILL BE STORED IN BUFFER BUT WILL NOT BE PRINTED
B. COUNTRY ( ,55)
FROM PS_PERSON A, PS_ADDRESS B
WHERE A.EMPLID= B.EMPLID
AND B.ADDRESS-TYPE=HOME
END - SELECT
END - PROCEDURE MAIN
NEW Commands used in the above two programs:
-
8/6/2019 Structured Query Reporting
14/21
DO COMMAND- This is used to call a program.
Syntax: DO
COMMENTING: using ! we can write single line comments.
Syntax:!
MOVE- This is used to move the data from one variable to the other.
This is also used for typecasting
# DEFINE- used to define the substitute variables.
Syntax: # DEFINE
LET- this is used to assign values to variables.
This is used to perform arithmetic or string operations.
-
8/6/2019 Structured Query Reporting
15/21
!** Master detail report using global variables
BEGIN - PROGRAM
DO GBL_VAR
END - PROGRAM
BEGIN - PROCEDURE GBL_VAR
BEGIN - SELECT
A. EMPLID(+1,1)
B. BIRTHDATE ( ,10)
MOVE &A.EMPLID TO $EMPLID !$EMPLID IS USER DEFINED
VARIABLE NAME
DO ADDR_DET
FROM PS_PERSON A
END - SELECT
END - PROCEDURE
BEGIN - PROCEDURE ADDR_DET
BEGIN - SELECT
B.EMPLID
B.CITY (+1, 25)
B.COUNTRY ( , 60)
FROM PS-ADDRESS B
WHERE B.EMPLID=$EMPLID
AND B.ADDRESS-TYPE=HOME
-
8/6/2019 Structured Query Reporting
16/21
END - SELECT
END - PROCEDURE
!**Master detail report using local variables
BEGIN-PROGRAM
DO MAIN
END-PROGRAM
BEGIN - PROCEDURE MAIN
BEGIN - SELECT
A. EMPLID (+1,1)
A. BIRTHDATE ( ,15)
DO EMPL_DET (&A. EMPLID) ! PASSING ARGUMENT TO $ EID INEMP_DET PROCEDURE
FROM PS_PERSON A
END - SELECT
END - PROCEDURE
BEGIN - PROCEDURE EMPL_DET ($ EID)
BEGIN - SELECT
B.EMPLID
B.CITY (+1, 40)
-
8/6/2019 Structured Query Reporting
17/21
B.COUNTRY ( , 60)
FROM PS_ADDRESS B
WHERE B.EMPLID=$EID
AND B.ADDRESS-TYPE=HOME
END - SELECT
END PROCEDURE
-
8/6/2019 Structured Query Reporting
18/21
FORM REPORTS
! ** This is a form report**
BEGIN - HEADING 1
PRINT FORM REPORT (1, 20) BOLD BOX
END- HEADING
BEGIN-PROGRAM
DO FORM_REPORT
END-PROGRAM
BEGIN - PROCEDURE FORM_REPORT
BEGIN - SELECT
EMPLID
NAME
MONTHLY_RT
MOVE &EMPLID T0 $SETID
MOVE &NAME TO $ENAME
MOVE &MONTHLY_RT TO $ SALARY
DO PRINTING
FROM PS_EMPLOYEES
END - SELECT
END - PROCEDURE
BEGIN - PROCEDURE PRINTING
PRINT EMPLID : (1 ,1)
-
8/6/2019 Structured Query Reporting
19/21
PRINT NAME : (3, 1)
PRINT SALARY : (5, 1)
PRINT $EID (1, 40)
PRINT $SALARY (5, 40)
NEW - PAGE
END - PROCEDURE
-
8/6/2019 Structured Query Reporting
20/21
MAILING LABELS
#DEFINE MAX_LABEL_LINES 10
#DEFINE LINES_BETWEEN_LABELS 3
BEGIN-PROGRAM
DO LABELS
END-PROGRAM
BEGIN - PROCEDURE LABELS
LET #LABEL-COUNT=0
LET #LABEL-LINES=0
COLUMNS 1 30 60
BEGIN - SELECT
EMPLID (1, 1, 29)
ADDRESS1 (2, 1, 29)
ADDRESS2 (3, 1, 29)
ADDRESS3
ADDRESS4
CITY
STATE
COUNTRY
LET $ADDR-LINE=&ADDRESS3 ll , ll &ADDRESS4
LET $LASTLINE= &CITY ll , ll&STATE ll , ll &COUNTRY
-
8/6/2019 Structured Query Reporting
21/21
PRINT $ADDR-LINE (4, 1, 29)
PRINT $LASTLINE (5, 1, 29)
NEXT-COLUMN AT-END=NEWLINE
ADD 1 TO #LABEL-COUNT
IF #CURRENT-COLUMN=1
ADD 1 TO LABEL-LINES
IF #LABEL-LINES ={MAX-LABEL-LINES}
NEW-PAGE
LET #LABEL-LINES=0
ELSE
NEXT-LISTING NO-ADVANCE SKIPLINES={LINES-BETWEEN-LABELS}
END-IF
FROM PS-ADDRESSES
WHERE ADDRESS-TYPE= HOME
END - SELECT
USE COLUMN=0
NEW-PAGE
PRINT TOTAL LABELS PRINTED : (+1, 1)
PRINT #LABEL-COUNT ( ,30)
END - PROCEDURE