Categories
SQR

Generate Multiple Report with Unique Name in PeopleSoft SQR

From last couple of days i was working on SQR Master Details report and also contains requirements to create a more than one report simultaneously based on the common data.After searching on Peoplebook as well Hyperion SQR Manual i found the solutions. So, today i’m sharing the solutions how to proceed it.

I’m describing this blog post pretty elaborate way so you will get fully understand. 🙂

How can we create multiple reports?

  •     Use DECLARE-REPORT to declare one or more reports to be produced in the application.
  •     You must use this command when developing applications to produce more than one report.
  •     This DECLARE-REPORT Command should be use in the SETUP i.e Begin-Setup section.
  •     It includes name of the Report/Layout/Printer.
  •     The name of the Layout and type of the report may different  that depends on the business requirement.
  •     If no printer type is specified, the default is LINEPRINTER.
  •     if no Layout is specified, the default Layout is used.
  •     Valid values for the Printer-Type arguments are HT-HTML, HP-HPLASERJET,PS-POSTSCRIPT, LP-LINEPRINTER

So Declare-Report Command Looke Like

 DECLARE-REPORT report_name

[TOC=toc_name]

[LAYOUT=layout_name]

[PRINTER-TYPE=printer_type]

END-DECLARE

Arguments

report_name

The report name.

TOC

The name of the Table of Contents.

LAYOUT

The layout name. If none is specified, the default layout is used.

PRINTER-TYPE

The type of printer used. If none is specified, the default is the LINEPRINTER. If no DECLARE‑PRINTER is specified, DEFAULT-LP is used. Valid values for PRINTER-TYPE are HT, HP, PD, PS, LP, HTML, HPLASERJET, POSTSCRIPT, and LINEPRINTER.

So first i am declaring the layout like that

 !DECLARE-LAYOUT should be usd in SETUP section.
    DECLARE-LAYOUT RPT_LAYOUT_A
		paper-size={PAGE_PAPER_SIZE}
		orientation = LANDSCAPE
		line-height=9
		char-width=2
    END-DECLARE 

then create the report defination with DECLARE-REPORT command like that and use the previously defined Layout Name RPT_LAYOUT_A

 !DECLARE-REPORT produced one or more reports and should be use in the SETUP section.
	DECLARE-REPORT RPT_A
		LAYOUT=RPT_LAYOUT_A
	END-DECLARE

As you can see the below whole snippets i have use 5 Layout along with 5 Report. So i general terms this MULRPT.SQR will create 5 output in Process Output Directory.

Process Output Directory Generate 5 Reports
Process Output Directory Generate 5 Reports

So next process i have created Different Heading Section for all reports as it is required.

 
!**************
! For Report A
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_A)
	PRINT 'This is Report A' (1) CENTER
END-HEADING

!**************
! For Report B
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_B)
  PRINT 'This is Report B' (1) CENTER
END-HEADING

!**************
! For Report C
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_C)
  PRINT 'This is Report C' (1) CENTER
END-HEADING

But i have use Common Footer for all

 !**** Common Footer all Report *****************
BEGIN-FOOTING 3 FOR-REPORTS=(RPT_A,RPT_B,RPT_C,RPT_D,RPT_E)
	ALTER-PRINTER
		FONT = 3
		POINT-SIZE =13
    PAGE-NUMBER (1,1) 'Page - '
END-FOOTING

RPT_A,RPT_B,RPT_C,RPT_D,RPT_E Use Same Footer.

So here is the most important section. I’m using getlogou.sqc to get Process Output directory and making new file name with some naming conventions that i want to see in process output directory, then use USE-REPORT for using the report, write some text on that and finally Using New-Report command To close the current report output file and open a new one with the specified file name.

 Do get-logoutputdir-value !Get Process Output Directory
		
		if (rtrim($prcsoutputdir,' ') <> '')
			
			Let $NewFileFileSuffixValue = edit($AsOfToday,'DDMonthYYYY')
			
			Let $GenNewFileA = $prcsoutputdir || 'Report_A' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileB = $prcsoutputdir || 'Report_B' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileC = $prcsoutputdir || 'Report_C' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileD = $prcsoutputdir || 'Report_D' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileE = $prcsoutputdir || 'Report_E' || '_' || $NewFileFileSuffixValue || '.pdf'
			
			Use-Report RPT_A
			
			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileA
			PRINT 'This is the A Multiple Report' (1,1)

Same thing happening for all others reports.

Process Defination
Process Defination
Server Selection
Server Selection
Most Important: You need to specified at Override Tab, else multiple report will not gerenate
Most Important: You need to specified at Override Tab, else multiple report will not gerenate

So here is the Whole Code Snippet, Just Download it and put it to sqr folder i.e PS_HOME/sqr then Create Proess Name MULRPT.SQR as SQR Report

Please Put -MR 5 at the Override Options Tabs in *Parameter List: (Append )
*Parameter List Append -MR 5

See the results in process Monitor.

Please let me know if any problems , suggestion and correction & Hope this help you 🙂

 
!-----------------------------------------------------------------------------------------------!
!  Report Name:  MULRPT.SQR -- Run Via Process Schedular SQR Report (API Aware)                 !
!  Report Descriptions: Generate Multiple Reports with Unique Name in SQR			!
!  Report Create Date: 	4-Oct-2013								!
!  Report Run Component: PRCSMULTI								!
!  Report Process Group: HRALL									!	
!  Note: To make the process scheduler aware of multiple reports in the Parameter section of 	!
!        Process Scheduler specify "-MR n" without quotes. Here, MR indicates 			!
!		 Multiple Reports and 'n' is a number between 2 to 99 ( limit upto 99 )		!
!		 indicating to the  Process Scheduler the number                                !
!                of reports generated by the SQR report.                                        !
!-----------------------------------------------------------------------------------------------!

#include 'setenv.sqc'    ! set Default environment
#define PAGE_PAPER_SIZE (A4)

Begin-Setup

    !DECLARE-LAYOUT should be usd in SETUP section.
    DECLARE-LAYOUT RPT_LAYOUT_A
		paper-size={PAGE_PAPER_SIZE}
		orientation = LANDSCAPE
		line-height=9
		char-width=2
    END-DECLARE

    DECLARE-LAYOUT RPT_LAYOUT_B
		paper-size={PAGE_PAPER_SIZE}
		orientation = LANDSCAPE
		line-height=9
		char-width=2
    END-DECLARE

    DECLARE-LAYOUT RPT_LAYOUT_C
      paper-size={PAGE_PAPER_SIZE}
	  orientation = LANDSCAPE
	  line-height=9
	  char-width=2
	END-DECLARE

	DECLARE-LAYOUT RPT_LAYOUT_D
      paper-size={PAGE_PAPER_SIZE}
	  	orientation = LANDSCAPE
		line-height=9
		char-width=2
	END-DECLARE

	DECLARE-LAYOUT RPT_LAYOUT_E
      paper-size={PAGE_PAPER_SIZE}
	  orientation = LANDSCAPE
	  line-height=9
	  char-width=2
	END-DECLARE

	!DECLARE-REPORT produced one or more reports and should be use in the SETUP section.
	DECLARE-REPORT RPT_A
		LAYOUT=RPT_LAYOUT_A
	END-DECLARE

	DECLARE-REPORT RPT_B
		LAYOUT=RPT_LAYOUT_B
	END-DECLARE

	DECLARE-REPORT RPT_C
		LAYOUT=RPT_LAYOUT_C
	END-DECLARE

	DECLARE-REPORT RPT_D
		LAYOUT=RPT_LAYOUT_D
	END-DECLARE

	DECLARE-REPORT RPT_E
		LAYOUT=RPT_LAYOUT_E
	END-DECLARE

END-SETUP

!*****************************************
! Header & Footer Declation for All Report
!*****************************************

!**************
! For Report A
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_A)
	PRINT 'This is Report A' (1) CENTER
END-HEADING

!**************
! For Report B
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_B)
  PRINT 'This is Report B' (1) CENTER
END-HEADING

!**************
! For Report C
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_C)
  PRINT 'This is Report C' (1) CENTER
END-HEADING

!**************
! For Report D
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_D)
  PRINT 'This is Report D' (1) CENTER
END-HEADING

!**************
! For Report E
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_E)
  PRINT 'This is Report E' (1) CENTER
END-HEADING

!**** Common Footer all Report *****************
BEGIN-FOOTING 3 FOR-REPORTS=(RPT_A,RPT_B,RPT_C,RPT_D,RPT_E)
	ALTER-PRINTER
		FONT = 3
		POINT-SIZE =13
    PAGE-NUMBER (1,1) 'Page - '
END-FOOTING

!***** Heading & Footing Section Completed ************

!*****************
Begin-Program
!****************
	 do Init_Report
	 do Main_Report
	 do Stdapi-Term
end-program

!****************************
Begin-Procedure Init_Report
!****************************
	 Do Stdapi-Init
	 Do Init-DateTime
	 Do Init-Number
	 Do Get-Current-DateTime
end-procedure

!*****************
Begin-Procedure Main_Report
!****************

		Do get-logoutputdir-value !Get Process Output Directory

		if (rtrim($prcsoutputdir,' ') <> '')

			Let $NewFileFileSuffixValue = edit($AsOfToday,'DDMonthYYYY')

			Let $GenNewFileA = $prcsoutputdir || 'Report_A' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileB = $prcsoutputdir || 'Report_B' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileC = $prcsoutputdir || 'Report_C' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileD = $prcsoutputdir || 'Report_D' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileE = $prcsoutputdir || 'Report_E' || '_' || $NewFileFileSuffixValue || '.pdf'

			Use-Report RPT_A

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileA
			PRINT 'This is the A Multiple Report' (1,1)

			Use-Report RPT_B

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileB
			PRINT 'This is the B Multiple Report' (1,1)

			Use-Report RPT_C

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileC
			PRINT 'This is the C Multiple Report' (1,1)

			Use-Report RPT_D

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileD
			PRINT 'This is the D Multiple Report' (1,1)

			Use-Report RPT_E

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileE
			PRINT 'This is the E Multiple Report' (1,1)

		end-if

end-procedure

!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'curdttim.sqc'   !Get-Current-DateTime procedure
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'number.sqc'     !Routines to format numbers
#Include 'stdapi.sqc'     !Update Process API
#Include 'datemath.sqc'   !Date Manipulation functions
#Include 'timemath.sqc'   !Time Related Calculatin
#include 'getlogou.sqc'   !get current logoutput directory
#include 'reset.sqc'      !Reset Printer Show End of Report
!***********************************************************************
Categories
SQR

(SQR 2002) DO arguments do not match procedure’s – How to Correct

When Run the PeopleSoft SQR Programme via Command Line (SQRW Utility) or PIA Process Scheduler you may face the error like that,

Error on line 88: (SQR 2002) DO arguments do not match procedure’s.

It is SQR Procedure Argument Mismatch Problems. Ok let me tell the whole scenario so you will fully understand the problems and solutions also.

Let consider you have created a SQC Programme like that

 
Begin-Procedure Append-Log($Log_Item_Name, $Log_Item_Value)

  if #_log_array_counter > 9999

        if #_log_array_counter = 10000

          Display 'Maximum size of Log Item Array has been exceeded.'
          Display '(Maximum number of array elements = 10000)'

        end-if

  else

        if length($Log_Item_Name) > 40

          let $Log_Item_Name = SUBSTR($Log_Item_Name, 1, 40)

        end-if

For Example purposes i have not put the full code.
So above Append-Log procedure it take 2 Parameter. So When you invoke this procedure you need to Pass those 2 arguments value.

Do Append-Log('PROCESS NAME',      &L.PRCSNAME)
Do Append-Log('REPORT NAME',       $ReportID)

But if you pass only one , then show (SQR 2002) DO arguments do not match procedure’s. error and abort the SQR Programme.

Do Append-Log('PROCESS TYPE') 

Hope this helps 🙂