I'm very new to WebFOCUS. I come from a Crystal reports background. I'm trying to do a master/detail report, which FOCUS seems to call a Coordinated Compound report. I might even be able to convince my users to use a drill-down/drillthrough report, but the limitation for them seem to be the same as for coordinated compound reports. My trouble is that the manual quite clearly states that the two reports must be coordinated by the first BY field. My problem is that this report also needs to be sorted in subtotal order, with the subtotal appearing in the subhead, for which I've used the technique presented here: Sort on the subtotal combined with the technique presented here: How to display subtotal in subhead. I don't yet know the sample data well enough to provide a good example of what I'm trying to do, so here is a poor one:

TABLE FILE CAR
SUM SALES NOPRINT
COMPUTE HEADSALES /D15.2 = SALES ; NOPRINT
BY HIGHEST TOTAL SALES 
BY COUNTRY
PRINT SALES
BY HIGHEST TOTAL SALES NOPRINT
BY COUNTRY 
BY HIGHEST SALES NOPRINT
BY CAR
ON COUNTRY NOPRINT SUBHEAD
"Country: <COUNTRY Total Sales: <HEADSALES"
END 

Now, for each detail line in the master report above, I need to print a detail report, which basically passes in the country and car and prints out the list of comments about that country and car from another table, sorted by date. In Crystal, I could pass into my detail report, any column from the master report (whether or not it actually appeared in the report). However, there are no numbers in that other table, so as far as I can tell, I can't use the coordinated compound report at all.

At first, I thought that I might be able to use hold files, looping, and regular compound reports, as discussed here: PDF with REPEAT but then I don't see how to get back my sorting by subtotals, not to mention that I may have to have two levels of subtotal. I'd sure appreciate any ideas anyone has. Thank you.

This message has been edited. Last edited by: bcunnin,

You might consider using a standard drilldown approach to accomplish the task. Using your example:

-* SUMRPT.FEX
TABLE FILE CAR
 SUM SALES NOPRINT
     COMPUTE HEADSALES /D15.2 = SALES ; NOPRINT
 BY HIGHEST TOTAL SALES NOPRINT
 BY COUNTRY NOPRINT
 PRINT SALES NOPRINT
       COMPUTE XSALES/D15.2 = SALES ; AS 'SALES'
 BY HIGHEST TOTAL SALES NOPRINT
 BY COUNTRY NOPRINT
 BY HIGHEST SALES NOPRINT
 BY CAR
 ON COUNTRY SUBHEAD
  "Country: <COUNTRY Total Sales: <HEADSALES"
 ON TABLE SET STYLE *
  TYPE=DATA, COLUMN=XSALES, FOCEXEC=DETAILRPT (COUNTRY=COUNTRY CAR=CAR SALES=XSALES), $
 ENDSTYLE
 ON TABLE PCHOLD FORMAT PDF
END 

The drill-down report (DETAILRPT.FEX in this example) could include a link for returning to the summary report, like this:

-* DETAILRPT.FEX
TABLE FILE CAR
 PRINT
  COUNTRY
  CAR
  MODEL
  BODYTYPE
  SEATS
  DEALER_COST
  RETAIL_COST
  SALES
 WHERE COUNTRY EQ '&COUNTRY'
 WHERE CAR EQ '&CAR'
 WHERE SALES EQ &SALES
 HEADING
  "Back to Summary"
  " "
  "Detail Report"
  " "
 ON TABLE SET STYLE *
  ORIENTATION=LANDSCAPE, $
  TYPE=HEADING, LINE=1, SIZE=8, JUSTIFY=LEFT, FOCEXEC=SUMRPT, $
  TYPE=HEADING, LINE=3, SIZE=14, JUSTIFY=CENTER, $
 ENDSTYLE
 ON TABLE PCHOLD FORMAT PDF
END

Thank you! I must have gotten drill downs and drill throughs combined in my head, as I was under the mistaken impression that I couldn't do a drill down without a coordinated compound report. Now all I have to do is sell the drill down to my users. Thanks again!

On another note, I tried this report using standard compound reports and nested looping through the contents of files. As a focus newbie, it took way longer than I thought it should because apparently, there are two versions of the loop code, one is shorthand for when there are no DM statements in the body of a loop, just report (table file) commands, and the regular long way that is in the DM reference manual. As there were no examples of nested loops on the forums, I'm putting it here in hopes that it will help someone. However, the performance is probably bad, and I'm also not reusing my & variables, so that makes it even more inefficient, so you might think twice about using it.

SET BYDISPLAY = ON

-* the hold files weren't looking like i expected, so I put them where 
-* i could see them easily
APP HOLD BASEAPP
TABLE FILE CAR 
SUM 
	DEALER_COST
	BY HIGHEST TOTAL DEALER_COST NOPRINT
	BY COUNTRY 
-* couldn't get this to work
-*ON TABLE SAVE AS dssown FORMAT COMMA
ON TABLE SAVE AS CARLO FORMAT ALPHA
END
-*OUT_CNT is short for outer count
-SET &OUT_CNT = &LINES ;
-SET &CNTR = 0 ;

-REPEAT LOOP &OUT_CNT TIMES ;
-* CARLO is short for car loop outer
-READ CARLO NOCLOSE &OUTER.A10. &OUTTOT.A7. &JUNK.A7.
-TYPE 'outer'
-TYPE &OUTER
-TYPE 'outtot'
-TYPE &OUTTOT
-SET &CNTR = &CNTR + 1 ;
-*SET COMPOUND = OPEN
-SET &CMP_FLG = IF &CNTR EQ &OUT_CNT THEN 'CLOSE' ELSE IF &CNTR EQ 1 THEN 'OPEN NOBREAK' ELSE 'NOBREAK' ;
-TYPE 'cmpflg'
-TYPE &CMP_FLG
SET COMPOUND = &CMP_FLG
TABLE FILE CAR
SUM 
	DEALER_COST/D12.2M AS 'Dealer cost sum'
	BY HIGHEST TOTAL DEALER_COST NOPRINT
	BY COUNTRY AS 'Country'
WHERE COUNTRY EQ '&OUTER'
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='Letter', LEFTMARGIN=0.250000, RIGHTMARGIN=0.250000,         
  TOPMARGIN=0.250000, BOTTOMMARGIN=0.250000, SQUEEZE=OFF,
  ORIENTATION=PORTRAIT, $
ENDSTYLE
-*ON TABLE PCHOLD FORMAT DHTML
END

-RUN

TABLE FILE CAR
SUM 
	DEALER_COST 
-*	COMPUTE HEADNET /D15.2 = SumOfNET_AMT ; NOPRINT
	BY HIGHEST TOTAL DEALER_COST NOPRINT
	BY CAR
-*PRINT 
-*WELL
-*-*WELL_NAME
-*-*PAYCM_ID
-*-*SumOfNET_AMT
-*BY HIGHEST TOTAL SumOfNET_AMT NOPRINT
-*BY WELL_ID NOPRINT
-*BY PAYCM_ID NOPRINT
WHERE COUNTRY EQ '&OUTER'
ON TABLE SAVE AS CARLI FORMAT ALPHA
END

-RUN
-SET &IN_CNT = &LINES ;

-TYPE 'wellcnt'
-TYPE &IN_CNT

-*pool is loop spelled backwards
-REPEAT POOL &IN_CNT TIMES ;
-* CARLI is car loop inner
-READ CARLI NOCLOSE &INCTY.A16. &JUNK3.A7. &JUNK4.A7.

-TYPE 'innerowner'
-TYPE &OUTER
-TYPE 'inner cty (country)'
-TYPE &INCTY

SET COMPOUND = NOBREAK
TABLE FILE CAR
SUM 
	DEALER_COST 
	BY HIGHEST TOTAL DEALER_COST NOPRINT
-*	BY WELL_ID 
	BY COUNTRY NOPRINT
	BY CAR
-*PRINT
-*WELL_ID
-*WELL_NAME
-*PAYCM_ID
-*SumOfNET_AMT
-*BY HIGHEST TOTAL SumOfNET_AMT NOPRINT
-*BY WELL_ID NOPRINT
-*BY PAYCM_ID NOPRINT
WHERE COUNTRY EQ '&OUTER' AND CAR EQ '&INCTY'
-*ON TABLE PCHOLD FORMAT PDF
-*ON TABLE PCHOLD FORMAT DHTML 
ON TABLE SET STYLE *
   SQUEEZE=OFF,
$
ENDSTYLE
END
-RUN

-POOL
-CLOSE CARLI

END
-LOOP
-CLOSE CARLO

Thank You Bcunnin

Your example helped me.