Search This Blog


Wednesday, November 26, 2014

SQR to create Summary Charts using print-chart

Usually Summary reports, show up break up of count of records processed of the total records.
Representing these Summary reports through diagrams will help client understand better.

 Declare-Chart is used to describe the layout of the chart.
Array is created to store data.
Through Print-Chart we read data from Array and print onto the chart.

Below is one sample code to retrieve data and display in chart.

 Create-Array    ! This Array is passed to the Print-Chart
    Name = emp_hired
    Size = 10    ! Maximum of 10 rows of data
    Field = count:number:3    ! Fields in each row
  Declare-Chart tot_emp_hired
    Chart-Size = ( 30,30)
    Title = 'Employee Fetched'
    Type = histogram
    3D-Effects = yes
    X-Axis-Label = 'Company'
    Y-Axis-Label = 'Employee Hired'

 do Stdapi-Init        !stdapi.sqc
 do Init-DateTime    ! datetime.sqc
 do Init-Number        ! number.sqc
 do Get-Current-DateTime! curdttim.sqc
 do EmpChartReport
 do Stdapi-Term            !stdapi.sqc

begin-procedure EmpChartReport
! Load the array with the Chart Data. Max rows is specified in Create-Array
  Put  20
  Into emp_hired(0)  count(0)
  Put  12
  Into emp_hired(1)  count(0)   
  Put  4
  Into emp_hired(2)  count(0)

  Move 5 TO #x
  Move 10 TO #y
  Move 3 TO #row
  Move 3 TO #col

 Print-Chart tot_emp_hired (#x,#y)
    Fill = color
    Sub-Title = 'Employee hired report'
    Data-Array-Row-Count = #row   
    Data-Array-Column-Count = #col
    Data-Array-Column-Labels = ('ABC1', 'ABC2', 'ABC3')
    Data-Array = emp_hired

Sample Output:

Creating PDF file through AE using Java Class

The example mentioned below will give basic insights on how to create PDF file through Application Engine using Java classes.
Other way is to use BI Publisher and call the report definition and then publish the report.

To start with, firstly the Appserver/Classes path should contain the JAR file starting with itext.
Write the java code in AE to create PDF file.

Below is the sample code:
/* Create a Document object */
Local JavaObject &Obj_CreateiTextTable = CreateJavaObject("com.lowagie.text.Document");

/* Cretae PDF Writer object */
Local JavaObject &obj_writePDFoutput = GetJavaClass("com.lowagie.text.pdf.PdfWriter").getInstance(&Obj_CreateiTextTable, CreateJavaObject("", "/ TESTPDF11262014.pdf", True));
&; /*open the document for writing */
 /* Array with 3 columns */
Local JavaObject &Obj_myPDFTable = CreateJavaObject("com.lowagie.text.pdf.PdfPTable", 3);
 /* Add data to each cell */
&Obj_myPDFTable.addCell("Emp ID");
&Obj_myPDFTable.addCell("Main Street");
 /* Add Table to Document */
&Obj_CreateiTextTable.close();  /* close the document */

Output Snapshot:

Thursday, June 26, 2014

SQR: Global Variables

In SQR, we donot explicitly declare variable as global in general unless the variables are passed through the procedure in_list.
Below is the sample program to demonstrate usage of variable value globally.


  do Stdapi-Init          !stdapi.sqc
  do Init-Report          !Init Report        
  do Process-Main       !Process-Main-Logic
  do Stdapi-Term              !stdapi.sqc 

begin-procedure Init-Report
let $Flag = 'N'
end-procedure Init-Report

begin-procedure Process-Main
! Business logic

show ' Before override = ' $Flag
let $Flag = 'Y'
show ' After override = ' $Flag
    do procedure-1
end-procedure Process-Main

begin-procedure procedure-1
let $Flag = 'N'
show ' In procedure-1 = ' $Flag
end-procedure procedure-1

Before override = N
After override = Y
In procedure-1 = N

Tuesday, June 17, 2014

SQR: Date formatting in CSV file

For writing data to ouput files we use write command in SQR. And if we have date as one of the output fields, clients prefer '01062014'. This can be achieved by applying formatting to the date field.

Below is the manual way of doing in Excel file. Enter date as show in pic 1 and tab out.
Once we tab out, below is the format of the date that appears in the file.

But this is not we expected. So now lets apply formatting.
So when we prepend =" to date and append ", it converts date to Text format , thus retaining required format.

Fine. Now we need to achieve same through the SQR. Below is the sample code to do the same.
Sample Code:
  let $write_header = 'EMPLID,EFFDT,FORMATTED EFFDT'
  let $emplid = 'EMP101'
  let $effdt  = '01022014'
  let $text = '="05062014"'

  let $delim = ','
write 1 from $write_header

write 1 from $emplid
SQR Output for a comma separated CSV file

Saturday, March 1, 2014

SQR Basics: Usage of DISTINCT clause

Below is the syntax for the usage of DISTINCT clause in SQR.


Friday, January 24, 2014

PeopleSoft: Working with grids have same record at Level 0 and Level 1

In PeopleSoft, we use grids to populate data in a list wise manner. And generally in Setup pages, we see same record being used at Level 0 and Level 1.

In such case, there was an issue that creeped up while saving the data in the page. Along with the data in the grid , extra row gets inserted to the database record which contains only data for Level0 fields.

To avoid the unwanted row, it is noticed that the page design is causing the issue. The Level 0 field which we enter data from Search Page is in Edit mode. So the Component Processor allocated buffer for the Level0 fields expecting some data. During Save , along with Level1 data in grid , Level 0 data also got inserted.

To resolve the issue, the Level 0 field is made 'Display Only', such that Component processor will treat this field as 'Read Only' and does not allocate buffer. Now when we save data , only data from the grid gets inserted.

Monday, January 13, 2014

Oracle: Differnce between to_date('01-JAN-00','DD-MON-RR') and to_date('01-JAN-1900','DD-MON-YYYY')

Difference between to_date('01-JAN-00','DD-MON-RR') and to_date('01-JAN-1900','DD-MON-YYYY')

Result of to_date('01-JAN-00','DD-MON-RR') will be '01-JAN-2000'  where 'RR' refers to current century
Result of to_date('01-JAN-1900','DD-MON-YYYY') will be '01-JAN-1900'.

Note: By default, if you do a 'Save As' with Insert , the default format will be in 'RR'.