Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Hyperlink in Excel from one tab to the next

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Hyperlink in Excel from one tab to the next
 Login/Join
 
Platinum Member
posted
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,


App Studio Version 8202
windows Platform
SQL Server 2008/2012
 
Posts: 183 | Location: TX | Registered: January 22, 2007Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
When will it end.......


Music


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
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



App Studio Version 8202
windows Platform
SQL Server 2008/2012
 
Posts: 183 | Location: TX | Registered: January 22, 2007Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
Great , Thanks.
Just one last quesion,can I get a CAR EXAMPLE to win the mega millions?
Just kiddin :-), Thanks a lot.


App Studio Version 8202
windows Platform
SQL Server 2008/2012
 
Posts: 183 | Location: TX | Registered: January 22, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Hyperlink in Excel from one tab to the next

Copyright © 1996-2020 Information Builders