SandyFlat.Net

When a report is run from inside Maximo, a text format parameter file, PRM (with no extension), is created in the local spool directory. PRM is then handed to the SQR report as an input file.

PRM will have five lines:

Line 1 will contain the SQL "where" query from the calling Maximo screen or will have an asterisk (*) if no query-by-example was used.

Lines 2 through 5 will contain the user's inputs from the (up to) four prompts.

TIP - When debugging an SQR, it's vital to know exactly what is being fed to it. The simplest way to see the contents of PRM is to create a Notepad icon on your desktop that opens PRM.

Alternatlvely, you can download, register and run TESTPRM to see passed parameters.

Prior to Maximo V3.x, uncompiled reports were the norm. One would craft an SQR using their favorite text editor and register the .SQR in Maximo.
The introduction of the SQR Workbench included the option to compile SQRs to SQTs, and PSDI coded Maximo so pass the report name with an .SQT appended rather than .SQR.
Although the compiled SQTs ran marginally faster than the SQRs, the recommendation that a separate sets of compiled SQT be maintained for each database meant that those of us that maintained training/development databases would have more work. Then, there was the problem of the plain-text admin userids and passwords in the compiled SQTs...
Since PSDI hardcoded the .SQT extension, they didn't leave an option to run uncompiled; but it can be done with a simple hack:

• In the Reports and Other Apps module, on the record for SQR reports, make two changes:

  	Compiled	Uncompiled
  Application:	SQRWT	SQRW
  Type:	REPORT WRITER	REPORT WRITER
  Application Command Line:	sqrwt.exe	sqrw.exe

• Rename your reports from *.SQR to *.SQT

Enjoy one set of reports for all iterations of your database.

From within an SQR report, you can access several system variables, at runtime, that can be used to enhance the report. Add this procedure (or reference it as an "include" file), to make the current database name, current username and the workstation available in uppercase.

                     BEGIN-PROCEDURE get_db_info
                     BEGIN-SELECT
                     db_name()        &database
                     host_name()      &workstation
                     user_name()      &susername
                     END-SELECT
                        MOVE      &database    TO $database
                        UPPERCASE $database
                        MOVE      &workstation TO $workstation
                        UPPERCASE $workstation
                        MOVE      &susername   TO $susername
                        UPPERCASE $susername
                     END-PROCEDURE
                  

In the report's main procedure, call this procedure with this line:

                  DO get_db_info
               

Then, every time the SQR is run, the database that it is being run against, the username running the report and the workstation its being run from will be available within the report. There's several options for using the runtime data, see below for an example of using it in the footer.

The runtime parameters made available by the above procedure can be used in the footer to help qualify the report contents. For example, if you have a production and a development database, the database runtime parameter can be used to indicate which of the two databases the report reflects.

This is one possible footer procedure:

                  BEGIN-FOOTING 2
                     GRAPHIC                (1,1,93) HORZ-LINE 10
                     UPPERCASE $sqr-program
                     PRINT     $sqr-program (2,1)
                     UPPERCASE $susername
                     PRINT $susername       (,25)
                     PRINT $workstation     (,70)
                     PRINT $database        (,85)
                  END-FOOTING
               

This will print the report file name, current username, workstation name and database name in the footer, under a horizontal line (you will need to adjust the graphic width and the columns to suit your report width).

Over the years, as new SQR reports are written, report maintenance becomes an issue. And if you switch from SQLBase to Oracle or SQLServer, each report will need to be tweaked. In both cases, it's handy to know which reports are still being used, which have been abandoned and everything in between.

With the addition of a new table in the Maximo database and a few lines of code to each SQR (or an include file reference), report runs can be tracked automatically

A table with this structure:

                     CREATE TABLE [dbo].[reporttrack] (
                        [reportname] [varchar] (30) NULL,
                        [rundate] [datetime] NULL,
                        [userid] [varchar] (12) NULL,
                        [workstation] [varchar] (12) NULL,
                        [inputwhere] [varchar] (500),
                        [input1] [varchar] (50),
                        [input2] [varchar] (50),
                        [input3] [varchar] (50),
                        [input4] [varchar] (50) NULL
                     ) ON [PRIMARY]
                  

Can capture a significant amount of tracking information. Adding this line:

                  DO record_use ($sqr-program, $susername, $workstation, $where,
                  $param1, $param2, $param3, $param4)
               

To call this procedure:

                  BEGIN-PROCEDURE record_use ($sqr-program, $susername, $workstation,
                     $ru_in_where, $ru_in_1, $ru_in_2, $ru_in_3, $ru_in_4)
                  BEGIN-SQL
                  INSERT INTO reporttrack (reportname, rundate, userid, workstation,
                     inputwhere, input1, input2, input3, input4)
                  VALUES ($sqr-program, GETDATE(), $susername, $workstation,
                     $ru_in_where, $ru_in_1, $ru_in_2, $ru_in_3, $ru_in_4)
                  END-SQL
                  END-PROCEDURE
               

Then, every time the SQR is run, it will record into table REPORTTRACK the time, user, workstation and all of the inputs. It's not only handy for prioritizing which reports to concentrate when migrating, it's useful for seeing how users are using the reports.