Creating a PDF Compound Report With Drill Through Links

Example: Creating the Summary Report (Step 1)

The following syntax generates a sample summary report:

TABLE FILE GGSALES
SUM UNITS DOLLARS BY CATEGORY BY PRODUCT
ON TABLE PCHOLD FORMAT PDF
END

The output is:

Example: Creating the Detail Report (Step 2)

The following syntax generates a sample detail report:

The first page of the output is:

SET SQUEEZE=ON
TABLE FILE GGSALES
SUM UNITS BUDUNITS DOLLARS
BY CATEGORY NOPRINT BY PRODUCT NOPRINT
ON CATEGORY PAGE-BREAK
HEADING CENTER
"Category: <CATEGORY"
" "
ON PRODUCT SUBHEAD
"**** Product: <PRODUCT"
ON PRODUCT SUBFOOT
" "
"<25 **** Return to Summary ****"
ON PRODUCT PAGE-BREAK
BY REGION BY CITY
ON TABLE PCHOLD FORMAT PDF
END

Example: Connecting the Reports With Hyperlinks (Step 3)

The example illustrates the following:

• When you place a Drill Through hyperlink on a sort-break element, ensure the sort-break is at least at the level of the last sort field participating in the Drill Through. For example, in the second report, the Drill Through hyperlink is on the subfooting associated with PRODUCT rather than the heading (with a sort break) associated with CATEGORY.

  Although the code can infer a value of PRODUCT for the CATEGORY heading (you can verify this by embedding the field <PRODUCT> in the heading), it is always the value of the first PRODUCT within that CATEGORY. Typically you want a Drill Through hyperlink for each value of PRODUCT within each CATEGORY.

  You do not need to place the hyperlink on an embedded item. You can just as effectively place it on a text item. Any item in the subfooting is associated with the same values of CATEGORY and PRODUCT. Similarly, you can place a hyperlink on any field in a DATA line, and the values of the associated link fields will be identical. Conventional Drill Down hyperlinks also work this way.

The summary report:

• Places a hyperlink on the PRODUCT field on each DATA line.
• Specifies that the fields to link to the next report are CATEGORY and PRODUCT. Since the target fields in the detail report have identical names in the summary report, you can use the notation CATEGORY rather than CATEGORY=CATEGORY.
• Specifies the action DOWN, so that clicking a hyperlink brings you to the location in the next report that has the corresponding values of the two link fields.
• Uses the default appearance for the hyperlinks, which is blue, underlined text.

The summary report is:

TABLE FILE GGSALES
SUM UNITS DOLLARS BY CATEGORY BY PRODUCT
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=PRODUCT, DRILLTHROUGH=DOWN(CATEGORY PRODUCT), $
ENDSTYLE
END

The detail report:

• Places a hyperlink in the subfooting associated with the link field PRODUCT. Since CATEGORY is a higher level BY field than PRODUCT, each PRODUCT subheading is also associated with a unique value of CATEGORY.
• Places a hyperlink on the first item of the second line of the subfooting, which is the text Return to Summary.
• Specifies action FIRST, so that clicking the hyperlink jumps to the line in the first (summary) report that contains the same values of the two link fields CATEGORY and PRODUCT.
• Uses the COLOR attribute to display the hyperlink as red, underlined text.

The detail report is:

SET SQUEEZE=ON
TABLE FILE GGSALES
SUM UNITS BUDUNITS DOLLARS
BY CATEGORY NOPRINT BY PRODUCT NOPRINT
ON CATEGORY PAGE-BREAK
HEADING CENTER
"Category: <CATEGORY"
" "
ON PRODUCT SUBHEAD
"**** Product: <PRODUCT"
ON PRODUCT SUBFOOT
" "
"<25 **** Return to Summary ****"
ON PRODUCT PAGE-BREAK
BY REGION BY CITY
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=SUBFOOT, LINE=2, ITEM=1, DRILLTHROUGH=FIRST(CATEGORY PRODUCT),
COLOR=RED, $
ENDSTYLE
END

The next step is the only step that is different for creating a Compound Layout report or a legacy Compound Report.

Example: Creating the Compound Layout Report (Step 4)

Perform this version of Step 4 if you are creating a Compound Layout report.

To create the Compound Layout report:

• Add a COMPOUND LAYOUT and SECTION declaration to the top of the procedure.
• Add PAGELAYOUT and COMPONENT declarations for the two reports. Add DRILLMAP attributes to the COMPONENT declarations.
• Add SET COMPONENT commands and the two reports.
• End the procedure with a COMPOUND END command:

SET HTMLARCHIVE=ON
COMPOUND LAYOUT PCHOLD FORMAT PDF
UNITS=IN, $
SECTION=section1, LAYOUT=ON, METADATA='0.5^0.5^0.5^0.5', MERGE=OFF,
   ORIENTATION=PORTRAIT, PAGESIZE=Letter,  $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1,
   BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,
   TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0, $
COMPONENT='REPORT1', TEXT='REPORT1', TOC-LEVEL=2,
DRILLMAP=((L1 REPORT2)), POSITION=(0.750 1.083), DIMENSION=(7.000 3.167),
   METADATA='Z-INDEX: 100; LEFT: 0.75in; OVERFLOW: auto; WIDTH: 7in;
   POSITION: absolute; TOP: 1.083in; HEIGHT: 3.167in', $
PAGELAYOUT=2, NAME='Page layout 2', text='Page layout 2', TOC-LEVEL=1,
   BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,
   TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0, $
COMPONENT='REPORT2', TEXT='REPORT2', TOC-LEVEL=2,
DRILLMAP=((L1 REPORT1)), POSITION=(0.500 0.667), DIMENSION=(7.417 7.000),
   METADATA='Z-INDEX: 100; LEFT: 0.5in; OVERFLOW: auto; WIDTH: 7.417in;
   POSITION: absolute; TOP: 0.667in; HEIGHT: 7in', $
END

-* Add Report1 code and SET COMPONENT command
SET COMPONENT='REPORT1'
TABLE FILE GGSALES
SUM UNITS DOLLARS BY CATEGORY BY PRODUCT
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=PRODUCT, DRILLTHROUGH=DOWN(CATEGORY PRODUCT), $
ENDSTYLE
END

-* Add report2 code and SET COMPONENT command
SET COMPONENT='REPORT2'
SET SQUEEZE=ON
TABLE FILE GGSALES
SUM UNITS BUDUNITS DOLLARS
BY CATEGORY NOPRINT BY PRODUCT NOPRINT
ON CATEGORY PAGE-BREAK
HEADING CENTER
"Category: <CATEGORY"
" "
ON PRODUCT SUBHEAD
"**** Product: <PRODUCT"
ON PRODUCT SUBFOOT
" "
"<25 **** Return to Summary ****"
ON PRODUCT PAGE-BREAK
BY REGION BY CITY
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=SUBFOOT, LINE=2, ITEM=1, DRILLTHROUGH=FIRST(CATEGORY PRODUCT),
COLOR=RED, $
ENDSTYLE
END
COMPOUND END

Example: Merging Summary and Detail Reports Into a PDF Compound Report (Step 4)

Perform this version of Step 4 if you are creating a legacy compound report.

The next step is to combine the reports into a single PDF Compound Report. You can:

• Code the OPEN and CLOSE options on the [PC]HOLD FORMAT PDF command.
• Code the OPEN and CLOSE options on the SET COMPOUND command before the component report syntax.

Drill Through does not support the NOBREAK option, which displays compound reports without intervening page breaks.

This example uses the OPEN and CLOSE options on the PCHOLD FORMAT PDF command:

TABLE FILE GGSALES
SUM UNITS DOLLARS BY CATEGORY BY PRODUCT
ON TABLE PCHOLD FORMAT PDF OPEN
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=PRODUCT, DRILLTHROUGH=DOWN(CATEGORY PRODUCT), $
ENDSTYLE
END

SET SQUEEZE=ON
TABLE FILE GGSALES
SUM UNITS BUDUNITS DOLLARS
BY CATEGORY NOPRINT BY PRODUCT NOPRINT
ON CATEGORY PAGE-BREAK
HEADING CENTER
"Category: <CATEGORY"
" "
ON PRODUCT SUBHEAD
"**** Product: <PRODUCT"
ON PRODUCT SUBFOOT
" "
"<25 **** Return to Summary ****"
ON PRODUCT PAGE-BREAK
BY REGION BY CITY
ON TABLE PCHOLD FORMAT PDF CLOSE
ON TABLE SET STYLE *
TYPE=SUBFOOT, LINE=2, ITEM=1, DRILLTHROUGH=FIRST(CATEGORY PRODUCT),
COLOR=RED, $
ENDSTYLE
END

Example: Run the Drill Through Report (Step 5)

Run the compound report. The first page of output has the summary report with the hyperlinks to the individual products in blue and underlined:

Click the hyperlink Croissant for the category Food. You jump to that detail information. In the detail report, the hyperlink back to the summary report is in red and underlined:

Click the hyperlink Return to Summary to return to the first page (summary report).