Multiple reports in sqr

Requirement :

1.       Develop a SQR report to Generate Hire/Terminate report.  Single SQR should generate 2 reports. Process has to pickup From Date and To date as runcontrol parameters and generate report.

Report Heading – Should be

                Employee Hire Information (Center)

                From Date                                                                           Report Rundatetime

                To Date

                Logo of Company at Right Corner(Envytee).

Report Footing – Should be

                Page Number 1ofn

this is the SQR

#include 'setenv.sqc'      !Set Enviroment Values
#Include 'setup32.sqc'     !Printer and page-size initialization (Landscape)

!**********************    B E G I N   P R O G R A M    **********************!
!********************************************************

Begin-Setup
!***********************
Declare-Layout employee_active
    Left-Margin=1
End-Declare
Declare-Layout emp_Terminated
    Orientation=Landscape
    Left-Margin=0.3
End-Declare
Declare-Report employee_active
    Layout=employee_active
End-Declare
Declare-Report emp_Terminated
    Layout=emp_Terminated
End-Declare
Declare-Image companylogo
Type=BMP-FILE
Source='C:\Documents and Settings\Administrator\Desktop\envyteelogo.BMP'
End-Declare
End-Setup
!**********************************************************

BEGIN-PROGRAM

 do Init-DateTime
 do Init-Number
 do Get-Current-DateTime
 do Stdapi-Init
    if $prcs_process_instance = ''
      input $from_date 'Enter From Date' type = date format = 'YYYYMMDD'
      input $to_date 'Enter To Date' type = date format = 'YYYYMMDD'
   else
 do Select-Parameters
end-if

 do Process-Main
 do process-term
 do Stdapi-Term
end-program



!*****************************************************************************!

!*****************************************************************************!
BEGIN-HEADING 9  For-Reports=(employee_active)
  let $ReportID         = 'Rama'
  let $ReportTitle      = 'HI - Starters Report'
 #include 'stdhdg01.sqc'
SET-COLOR PRINT-TEXT-BACKGROUND = (255,0,255)
   print 'employees who are working' (3) center
Let #Image_Length = 7
Let #Image_Height = 3
!Print 'company logo' (3,20)
Print-Image companylogo (3,1)
Image-Size=(#Image_Length,#Image_Height)
Let #Curr_Line_Adj = #Image_Height +3
   print 'From Date:'        (5,1)
   print $from_date            (5,13) edit 'DD_MM-YYYY'
   print 'To Date:  '        (6,1)
   print $to_date            (6,13)  edit 'DD-MM-YYYY'
   print 'EMPID' (8,1)
   print 'NAME'   (,10)
   print 'SALARY' (,28)
   print 'HIRE DATE'  (,33)
   print 'Action' (,48)
   print 'Sl.No' (,55)
SET-COLOR PRINT-TEXT-BACKGROUND = (255,255,255)
   !print '--------' (7,1) FILL
   !GRAPHIC () FONT  4  8.5
!GRAPHIC (1,30,11) HORZ-LINE 8

END-HEADING
!*****************************************************************
BEGIN-HEADING 9  For-Reports=(emp_Terminated)
let $ReportID         = 'Rama'
  let $ReportTitle      = 'HI - Starters Report'
 #include 'stdhdg01.sqc'
SET-COLOR PRINT-TEXT-BACKGROUND = (255,0,255)
   print 'employees who are terminated' (3) center
Let #Image_Length = 7
Let #Image_Height = 3
!Print 'company logo' (3,20)
Print-Image companylogo (3,1)
Image-Size=(#Image_Length,#Image_Height)
Let #Curr_Line_Adj = #Image_Height +3
   print 'From Date:'        (5,1)
   print $from_date            (5,13) edit 'DD_MM-YYYY'
   print 'To Date:  '        (6,1)
   print $to_date            (6,13)  edit 'DD-MM-YYYY'
   print 'EMPID' (8,1)
   print 'NAME'   (,10)
   print 'SALARY' (,28)
   print 'TERMINATION_DT'  (,34)
print 'Action' (,48)
  print 'Sl.No' (,55)
SET-COLOR PRINT-TEXT-BACKGROUND = (255,255,255)
   !print '--------' (7,1) FILL
!GRAPHIC () FONT  4  8.5
!GRAPHIC (1,30,11) HORZ-LINE 8


END-HEADING
!******************************************************************
begin-footing 4 For-Reports = (employee_active)
! print "Page n of m" in the footing
page-number (1,50) 'Page '
last-page () ' of '
end-footing
!******************************************************************************!
begin-footing 4 For-Reports = (emp_Terminated)
! print "Page n of m" in the footing
page-number (1,50) 'Page '
last-page () ' of '
end-footing
!*****************************************************************************!
BEGIN-PROCEDURE Select-Parameters

Begin-Select
RC.FROMDATE
RC.THRUDATE
   
    let $from_date = &RC.FROMDATE
    let $to_date = &RC.THRUDATE

FROM PS_SP_RUNCNTL_REC RC
WHERE RC.OPRID           = $prcs_oprid
  AND RC.RUN_CNTL_ID     = $prcs_run_cntl_id
End-Select

END-PROCEDURE
!*****************************************************************************!
!to control the in put parameters if the report is run by sqrw or by process-scheduler
!*****************************************************************************!
BEGIN-PROCEDURE Process-Main

let #data_row_count = 0
USE-REPORT employee_active
begin-select DISTINCT
P.EMPLID  (+1,1)
P.NAME_FORMAL       (,10,17)
J.MONTHLY_RT        (,28,4)
J.HIRE_DT           (,34,12)
J.ACTION         (,48)

     let #data_row_count = #data_row_count + 1 ! increment when a row is found
     print #data_row_count (,55,4)
       FROM PS_PERSONAL_DATA P,PS_JOB J
       WHERE P.EMPLID = J.EMPLID
and J.ACTION='HIR'
      AND   J.HIRE_DT between $from_date and $to_date
end-select
if #data_row_count = 0
    print ' **************  No Data Found  ************** '        (+2, 60)
   end-if
end-procedure ! list_customers
!*********************************************************************************
begin-procedure process-term
let #data_row_count = 0
Use-Report emp_Terminated
begin-select DISTINCT
P1.EMPLID  (+1,1)
P1.NAME_FORMAL       (,10,17)
J1.MONTHLY_RT        (,28,4)
J1.TERMINATION_DT           (,34,12)
J1.ACTION         (,48)


     let #data_row_count = #data_row_count + 1 ! increment when a row is found
     print #data_row_count (,55,4)
       FROM PS_PERSONAL_DATA P1,PS_JOB J1
       WHERE P1.EMPLID = J1.EMPLID
and J1.ACTION='TER'
      AND   J1.TERMINATION_DT between $from_date and $to_date
end-select
if #data_row_count = 0
    print ' **************  No Data Found  ************** '        (+2, 60)
   end-if
end-procedure
 
!********************************************************************************!
#include 'reset.sqc'            ! Reset printer
#include 'stdapi.sqc'    ! UPDATE PROCESS API
#include 'curdttim.sqc'         ! Current Date and Time
#Include 'datetime.sqc'  !Routines for date and time formatting
#Include 'number.sqc'    !Routines to format numbers
#include 'prcsapi.sqc'   !Update Process Request API
#include 'prcsdef.sqc'   !Update Process Request variable declaration