Back Issues
The Director's Chair
by Noreen Redden
Advanced Technique
FOCUS 101
New Technique
Feature Preview
Feature
User Group Meeting Schedule

McGyver Revisited

Many of you are familiar with the infamous "McGyver Technique," that can solve a myriad of reporting problems with a DEFINE-based JOIN, and some additional DEFINEs. Over the years, we have published many of these techniques, as well as providing the overview on our Web site at: http://techsupport.informationbuilders.com/tech/cof/cof_tcn_m00.html

Today, as even more people use FOCUS in its various flavors, we see growing need and opportunities for employing this technique. As most are centered on concepts involving sorting, here's a new slant on the venerable McGyver technique. Say, for example, you need this report from the EMPLOYEE file:

DEPARTMENT  CURR_JOBCODE  JOB_DESC                          CURR_SAL      
    
 MIS         A07           SECRETARY                        $9,000.00      
             A17           DEPARTMENT MANAGER              $27,062.00      
             B02           PROGRAMMER                      $18,480.00      
             B03           PROGRAMMER ANALYST              $18,480.00      
             B04           SYSTEMS ANALYST                 $21,780.00      
             B14           FILE QUALITY                    $13,200.00      
 PRODUCTION  A01           PRODUCTION CLERK                 $9,500.00      
             A07           SECRETARY                       $11,000.00      
             A15           ASSIST.MANAGER                  $26,862.00      
             A17           DEPARTMENT MANAGER              $29,700.00      
             B02           PROGRAMMER                      $16,100.00      
             B04           SYSTEMS ANALYST                 $21,120.00      
 TOTAL       A01           PRODUCTION CLERK                 $9,500.00      
             A07           SECRETARY                       $20,000.00      
             A15           ASSIST.MANAGER                  $26,862.00      
             A17           DEPARTMENT MANAGER              $56,762.00      
             B02           PROGRAMMER                      $34,580.00

If you look closely, this actually looks like two reports. The first is a sum of current salary sorted by department and job code (showing job code descriptions). The second, while similar, is simply the sum of current salary by job code and job description. Certainly, in these days of compound PDF reports, we could code two requests, combining them into one after the fact. However, that may not be optimal with a large input file, as two passes will require too much time and CPU; or when you don't want to transfer the output to a PC; or if you want to number the pages one of n, etc., and incorporate both reports. So, what to do? What if the EMPLOYEE file actually had a "report number" field, containing two values for each employee?

Then it would be relatively easy to code with a DEFINE:

DEFINE FILE EMPLOYEE
SORTFLD/A10 = IF REPORT_NUMBER EQ 1 THEN DEPARTMENT ELSE 'TOTAL';
END
TABLE FILE EMPLOYEE
SUM SALARY 
BY REPORT_NUMBER  NOPRINT
BY SORTFLD AS 'DEPARTMENT' 
BY JOB_CODE 
BY JOB_DESC
END

OK, but who in their right mind actually codes a report number into a database? The answer, of course, is no one. However, we can create a flat file with a report number (-TYPE flatfile R12) and describe it, adding a constant at the beginning in order to give us something to join to (previous iterations of McGyver used BLANK).

FILE=FSEQ,SUFFIX=FIX
SEGNAME= REPORTS,SEGTYPE=S0
FIELD=CONTROL,CTRL,A1,A1,$
SEGNAME=SEG2,PARENT=REPORTS,OCCURS=VARIABLE
  FIELD=REPORT_NUMBER,,I1,A1,$

Then, the JOIN and DEFINE give us basically the same structure:

[CMS] FILEDEF FSEQ …                  OR
DYNAM ALLOC FSEQ …
-RUN
-WRITE FSEQ R12
-RUN
JOIN CONTROL WITH JOB_CODE IN EMPLOYEE TO CONTROL IN FSEQ AS AJ
END
DEFINE FILE EMPLOYEE
CONTROL/A1 WITH JOB_CODE = 'R';
DEFINE FILE EMPLOYEE
SORTFLD/A10 = IF REPORT_NUMBER EQ 1 THEN DEPARTMENT ELSE 'TOTAL';
END

So, everything is right in the FOCUS world. With this, you can get up to nine reports in one pass. Should you actually need more, or not want to rewrite the FSEQ file for each difference in the report, FOCUS provides another option in describing sequential files. When you have a segment that contains an OCCURS clause, you may describe a field that FOCUS will create as an index to the OCCURS entries. It must have an ALIAS = ORDER, and ACTUAL of I4. So, being very generic, that same FSEQ master might look like this:

FILE=FSEQ,SUFFIX=FIX
SEGNAME= REPORTS,SEGTYPE=S0
FIELD=CONTROL,CTRL,A1,A1,$
SEGNAME=SEG2,PARENT=REPORTS,OCCURS=VARIABLE
  FIELD=FILLER,,A1,A1,$
  FIELD=REPORT_NUMBER,ORDER,I4,I4,$

And, the FILEDEF/ALLOCATE points to any existing file with an R in the first column. Of course, if you don't like R, you can use any constant, blank, X, whatever).

JOIN CONTROL WITH JOB_CODE IN EMPLOYEE TO CONTROL IN FSEQ AS AJ
END
DEFINE FILE EMPLOYEE
CONTROL/A1 WITH JOB_CODE = 'R';
DEFINE FILE EMPLOYEE
SORTFLD/A10 = IF REPORT_NUMBER EQ 1 THEN DEPARTMENT ELSE 'TOTAL';
END
TABLE FILE EMPLOYEE
SUM SALARY 
BY REPORT_NUMBER  NOPRINT
BY SORTFLD AS 'DEPARTMENT' 
BY JOB_CODE 
BY JOB_DESC
WHERE REPORT_NUMBER LE 2
END

Once you have this set up, the sky's the limit. Use REPORT_NUMBER as an ACROSS field, rather than BY. Use it to determine verb objects, rather than Sort fields. Make it a month, so that you can get details by month when you only have a start date and end date. You'll find additional examples at: http://techsupport.informationbuilders.com/tech/contents/cof_tcn_toc.html?prod=FOCUSforS/390#MACGYVER.

Back Next