Get Latest Updates directly delivered in your Email

Generate Excel Report From PeopleSoft SQR

Publish On December 3, 2014 In SQR

This blog post i will share & discuss about, how to create excel report using SQR. For generating excel files i have created a sample query to fetch from job data like employee id , name , business unit , location & dept name etc. This sample sqr is very easy to understand for producing excel report (.xls) file. Now let s explain the SQR.

On Begin-Program section i have called number of procedure i.e Init_Report which will initialize report by calling Stdapi-Init method. We all know what is the purpose of Stdapi-Init procedure, i’ll not discuss it here
Second thing is Init_Variables – I have used this procedure for variables and constant declaration. let say an example i have declare worksheet name and quote for dynamic sql creation.
Now let come across Main_Report procedure which is also number of procedure
If your program has any Run Control Page that take values from end user then you should remove comments from Get_Runcntl_Value procedure and do the necessary changes as per your requirements. Since i’m using any run control page for this hence i have commented out.

Most important thing is about Validate_RunCntl_Value procedure , we all know based on Operator ID ( Some time Called User ID ) & Run Control Id only one row will be pickup from Run Control Record hence it is always good idea to check using such numeric variables #Row_In_Run_Cntl_Table = 1 then i can built any dynamic sql or any other routing procedure etc. Follow this examples.

!Validate Run Control Value
    !If #Row_In_Run_Cntl_Table = 1
        ! To Make Dynamic Where clause according to input parameters.
        !IF NOT ISBLANK (LTRIM(RTRIM(&BUSINESS_UNIT,' '),' ')) 
        !    LET $WHERE = ' AND BUSINESS_UNIT = ' || $QUOTE || &BUSINESS_UNIT || $QUOTE
        !END-IF

        !IF NOT ISBLANK (LTRIM(RTRIM(&LOCATION,' '),' ')) 
        !        LET $WHERE = $WHERE || ' AND LOCATION = ' || $QUOTE || &LOCATION || $QUOTE
        !END-IF

Now lets go to Init_Excel_File – it is simply initialize and open the excel files for writing on Process Scheduler Output Directory then write rows one by one using SQR Write 1 From Syntax.
Write_XML_HTML_Code – Write XML tags into Excel , used for Excel schema in MS Office Excel 2003 format.
For Generate_Excel – Excel report creation is simple output the html tags & render into excel files ( .xls ) so Write_Excel_Header write html table column header. So you can use all html tags specially table attribute like rowspan colspan bgcolor etc.
Fetch_Excel_Data – Fetching data from job record and write column values Write_Excel_Data procedure

Write_Excel_Data – is Write data with table tr td tags, follow this examples.

!********************************
Begin-Procedure Write_Excel_Data
!********************************

        WRITE 1 FROM '<tr>'

        WRITE 1 FROM '<td align = left>' $EMPLID '</td>'
        WRITE 1 FROM '<td>' $NAME_DISPLAY
        Let $BU = $BusUnit
        Do Get-Business-Unit-Name
        WRITE 1 FROM '</td><td>' $BU_DESCR
            Let $DeptID = $DEPTID
            Let $AsOfDate = $AsOfToday
            DO Get-Department-Name
        WRITE 1 FROM '</td><td>' $DeptName
            Let $SetID = $SetIDJobCode
            Let $JobCode = $JOBCODE
            DO Get-Job-Title
        WRITE 1 FROM '</td><td>' $JobTitle

So Download the Code and register Process As SQR Report

SQRXL

Sample Excel Output

ExcelReport_4010

Process Registration Steps

Step 1

Step 2

Step 3 – Not Required,but used for Debugging purpose.

So Please let us know your view & suggestion. Hope this helps 🙂

Here is the Full Code

!-----------------------------------------------------------------------------------------------!
!  Report Name: SQRXL  -- Run Via Process Scheduler SQR Report (API Aware)                      !
!  Report Descriptions: Sample SQR Report Produce Excel                                         !
!  Report Create Date: 30-Nov-2014                                                              !
!  Report Run Component: PRCSMULTI ( System Process )                                           !
!  Written By - DBTutorBlog    https://www.dbtutor.com                                           !
!------------------------------------------------------------------------------------------------

#include 'setenv.sqc'       !Set environment
#define RecordLength  10000 !Set Record Length

!*****************
Begin-Program
!****************
     Do Init_Report
     Do Init_Variables
     Do Main_Report
     Do Stdapi-Term
End-Program


!*****************************
Begin-Procedure Init_Variables
!*****************************
        !Variable Init & Constant Declaration 
        Let $Quote = ''''    ! Used to make dynamic where caluse.
        Let $WorksheetName = 'Employee Report'
End-Procedure


!****************************
Begin-Procedure Init_Report
!****************************
     !Init Report & Load Lookup Section
     Do Stdapi-Init
     
End-Procedure


!***************************
Begin-Procedure Main_Report 
!***************************
    !Do Get_Runcntl_Value
    !Do Validate_RunCntl_Value
    Do Init_Excel_File
    Do Write_XML_HTML_Code
    Do Generate_Excel
    Do Close_HTML_Tag
End-Procedure


!*************************************
!Begin-Procedure Get_Runcntl_Value
!************************************
!Move 0 to #Row_In_Run_Cntl_Table
!Begin-Select On-Error=SQL-Error Loops=1 !Run Only 1 Time As RunControl is Key Field
!RC.SOA_RPT_TYPE_FLAG
!RC.COMPANY
!        
!        !Run Control Row Should be Only One
!        Add 1 to #Row_In_Run_Cntl_Table
!        
!        !Getting Run Control Value
!        Let $Soa_Rpt_Type_Flag  = rtrim(ltrim(&RC.SOA_RPT_TYPE_FLAG, ' '), ' ')
!        Let $Company  = rtrim(ltrim(&RC.COMPANY, ' '), ' ')
!        !----------- End

!FROM PS_SOA_RUNCNTL_TBL RC
!WHERE RC.OPRID = $prcs_oprid
!AND   RC.RUN_CNTL_ID = $prcs_run_cntl_id
!End-Select
!End-Procedure
  
!**************************************
!Begin-Procedure Validate_RunCntl_Value
!**************************************
    
    !Validate Run Control Value
    !If #Row_In_Run_Cntl_Table = 1
        ! To Make Dynamic Where clause according to input parameters.
        !IF NOT ISBLANK (LTRIM(RTRIM(&BUSINESS_UNIT,' '),' ')) 
        !    LET $WHERE = ' AND BUSINESS_UNIT = ' || $QUOTE || &BUSINESS_UNIT || $QUOTE
        !END-IF

        !IF NOT ISBLANK (LTRIM(RTRIM(&LOCATION,' '),' ')) 
        !        LET $WHERE = $WHERE || ' AND LOCATION = ' || $QUOTE || &LOCATION || $QUOTE
        !END-IF
    !else
        !Show 'This program must be run from the Process Monitor. with valid Run Control Value.'
        !Stop Quiet
    !End-If
    
!End-Procedure

!*********************************
Begin-Procedure Init_Excel_File
!*********************************
    
    !*************************************************************
    ! Now lets initialize the Excel File.
    ! We Will put Excel File to Process Scheduler Output Directory
    !*************************************************************
                                  
    Do Get-Prcsoutputdir      !** Procedure To Get The Path Of The Output Folder.
                                    
    If (rtrim($prcsoutputdir,' ') <> '')
        Let $GenerateNewFile = $prcsoutputdir || 'ExcelReport' || '_' || $prcs_process_instance || '.xls' !* The Output File Represented By the Variable $GenerateNewFile Is Suffixed With The Report Name And The Process Instance
    End-if
    
    !Open File For Writing From Line No - 1
    Open $GenerateNewFile as 1 for-writing record = {RecordLength}:vary status = #FileStatus
    
    !Checking For File Status
     if #FileStatus != 0
          display $GenerateNewFile noline
          display 'Unable to Write File'
     end-if
    
End-Procedure

!*********************************
Begin-Procedure Write_Excel_Header
!*********************************

        Write 1 From '<html xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/tr/REC-html40">'
        Write 1 From '<body>'
        Write 1 From '<table border="1">'
        Write 1 From ' '
        
        !Start Printing Top Excel Information, Specially Run Date Time & Run Control Values
        Write 1 From '<tr>'
        Write 1 From '<td class=tablehead colspan=6 align=center>Oracle PeopleSoft Enterprise</td>'
        Write 1 From '</tr>'

        Write 1 From '<tr>'
        Write 1 From '<td colspan=6 align=center>Employee Info Sheet</td>'
        Write 1 From '</tr>'

        Write 1 From '<tr><td colspan=6></td></tr>'
        
        Write 1 From '<tr>'
        Write 1 From '<td colspan=6 align=center>No INPUT PARAMETERS</td>'
        Write 1 From '</tr>'
        
        Write 1 From '<tr><td colspan=6></td></tr>'
        !End Printing Top Excel Information
        
        !Start Writing Column Header Information , Put your Column Name Here
        WRITE 1 FROM '<tr>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Emplid </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Name </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Business Unit </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Department </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Job Title </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Location </th>'
        WRITE 1 FROM '</tr>'
        !End Writing Column Header Information
            
End-Procedure 

!*******************************
Begin-Procedure Generate_Excel
!************/******************
    Do Write_Excel_Header
    Do Fetch_Excel_Data
    Do Write_Excel_Data
End-Procedure


!********************************
! Driving SQL 
!********************************
!********************************
Begin-Procedure Fetch_Excel_Data
!********************************
Begin-Select DISTINCT

JOB.EMPLID
NAMES.NAME_DISPLAY
JOB.DEPTID
JOB.BUSINESS_UNIT
JOB.JOBCODE
JOB.SETID_JOBCODE
JOB.LOCATION
JOB.SETID_LOCATION

    !**** Getting Column Value *****
    Let $EMPLID = &JOB.EMPLID
    Let $NAME_DISPLAY = &NAMES.NAME_DISPLAY
    Let $DEPTID = &JOB.DEPTID
    Let $BusUnit = &JOB.BUSINESS_UNIT
    Let $JOBCODE = &JOB.JOBCODE
    Let $SetIDJobCode = &JOB.SETID_JOBCODE
    Let $Location = &JOB.LOCATION
    Let $SetidLocation = &JOB.SETID_LOCATION
    !**************************************
    
    DO Write_Excel_Data     ! Calling another procedure to write details in excel file.
   
FROM PS_JOB JOB,  PS_PERSON_NAME NAMES 
  WHERE (JOB.EFFDT = 
        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED 
        WHERE JOB.EMPLID = A_ED.EMPLID 
          AND JOB.EMPL_RCD = A_ED.EMPL_RCD 
          AND A_ED.EFFDT <= $current-date)
    AND JOB.EFFSEQ = 
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES 
        WHERE JOB.EMPLID = A_ES.EMPLID 
          AND JOB.EMPL_RCD = A_ES.EMPL_RCD 
          AND JOB.EFFDT = A_ES.EFFDT) 
     AND JOB.EMPLID = NAMES.EMPLID)   
     AND JOB.EMPL_STATUS IN ('A','L','P','S')
![$WHERE]
ORDER BY JOB.EMPLID

End-select
End-Procedure


!********************************
Begin-Procedure Write_Excel_Data
!********************************

        WRITE 1 FROM '<tr>'

        WRITE 1 FROM '<td align = left>' $EMPLID '</td>'
        WRITE 1 FROM '<td>' $NAME_DISPLAY
        Let $BU = $BusUnit
        Do Get-Business-Unit-Name
        WRITE 1 FROM '</td><td>' $BU_DESCR
            Let $DeptID = $DEPTID
            Let $AsOfDate = $AsOfToday
            DO Get-Department-Name
        WRITE 1 FROM '</td><td>' $DeptName
            Let $SetID = $SetIDJobCode
            Let $JobCode = $JOBCODE
            DO Get-Job-Title
        WRITE 1 FROM '</td><td>' $JobTitle
            Let $Location = $Location
            Let $Setid = $SetidLocation
            Do Get-Location-Name
        WRITE 1 FROM '</td><td>' $LocationName '</td></tr>'

End-Procedure



!**********************************************************************************
! Open_HTML_Code procedure - To convert html to excel file and make it landscape
!**********************************************************************************
Begin-Procedure Write_XML_HTML_Code

        Write 1 From '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">'
        Write 1 From '<head>'
        Write 1 From '<style>'
        Write 1 From '    {mso-displayed-decimal-separator:"\.";'
        Write 1 From '    mso-displayed-thousand-separator:"\,";}'
        Write 1 From '@page'
        Write 1 From '    {margin:1.0in .75in 1.0in .75in;'
        Write 1 From '    mso-header-margin:.5in;'
        Write 1 From '    mso-footer-margin:.5in;'
        Write 1 From '    mso-page-orientation:landscape;}'
        Write 1 From '.tablehead{'
        Write 1 From 'white-space:nowrap;'
        Write 1 From 'font-family:Arial;'
        Write 1 From 'font-weight:bold;'
        Write 1 From 'font-Size:13px;'
        Write 1 From 'color:#333333;'
        Write 1 From 'background-color:#ECF1F4;'
        Write 1 From 'Padding:4px;'
        Write 1 From 'text-align:center;'
        Write 1 From 'border:1px solid #d1d1d1;}'
        Write 1 From '</style>'
        Write 1 From '<xml>'
        Write 1 From ' <x:ExcelWorkbook>'
        Write 1 From '  <x:ExcelWorksheets>'
        Write 1 From '   <x:ExcelWorksheet>'
        Write 1 From '      <x:Name>' $WorksheetName '</x:Name>'
        Write 1 From '    <x:WorksheetOptions>'
        Write 1 From '     <x:Print>'
        Write 1 From '      <x:ValidPrinterInfo/>'
        Write 1 From '      <x:HorizontalResolution>600</x:HorizontalResolution>'
        Write 1 From '      <x:VerticalResolution>0</x:VerticalResolution>'
        Write 1 From '     </x:Print>'
        Write 1 From '     <x:Selected/>'
        Write 1 From '     <x:DoNotDisplayGridlines/>'
        Write 1 From '    </x:WorksheetOptions>'
        Write 1 From '   </x:ExcelWorksheet>'
        Write 1 From '  </x:ExcelWorksheets>'
        Write 1 From '  <x:WindowHeight>8700</x:WindowHeight>'
        Write 1 From '  <x:WindowWidth>15195</x:WindowWidth>'
        Write 1 From '  <x:WindowTopX>0</x:WindowTopX>'
        Write 1 From '  <x:WindowTopY>120</x:WindowTopY>'
        Write 1 From '  <x:ProtectStructure>False</x:ProtectStructure>'
        Write 1 From '  <x:ProtectWindows>False</x:ProtectWindows>'
        Write 1 From ' </x:ExcelWorkbook>'
        Write 1 From '</xml>'

End-Procedure 


!*****************************
! Close HTML Tags
!***************************** 
Begin-Procedure Close_HTML_Tag
    
      Write 1 From '</table>'
      Write 1 From '</body>'
      Write 1 From '</html>'
      Close 1
      
End-Procedure 

!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'curdttim.sqc'   !Get-Current-DateTime procedure
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'datemath.sqc'   !Date Manipulation functions
#Include 'readxlat.sqc'   !Read Translate Table
#Include 'number.sqc'     !Routines to format numbers
#Include 'stdapi.sqc'     !Update Process API
#include 'getlogou.sqc'   !get current logoutput directory
#include 'getlocnm.sqc'   !Get Location Description
#include 'getjobtl.sqc'   !Get JobCode Title
#include 'getdptnm.sqc'   !Get Department Name
#include 'getbunam.sqc'   !Get Business Unit Name
#include 'getsetid.sqc'   !Get Setid for the BU and Record Name
!***********************************************************************

13 comments on “Generate Excel Report From PeopleSoft SQR”

tsds on December 24, 2019 at 4:47 am Said:

Thanks for sharing this article. Do you have an example where the excel output contain multiple tabs?

Thanks

Reply ↓
Hussain on November 12, 2016 at 9:01 pm Said:

How can we pass parameters? e.g. From Peoplesoft HCM or FSCM Web UI, prompt the user for starting and ending employee id and pass it to this report.
Can you please do a tutorial where calling an SQR report is added to the Peoplesoft menu (along with the parameter selection) so we can see the steps involved?

Reply ↓
prasanna ambrose on June 16, 2016 at 8:48 am Said:

Hello Nayan
Have you ever converted SQR Reports into BI Publisher Reports?

Is it hard? I have done BI Publisher not SQR Reports.

Wonder how hard this will be
please advise

Reply ↓
Jayesh on March 18, 2016 at 7:47 pm Said:

Hi Jayesh Here,
Can u plz help me with concatenate and show command.

I will display the sqr i have return plz suggest me the change. in show and concatenate

!********************************
Begin-Procedure Fetch_HDR
!********************************
Begin-SELECT

EMPLID &EMPLID
BIRTHDATE &BIRTHDATE
BIRTHPLACE &BIRTHPLACE
BIRTHCOUNTRY &BIRTHCOUNTRY
BIRTHSTATE &BIRTHSTATE
DT_OF_DEATH &DT_OF_DEATH
LAST_CHILD_UPDDTM &LAST_CHILD_UPDDTM

LET $EMPLID = &EMPLID
LET $BIRTHDATE = &BIRTHDATE
LET $BIRTHPLACE = &BIRTHPLACE
LET $BIRTHCOUNTRY = &BIRTHCOUNTRY
LET $BIRTHSTATE = &BIRTHSTATE
LET $DT_OF_DEATH= &DT_OF_DEATH
LET $LAST_CHILD_UPDDTM = &LAST_CHILD_UPDDTM

show &EMPLID ||’_’|| &BIRTHDATE ||’_’|| &BIRTHPLACE

FROM PS_Z_PERSON01

!DO Write_HDR ! Calling another procedure to write details in excel file.

End-SELECT
End-Procedure

!*****************************************************************************
!Procedure Name : Write-HDR
!*****************************************************************************
BEGIN-PROCEDURE WRITE-HDR

let $string = ‘&EMPLID’ || {comma} || ‘&BIRTHDATE’ || {comma} ||’&BIRTHPLACE’ !| {Comma} || ‘&BIRTHCOUNTRY’ || {Comma} || ‘&BIRTHSTATE’ || {Comma} || ‘&DT_OF_DEATH’ || {Comma} || ‘&LAST_CHILD_UPDDTM’

WRITE {output_txt_file_number} FROM
$string

END-PROCEDURE

I am gettin the foll ERRoR
Error on line 101:
(SQR 4305) Unknown option for SHOW.
show &EMPLID ||’_’|| &BIRTHDATE ||’_’|| &BIRTHPLACE

Errors were found in the program file.

SQR for PeopleSoft: Program Aborting.

Reply ↓
Pingback: PeopleSoft and Microsoft Excel – drgaff
Mónica on December 11, 2015 at 9:05 pm Said:
Hi Nayan,
Thanks for posting that code, very Helpfull …
Though I have a problem with a string field (inv_item_id) from inventory.
If I have 1E0000108 as an input data, when I generate the excel file it convert it to 1E+108.
I already tried to use the style
```
 mso-number-format:"\@";
```
```
  in the style section of the procedure Write-XML_HTML_Code, but did not work. 
Please, Do you have any idea how to solve this?

Thanks,
Mónica.-
```
Reply ↓
PPN on April 15, 2015 at 7:51 pm Said:

Nice article.
Could you tell me, how to generate separate tabs in the above report? My requirement is to generate a cross tabular excel report of job data for a list of employees such that each employee data is on a separate tab.
Till now, using your inputs above I am able to generate a report for a single employee.
Going by your example, suppose the Emplid, Name and Business Unit should be one tab and Dept, Jobcode and Location on another tab, how would this be done?

Thank you!

Reply ↓

Hello Nayan, thanks, very usefull, but at the end of the excel file I have twice the same line.

Reply ↓

Hi Just comment out Do Write_Excel_Data in this procedure

!*******************************
Begin-Procedure Generate_Excel
!************/******************
    Do Write_Excel_Header
    Do Fetch_Excel_Data
    Do Write_Excel_Data
End-Procedure

It should be

!*******************************
Begin-Procedure Generate_Excel
!************/******************
    Do Write_Excel_Header
    Do Fetch_Excel_Data
    !Do Write_Excel_Data
End-Procedure

Reply ↓

ranjini on January 16, 2015 at 5:23 pm Said:

its working now 🙂 (y)
thanks a lot Nayan 🙂

Reply ↓

will this preserve zero’s… for example i want to write emplid as 001872145..will the zeros be preserved?

Reply ↓

Hi Ranjini,

it think it will not preserve leading zero like 01254 .. as because it delivered excel property.
By the way you can use this following technique that will preserve leading zero.

 Let $Some_Variables = '="' || &PN.NATIONAL_ID || '"' !Excel Remove Leading Zero.

Here &PN.NATIONAL_ID data fetch from database.

So use this way

 '="'||value_u_want_pass||'"'

Please let me know if works & like our Facebook page for daily updated.

Reply ↓

hi Nayan,
thanks for your reply.still it’s not working..
here is my program

 Begin-Program
	let $comma = ','
	let #count=0
	Do Open-File-For-Writing
End-Program

Begin-Procedure Write_Header
Write 1 from
  'EMPL ID '
  $comma
   'NAME'
  $comma
  'DEPT ID'
  $comma
 'DEPT NAME'
End-Procedure


Begin-Procedure  Open-File-For-Writing
let $file ='D:\asgs\pgm4f.csv'
open $file as 1 for-writing Record = 5000:Vary Status = #Open-Status
do Write_Header
do Fetch_from_Table
add 1 to #count
Close 1
End-Procedure Open-File-For-Writing

Begin-Procedure Fetch_from_Table
Begin-Select
A.id 
A.name
B.deptid
B.dname
	do Print_Data
From rm_tbl A , rm_tbl2 B
Where	A.deptid=B.deptid
End-Select
End-Procedure

Begin-Procedure Print_Data
!let $id=&A.id
$id='="'||&A.id||'"'
Write 1 from 
$id $comma
&A.name $comma
&B.deptid $comma
&B.dname
End-Procedure

{ DBTutor }

Tips for Those Who Want to Learn

Generate Excel Report From PeopleSoft SQR

So Download the Code and register Process As SQR Report

Sample Excel Output

Process Registration Steps

Here is the Full Code

13 comments on “Generate Excel Report From PeopleSoft SQR”

Leave a Reply Cancel reply