Multiple Reports in SQR

Multiple Reports in SQRGenerating multiple reports in SQR is common these days. Writing SQRs that produce multiple reports have many advantages over the other approach of having multiple SQRs do this job. Here are some of them.

Advantages

  1. Multiple reports in one SQR approach reduces database trips thereby making the reports faster. This is especially true when all the reports are based on the same set of data. However, be cautious not to get totally different SQR reports into one – this can complicate things.
  2. Easily possible to direct multiple reports to multiple printers. Since we are free to have different layouts and printers for different reports, we can easily direct some reports to one printer while some other reports to a different printer.
  3. This results in fewer SQRs which in turn reduces maintenance costs. Well, this one needs no further explanation.

Having seen the advantages, you would be curious to see how we can generate multiple reports in SQRs. This can essentially be achieved in a three step approach.

Declare-Report

When generating multiple reports, SQR mandates us to declare all the reports that we wish to generate. This is done within the Setup section of the SQR. We can use different printers / layouts for different reports. If you are happy with the default printer and layout, just ignore these in your report definition.

In the sample code below, we have declared two reports, both of which use the default layout and printer for simplicity sake.

BEGIN-SETUP
 
DECLARE-REPORT TEST1
END-DECLARE
 
DECLARE-REPORT TEST2
END-DECLARE
 
END-SETUP

For-Reports

Standard SQRs that generate just one report would have only one Heading / Footing. However, we need to have a mechanism to print different headings / footings on different reports. This can be achieved using the For-Reports parameter. This is how we use it.

BEGIN-HEADING 1 FOR-REPORTS=(TEST1)
PRINT 'Test Report One' (1) CENTER
END-HEADING
 
BEGIN-FOOTING 1 FOR-REPORTS=(TEST1)
PAGE-NUMBER (1,1) 'Page '
LAST-PAGE () ' of '
END-FOOTING
 
BEGIN-HEADING 1 FOR-REPORTS=(TEST2)
PRINT 'Test Report Two' (1) CENTER
END-HEADING
 
BEGIN-FOOTING 1 FOR-REPORTS=(TEST2)
PAGE-NUMBER (1,1) 'Page '
LAST-PAGE () ' of '
END-FOOTING

Use-Report

We have reached the final stage – printing the actual report. Before we can print anything, we would need to inform the processor about the report to which we are printing. We use the Use-Report command to set the printing context. This is how we do it.

BEGIN-PROGRAM
 
USE-REPORT TEST1
PRINT 'This text goes into report TEST1' (,1)
 
USE-REPORT TEST2
PRINT 'This text goes into report TEST2' (,1)
 
END-PROGRAM

This method would however be useful only if you know, at the time of writing the SQR, how many reports you would need. If you need to generate multiple reports on the fly, then you would use the New-Report command. We will write about this in a future post.

Tags: , ,
Subscribe to Comments RSS Feed in this post
4 Responses
  1. Hi Rakesh,

    What about new-report function?
    I meet a issue on using this.
    Do u have any sample sqr for this,many thanks.
    You can reach me at joanli226 [at] gmail.com.

    Joan

    • Hi Joan,

      I do not have a sample report that uses NEW-REPORT but can give you some points here.

      When NEW-REPORT is executed, it closes the current report and opens the new one with the name you have specified. This also resets the internal page counter to 1.
      After execution of this command, you can use print as you would normally do in any other report.

      NEW-REPORT  'testrpt.lis'
      NEW-REPORT  $next-filename
  2. how can i create reports dynamically (i.e., based on coulmns retrived from do-select command)

  3. SQR popo005.sqr is a good example

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Read previous post:
PeopleSoft Journal Uploader
PeopleSoft Journal Import Error – User-defined type not defined

Close