Archive

Archive for the ‘SQR’ Category

begin-footing – two different foot note in one SQC

September 14, 2008 1 comment

There was a need to put two different foot note in SQR Reports based on the page orientation (Landscape or Portrait). For this situation, we can creat one SQC using #if and PAGE_ORIENTATION variable defined in our standard setup01a.sqc, setup02a.sqc etc. The SQC lines are given below.

 #if {PAGE_ORIENTATION} = ‘PORTRAIT’
begin-footing 2
  print ‘Important Note: 1’ (1,1)
  !Add more lines as needed
end-footing
#else
    #if {PAGE_ORIENTATION} = ‘LANDSCAPE’
begin-footing 2
  print ‘Important Note: 2 ‘ (1,1) 
  !Add more lines as needed
end-footing
    #end-if
#end-if

This assumes that the main sqr report has included one of standard setup layout sqc like setup01.sqc or setup02.sqc which should have PAGE_ORIENTATION defined using #define.

Categories: SQR Tags:

-DBconnection string in SQR

January 9, 2008 8 comments

-DBconnection string option in Begin-Select, allows you to combine data from multiple databases in one program. An example for this is, Your SQR report which will be running from Finance database, needs name of employee from HRMS database. Create a procedure with -dbconnectionstring as show below.

Begin-Procedure Get_Empl_Name
#debugy show ‘In Procedure : Get_Empl_Name’

let $dbname = ‘DBNAME’
let $dbconnstr = ‘DSN=’ || $DbName || ‘;UID=USERID;PWD=PASSWORD’

Begin-Select -DB$dbconnstr
EMPLID
NAME
     show &emplid ‘ – ‘ &name
FROM PS_PERSONAL_DATA
WHERE EMPLID = $emplid
End-Select
End-Procedure !Get_Empl_Name

For this to work, we need to establish DB Link between the two databases. In the above example, the Finance database should have a DB Link established with HRMS database. The User-id and password, given in the connection string is the user id and password established while creating DB Link. We can assign appropriate access for this userid in DB Link.

Categories: SQR Tags:

Time taken by a procedure in SQR

January 7, 2008 1 comment

While testing, you might want to know how much time a particular procedure or a step in sqr is taking. You may use the following to know the time spent by a procedure or a step.

Begin-Program
let $start_time = edit(datenow(), ‘DD/MM/YYYY-HH:MI:SS:NNNN’)
   do any-procedure-or-step
let $end_time = edit(datenow(), ‘DD/MM/YYYY-HH:MI:SS:NNNN’)
let #total_time = datediff(strtodate($end_time,’DD/MM/YYYY-HH:MI:SS:NNNN’), strtodate($start_time,’DD/MM/YYYY-HH:MI:SS:NNNN’),’minute’)
#debugy show ‘total time taken by the Program = ‘ #total_time  ‘ minutes’
End-Program

Categories: SQR Tags:

File operations using DOS commands in SQR

January 3, 2008 2 comments

We can use the following as an sqc, for performing file operations in SQR. 

!*********************************************************************
!Program Name : FileOprs.sqc
!Description        : File operations using DOS commands.
!*********************************************************************

!*********************************************************************
!Procedure Name : Make_Dir
!Description        : Creates a new path based on the input. Path must be
!                        a complete directory tree listing.
!Usage              : Do Make_Dir($Directory_NewPath)
!*********************************************************************
begin-procedure  Make_Dir($NewPath)
#debugy show ‘In Procedure : Make_Dir’

Let $Commmand_line = ‘cmd /c mkdir ‘ || $NewPath
Do Exec_Cmd($Commmand_line)

end-procedure ! Make_Dir

!*********************************************************************
!Procedure Name : Exec_Cmd
!Description        : Executes $Commmand_line on the system and reports
!                         errors for debugging.
!Usage               : Do Exec_Cmd($Commmand_line)
!*********************************************************************
begin-procedure  Exec_Cmd($Commmand_line)
#debugy show ‘In Procedure : Exec_Cmd’

call system using $Commmand_line #status nowait
If #status <> 0
    #debugy show ‘Command ‘ $Commmand_line ‘ failed to execute.’
End-If

end-procedure ! Exec_Cmd

!*********************************************************************
!Procedure Name : Copy_Files
!Description : Copy files from old to new path. Old information should be of
!       form \pathname\filename, while new information can just be a path.
!Usage               : Do Copy_Files($old_file_name, $new_file_name)
!*********************************************************************
begin-procedure  Copy_Files($old, $new)
#debugy show ‘In Procedure : Copy_Files’

Let $Commmand_line = ‘cmd /c copy ‘ || $old || ‘ ‘ || $new
Do Exec_Cmd($Commmand_line)

end-procedure ! Copy_Files

!*********************************************************************
!Procedure Name : Move_Files
!Description    : Move files from old to new path. Old information should be of
!           form \pathname\filename, while new information can just be a path.
!Usage               : Do Move_Files($old_file_name, $new_file_name)
!*********************************************************************
begin-procedure  Move_Files($old, $new)
#debugy show ‘In Procedure : Move_Files’

Let $Commmand_line = ‘cmd /c move ‘ || $old || ‘ ‘ || $new
Do Exec_Cmd($Commmand_line)

end-procedure ! Move_Files

!*********************************************************************
!Procedure Name : Delete_Files
!Description        : Deletes the files passed in variable $files
!Usage               :  Do Delete_Files($files)
!*********************************************************************
begin-procedure  Delete_Files($files)
#debugy show ‘In Procedure : Delete_Files’

Let $Commmand_line = ‘cmd /c del ‘ || $files
Do Exec_Cmd($Commmand_line)

end-procedure ! Delete_Files

Categories: SQR Tags: