Creating Custom GRIM reports - A Primer

[Tom Pellitieri]

One of the best features Prelude has provided is the General Report Interface (aka GRIM).  This interface allows you to customize the parameters of various reports, and also schedule them for automatic processing.  The one thing they have not provided are concise instructions for creating your own custom reports.  This is actually much simpler than you might think, although there are a few special items you need to consider.

The most common use is for Report Writer reports.  You can create your own with Prelude's template.  With the /RD tool, choose your file, then press F7 and copy the TEMPLATE report from the REPORTS file.  You may then build your report as usual, although (by default) you should set the Selection Criteria to (@PARMS(39)).

Once you have your Report Definition process (e.g., R*MY.FILE*MY.REPORT), you will need to wrap it in the GRIM process framework.  One way to do this is to create a SB+ Paragraph.  For example, use /PD.P to create the Process MY.GRIM.RPT with the following four lines:

EXEC "GRI"
IF @RTN.FLAG THEN EXIT
EXEC "R*MY.FILE*MY.REPORT"
EXEC "GRI.END"

(Note:  Most of Prelude's report paragraphs don't do this!  They repeat the code from the GRI process in each paragraph, and therefore look extremely complicated.)

Now, you need to use the General Report Interface Maintenance tool (/GRIM) to set up your report parameters.  IMPORTANT:  the Report Name must match the name of the calling process!  Normally, the Description and Report Heading are the same, or at least similar.  The first would be used in a menu, and the second will appear in the Report (if you used the TEMPLATE).  The Data Filename is the file that you used for the report definition.

The actual selection criteria are specified in the lower part of the window.  Here is a brief summary of these fields:

Dictionary Name:  This is the field name to use in the selection process.  You can use @WORK fields here, but you will need to process them manually in your paragraph before you execute the report.

Description:  This is taken from the field definition, and will display as a prompt when the process is executed.

Oper:  This is the operation (e.g., "=", "<") to use on this field in the selection statement.

Multi:  Set this to "Y" if you want to be able to specify multiple values.  For example, you may want to limit the report to a single WHSE.NUM or perhaps run it for multiple values.

Man:  Set this to "Y" if this is a mandatory field for the report.

Cnv:  If necessary, enter an Input Conversion Code.  For example, "MCU" will force a text field to be upper case.

Sel:  Set this to "Y" if the field should be added to the PARMS(39) selection string.  Please note that most fields should be added, unless you are using the value to manipulate the process.  For example, you could use a @WORK field to request a Sort Order.  In your paragraph, you would use this value to choose which report to execute.

Dsp:  Set this to "Y" if the selected value should be displayed in the report heading.  For example, if you are sorting and breaking on WHSE.NUM, you probably don't need to display the selected WHSE.NUM in the report heading.

W:  This allows you to "wildcard" the selection.  F3 Help gives a good description of the options.

Add:  This provides additional information.  By default, this is set to "Y" and is preloaded with Default, Intuitive Help and Validation processes from the Field Definition.  You can override these, or add to these options as required.

When your user executes the paragraph, they will be prompted for each of the fields you've included here.  If they escape out of the screen, @RTN.FLAG will be set, so the paragraph will exit.  Otherwise, your report will execute, and the "GRI.END" process will do the required clean-up.

While the GRIM screen will build the selection criteria in PARMS(39) for you, there are times it can't do everything.  Also, you may have a report where you want to control the selection without input from the user.  In those cases, it's relatively easy to modify the criteria within the paragraph.

For example, if you wanted a report based on Open Back-Orders, you need to select ORDER.LINE with BO.QTY > "0".  You really don't want to have the user specify this every time.   Here is an example paragraph that will add this parameter to the selection criteria for you.

LOCAL CRIT
EXEC "GRI"
IF @RTN.FLAG THEN EXIT
CRIT = 'BO.QTY > "0"'
IF @PARMS(39) = "" THEN @PARMS(39) = CRIT ELSE @PARMS(39) = CRIT:" AND ":@PARMS(39)
EXEC "R*ORDER.LINE*MY.BO.RPT"
EXEC "GRI.END"

Notice that this paragraph modifies PARMS(39) to include the criteria that the user doesn't need to specify.  If the user didn't specify any criteria, PARMS(39) would be blank, so you need to use the basic criteria in order to have the report work as expected.

I hope other Prelude users find this useful.

--Tom Pellitieri
  Toledo, Ohio

[precisonline]

Good stuff!  If it's always the same why not create a generalized wrapper like this?

LOCAL L.PROCESS
*
L.PROCESS = @PARAM
*
EXEC 'GRI'
IF @RTN.FLAG = 0 THEN
  EXEC L.PROCESS
  EXEC 'GRI.END'
END

I have something similar called xxx.RUN.GRIM.RPT (where xxx is the client code).  With this in place you can invoke any report with a single line paragraph like this:

EXEC 'xxx.RUN.GRIM.RPT,R*file*item'

The only thing this is missing then is the ability to pass in the name of a stored parameter set from a menu.  So we could enhance this like this:

LOCAL L.PROCESS,L.PARAM
*
L.PROCESS = FIELD(@PARAM,',',1)
L.PARAM    = FIELD(@PARAM,',',2)
*
WORK<29,2> = L.PARAM
EXEC 'GRI'
IF @RTN.FLAG = 0 THEN
  EXEC L.PROCESS
  EXEC 'GRI.END'
END

But overall, you're right, that copied code that fronts each standard report really does make it look complicated when it really doesn't have to be.

[DeechVogt]

Hi All,

I have recently entered the world of SB+ and Prelude and was given a copy of this thread as a PDF by Kevin. 

I Followed the PDF of this thread which now looks like this:

001 * Universal Prelude GRIM Wrapper by Kevin King
002 LOCAL URM.NAME,PROCESS.NAME,PARAM.NAME,HOLD.WORK
003 *
004 URM.NAME     = FIELD(@PARAM,',',1)
005 PROCESS.NAME = FIELD(@PARAM,',',2)
006 PARAM.NAME   = FIELD(@PARAM,',',3)
007 *
008 IF URM.NAME = '' THEN
009   URM.NAME = @PROC.NAME<2>
010 END
011 *
012 HOLD.WORK = @WORK
013 *
014 @PROC.NAME<1> = '' ;* To fool passwords stuff in GET.CO
015 EXEC 'GET.CO,0'
016 @PROC.NAME<1> = 'KEY.RUN.GRIM.RPT'
017 IF @RTN.FLAG = 0 THEN                                                                                                        
018   W29 = URM.NAME
019   IF PARAM.NAME # '' THEN
020     W29,2 = PARAM.NAME
021   END ELSE
022     W29,2 = URM.NAME
023   END
024   *                                                                                                                          
025   EXEC 'GRI.ENT'
026   IF (W29,1 = 'GRI') THEN
027     IF (PROCESS.NAME # '') THEN
028       EXEC PROCESS.NAME ;* Do the work for the report
029       EXEC 'R,0'
030     END
031   END
032 END
033 @WORK = HOLD.WORK

I noticed at Line 12 in the above code that the HOLD.WORK is set but is not restored at the end of the paragraph.  Was this done by intent?  I was thinking about adding the line @WORK = HOLD.WORK at the end of the paragraph. 

Let me know if this would be a good idea or not.

Thanks,

Deech

P.S.  Made the change in the above code.  It will now restore @WORK back to its original state.

[precisonline]

Absolutely.  And good find!

[nmorgan]

I wind up writing quite a few programs in Basic rather than Report Writer as they need to pull data from several places, merge it sort it and then report.  Not wanting to lose the benefits of the General Report Interface (GRI), I developed some standards to use GRI with Basic.  This is from our internal wiki:

Using the General Report Interface (GRI) with BASIC Programs

The GRI works by building an SB+ select clause and returning it in the COMMON variable PARMS(39).  The SB+ select clause differs from a D3 or UD TCL select clause by not using the WITH keyword before each parameter.  To use the GRI output in a Basic program, it is necessary to parse the string in PARMS(39) and add the WITH keyword at the appropriate places.  The example below shows a simple way to do this.

Line 70 sets up the first part of the TCL command, naming the file to select from.  The “WITH” on the end is required.

The subroutine called in line 100 appends each word found in PARMS(39) to the VERB variable we initialized at line 70.  If the word just processed was an AND or OR, it appends WITH as the next word in the VERB output string.

Line 120 completes the TCL command in VERB by adding the sort criteria.

070      VERB = 'SELECT ORDER WITH'
080 *------------------------------------------------------------
090      WORDS = PARMS(39)
100      CALL PARSE.SELECT(WORDS,VERB)
110 *------------------------------------------------------------
120      VERB := ' BY CUST.NUM BY SLSM.NUM'
130      EXECUTE VERB RETURNING MSG CAPTURING DSP
140      IF SYSTEM(11) = 0 THEN
150         . . . .
160      END

This is the subroutine that is called from line 100 above.

010 SUBROUTINE PARSE.SELECT(WORDS,VERB)
020 * Parse out GRI selection parameters, adding WITH's
030 *
040      WORDS = TRIM(WORDS)
050      CONVERT " " TO @AM IN WORDS
060      NUM.WORDS = DCOUNT(WORDS,@AM)
070      FOR X = 1 TO NUM.WORDS
080         VERB := ' ':WORDS<X>
090         IF WORDS<X> = 'AND' OR WORDS<X> = 'OR' THEN
100            VERB := ' WITH'
110         END
120      NEXT X
130      RETURN

 
The GRI also returns page heading information in WORK<26>.  This can be used in a “universal” page heading routine as shown below.  Line 720 prevents the routine from ejecting a blank page before page 1.  The line counter is reset at line 730 so that it can be later incremented for optional heading lines.  PARMS(2)<1> referenced in line 750 contains the company name from Prelude’s company file.  WORK<26,2> in that line will indicate whether this is a standard PSI report or a user custom report.

Line 790 is the report title.  GRI returns this in WORK<26,1>.  The string includes the “as of” date and time.

The statements beginning at lines 810 and 860 are for optional elements returned by the GRI in WORK<26,3> and WORK<26,4>.  These show the selection criteria that was passed in PARMS(39), plus any non-selection parameters entered on the GRI screen.  WORK<26,4> is only used if WORK<26,3> cannot hold the entire string.  Since these statements are optional, they increment the line counter if they are used.

700 PageHeads:
710      PGNO += 1
720      IF PGNO > 1 THEN PRINT CHAR(12):
730      LNNO = 5
740 *    ** Heading line 1 -- Company, User, Page #
750      PRINT PARMS(2)<1>"L#30":" ":WORK<26,2>"L#10":SPACE(65):
760      PRINT "User-Id ":OCONV(USER.ID,'mcu')"L#3":
770      PRINT "Page  ":PGNO
780 *    ** Heading Line 2 -- Report Title, As-Of Date
790      PRINT WORK<26,1>"L#130"
800 *    ** Heading Line 2a -- Selection Criteria
810      IF WORK<26,3> # "" THEN
820         PRINT WORK<26,3>"L#130"
830         LNNO += 1
840      END
850 *    ** Heading Line 2b -- Sort Criteria
860      IF WORK<26,4> # "" THEN
870         PRINT WORK<26,4>"L#130"
880         LNNO += 1
890      END
900 *    ** Heading Line 3 -- Blank
910      PRINT
920 *    ** Heading Line 4 -- Column Headings
930      PRINT "Column"
940 *    ** Heading Line 5 -- Heading Underscores
950      PRINT "======"
960      RETURN

 
Below is an example of a paragraph process to run a Basic program front-ended with the GRI screen.  It should have the same name as the GRI screen named in line 13 and the program named in line 16.

GET.CO in line 11 insures that basic Prelude information is stored in several PARMS variables. 

Line 13 sets up the parameter set used by the GRI.ENT process.

If the execution of GRI.ENT was successful, WORK<29,1> will contain the string “GRI”, PARMS(39) will contain the SB+ select clause, WORK<26,1> will contain the report title, WORK<26,2> will contain the string “* USER *”, and WORK<26,3> will contain a version of the select clause suitable for the report heading.


10   LOCAL HOLD.WORK; HOLD.WORK = @WORK
11   EXEC “GET.CO,0”
12   IF @RTN.FLAG THEN GOTO 9999
13   @WORK<29> = “MY.PROGRAM”
14   EXEC “GRI.ENT”
15   IF @WORK<29,1> # “GRI” THEN GOTO 9999
16   CALL MY.PROGRAM
17   9999 *
18   @WORK = HOLD.WORK
19   EXIT 0

[nmorgan]

In conjunction with the stuff above, I also use a home-grown program generator to provide me with a consistent frame work for programs that incorporates the GRI code.  It has a front-end screen that asks for some essential information, like the list of files to be used, number and levels of total fields, etc.  If anyone is interested, I would be happy to post that code here.

[precisonline]

SB+ also has a "standard" (and undocumented) subroutine that we call to convert GRIM criteria into a usable TCL statement:

CALL SB.GEN.SELECT(selCrit,dict,data,'',sort,'',TCL,RSVD)

The "selCrit" variable is the selection criteria in @PARMS(39).  "dict" and "data" are the text names of the dict and data files to be selected.  "sort" is the space delimited list of sort fields".  TCL is returned.  RSVD is unused.  I've used this to make a component xxx.CONV.SEL.CRIT (xxx = company identifier) that accepts the dictionary, data, and sort field field names in @PARAM and then returns the TCL statement in @VALUE:

SUBROUTINE XXX.CONV.SEL.CRIT
*
* Written By:  Kevin King
* Date:        12 Jan 2003
* Description: This subroutine will take the selection criteria in @PARMS(39)
*              and will return a valid TCL select statement in @VALUE.  @PARAM
*              contains the dictionary file name, the data file name, and
*              sort fields.
*
*              Note: @PARMS(39) is used for the selection criteria to avoid
*                    the 999 character limit in @PARAM.
*
*****************************************************************************
* Modifications
*****************************************************************************
* Date.....  Changed By.....  Description of Change..........................
* 21 Mar 03  Kevin King       Added code to strip SSELECT to SELECT if no
*                             sort fields specified.  (SB+ Bug)
*
*****************************************************************************
* Include(s)
*****************************************************************************
*
$INCLUDE DMSKELCODE COMMON
*
DICT.NAME = FIELD(PARAM,',',1)
DATA.NAME = FIELD(PARAM,',',2)
SORT.FLDS = FIELD(PARAM,',',3)
SEL.CRIT  = PARMS(39)<1>
*
IF (SEL.CRIT : SORT.FLDS NE '') THEN
  CALL SB.GEN.SELECT(SEL.CRIT,DICT.NAME,DATA.NAME,'',SORT.FLDS,@VM:'X',TCL.EXP,AA)
  IF (SORT.FLDS EQ '') THEN
    IF (TCL.EXP[1,2] EQ 'SS') THEN
      TCL.EXP = TCL.EXP[2,32200]
    END
  END
END ELSE
  TCL.EXP = 'SELECT '
  BEGIN CASE
    CASE (DATA.NAME NE '')
      TCL.EXP = TCL.EXP : DATA.NAME
      IF (DICT.NAME NE '') THEN
        TCL.EXP = TCL.EXP : ' USING DICT ' : DICT.NAME
      END
    CASE (DICT.NAME NE '')
      TCL.EXP = TCL.EXP : DICT.NAME
    CASE 1
      TCL.EXP = ''
  END CASE
END
*
VALUE = TCL.EXP
*
RETURN