I have a compound EXL2K report.
It has two tabs first tab is summary and second tab is detail. Is it possible to have a link on Field1 in Summary Tab which when clicked goes to its position on detail tab?. I tried searching for this in the forum but didn't find any.A car example would be great!.
Thanks.

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

Here is something that I knocked up.

Seems that Excel uses an anchor to link to cells in the workbook.

SET COMPOUND = OPEN

TABLE FILE CAR
PRINT 
COMPUTE CNTR/I3 = IF LAST CNTR EQ 0 THEN 1 ELSE LAST CNTR + 1 ;
COMPUTE CTYLINK/A50 = '<a href="#' || COUNTRY || '!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
END

SET COMPOUND = BYTOC CLOSE

TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END

Bravo!

-- One shortcoming -- the embedded blank in W GERMANY makes its link invalid.

The fix:

DEFINE FILE CAR
  TAB/A10=STRREP(10, COUNTRY, 1, ' X', 1, '_', 10, TAB);
  TAB=SUBSTR(10, TAB, 1, ARGLEN(10,COUNTRY,'I2'), 10, TAB);
END

SET COMPOUND = OPEN

TABLE FILE CAR
WRITE 
  COMPUTE LIST/I3 = LIST + 1 ;
  COMPUTE CTYLINK/A90 = '<a href="#' || TAB || '!A1">' || COUNTRY || '</a>' ; AS COUNTRY
BY COUNTRY NOPRINT
ON TABLE PCHOLD FORMAT EXL2K 
END

SET COMPOUND =  CLOSE

TABLE FILE CAR
PRINT CAR MODEL
BY TAB  NOPRINT
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

Well spotted.

Seems that Excel will wrap the text in single quotes, so the below will also work.

SET COMPOUND = OPEN

TABLE FILE CAR
PRINT 
COMPUTE CNTR/I3 = IF LAST CNTR EQ 0 THEN 1 ELSE LAST CNTR + 1 ;
COMPUTE CTYLINK/A50 = '<a href="#''' || COUNTRY || '''!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
END

SET COMPOUND = BYTOC CLOSE

TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END

And back!

  
SET COMPOUND = OPEN

TABLE FILE CAR
PRINT 
COMPUTE CTYLINK/A50 = '<a href="#''' || COUNTRY || '''!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
 TITLETEXT='Countries', $
ENDSTYLE
END

SET COMPOUND = BYTOC CLOSE
DEFINE FILE CAR
PG1/A50='<a href="#''Countries''!A1">' || 'Countries' || '</a>' ;
END

TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
FOOTING
"<PG1 "
ON TABLE PCHOLD FORMAT EXL2K
END

When will it end.......

You guys are awesome!
Still trying to get the link to go the exact cell value instead of A1. If we click on France on SUMMARYRPT it goes to the cell where the value of the country is 'FRANCE' IN DETAILRPT.


-*************************************************

DEFINE FILE CAR
CTYLINK/A100 = '<a href="#''' || 'DETAILRPT' || '''!A1">' || COUNTRY || '</a>' ;
END

 TABLE FILE CAR
HEADING
"Summary Report"
" "
SUM RC
BY CTYLINK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='SUMMARYRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN
END

TABLE FILE CAR
HEADING
"Detail Report"
" "
PRINT SALES
BY COUNTRY
BY MODEL
BY CAR
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='DETAILRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END

That's just a matter of replacing the A1 cell reference in the link variable with a computed variable pointing to the appropriate cell -- column A (constant), row = first row for the respective country = cumulative total of rows for previous countries + the offset of the very first detail row.

TABLE FILE CAR
WRITE CNT.SALES
COMPUTE
  OFFSET/I2=LAST OFFSET+LAST CNT.SALES;
  ROW/I2=OFFSET+4;
  CTYLINK/A50 = '<a href="#''DETAILRPT''!A' || EDIT(ROW) || '">' || COUNTRY || '</a>' ;
BY COUNTRY
ON TABLE HOLD AS LINKS FORMAT FOCUS INDEX COUNTRY
END

JOIN CLEAR *
JOIN COUNTRY IN CAR TO COUNTRY IN LINKS AS L:

TABLE FILE CAR
HEADING
"Summary Report"
" "
SUM RC
BY CTYLINK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='SUMMARYRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN
END

TABLE FILE CAR
HEADING
"Detail Report"
" "
PRINT SALES
BY COUNTRY
BY MODEL
BY CAR
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='DETAILRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END

Great , Thanks.
Just one last quesion,can I get a CAR EXAMPLE to win the mega millions?
Just kiddin :-), Thanks a lot.