 |
|||||
|
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. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] WebFocus 8.2.01 Excel drill-down code not working as expected
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] WebFocus 8.2.01 Excel drill-down code not working as expected Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] WebFocus 8.2.01 Excel drill-down code not working as expected | Login/Join |
| Silver Member |
Hi, I am testing WebFOCUS 8.2.01 (currently running 8.0.0.7) - we have code that uses a DEFINE to create a hyperlink to go to the appropriate tab in our excel spreadsheet using EXL2K. In our current version, 8.0.0.7, the code works, but when I run the same code (but change the output to XLSX in the 8.2.01 version, it shows the code in place of the link. Here is code that works using EXL2K: DEFINE FILE CAR BODYLABEL/A70=IF SEATS EQ 1 THEN BODYTYPE ELSE IF SEATS NE 0 THEN '<a href="#''' || BODYTYPE || '''!A1">' || BODYTYPE || '</a>' ELSE BODYTYPE; END -RUN TABLE FILE CAR PRINT BODYLABEL AS 'BODYTYPE' CAR MODEL SEATS DEALER_COST RETAIL_COST SALES LENGTH WIDTH HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL BY COUNTRY NOPRINT ON TABLE PCHOLD FORMAT EXL2K OPEN ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * TYPE=REPORT,FONT='TIMES NEW ROMAN',SIZE=10,COLOR='BLACK',STYLE=NORMAL,GRID=OFF,$ TYPE=REPORT,COLUMN=P1,WRAP=1.20,$ ENDSTYLE END -RUN -* TABLE FILE CAR PRINT COUNTRY CAR MODEL BODYTYPE SEATS DEALER_COST RETAIL_COST SALES LENGTH WIDTH HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL BY BODYTYPE ON TABLE PCHOLD FORMAT EXL2K CLOSE ON TABLE SET HTMLCSS ON ON TABLE SET COMPOUND BYTOC ON TABLE SET STYLE * TYPE=REPORT, FONT='ARIAL', SIZE=9,$ ENDSTYLE END -RUN When changing the EXL2K to XLSX on my other machine that ha 8.2.01 on it, it doesn't work anymore. Any ideas would be appreciated. Thanks. PaulThis message has been edited. Last edited by: FP Mod Chuck, WebFOCUS 8 Windows, All Outputs | ||
|
| Virtuoso |
Paul I took your code and tried to reproduce the problem. First of all I don't see any hyperlinks created in the first cell for EXL2K and when I run it with XLSX I get the same results as with EXL2K. I am running 8.2.01M gen 83 I went back and tried the same code in 8.0.09 and I don't see hyperlinks created in that version either. Thank you for using Focal Point! Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats | |||
|
| Silver Member |
Thanks Chuck, I appreciate you having a look. When I run the report in 8.0.0.7, I get an excel spreadsheet that has the first column as a link, and then when I click it, it goes to the appropriate tab (SEDAN, HARDTOP, etc} I am not sure why the first column (bodytype} isn't a link for you. I was hoping to include a screenshot here so you could see what I am seeing but it doesn't seem to work. Thank you for your efforts. Paul WebFOCUS 8 Windows, All Outputs | |||
|
| Expert |
Paul, the reason we're not getting hyperlinks when we run the code you posted is that the HTML code in your DEFINE statement get's converted to (real) HTML. Please edit your first post by clicking the Folder icon at the bottom right of the post. Then, ALWAYS, add code tags at the beginning and end of your code - this is to prevent the browser from converting HTML code to real HTML. Code tags are easily added via the </> button at the top right of the small post window, or by typing the tags: [code] your code here [/code] Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Expert |
You will note that we cannot see your HTML that creates the hyperlink. Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Expert |
Meanwhile, please check the EXL07/XLSX Format Supported Features Release Roadmap. It appears most of the features have finally arrived in v8.2.01, but the few at the bottom of the feature list have not - does your hyperlink use one of these? Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Silver Member |
Francis, Many thanks for the heads up on the code block issue. I haven't been here for quite a while, and even years ago, I can't remember if I ever put code out here. I think the code looks OK now, so hopefully that will help if others are being gracious enough to try and test it on their end. No, my hyperlink do not have any of those feature - thanks for sharing that link with me. Thanks again. Paul WebFOCUS 8 Windows, All Outputs | |||
|
| Expert |
Your hyperlink works with EXL2K because EXL2K is not a real Excel format - it's HTML. For XLSX, unfortunately, from perusing the web, it appears the only way to create a hyperlink to another sheet is with VBA. Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Silver Member |
Francis, Thank you for your expertise. I searched around a bunch on Friday of last week, but mostly in relation to WebFOCUS and on these forums because I was looking for a solution that was WebFOCUS driven. I do appreciate your help. Thank you. Paul WebFOCUS 8 Windows, All Outputs | |||
|
| Expert |
Almost, but not quite  Use the Excel HYPERLINK function in the DEFINE: IF SEATS NE 0 THEN '=HYPERLINK("#' || BODYTYPE || '!A1","' || BODYTYPE || '")' ELSE BODYTYPE; This generates a proper XLSX hyperlink, but unfortunately the XLSX document has the code displayed in the cell, until you press enter within the formula bar for the cell value - then the code becomes a link. Since the cell is defined as text - the formula displays...This message has been edited. Last edited by: Francis Mariani, Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Platinum Member |
Make sure your JSCOM3 service is up and running in your reporting server. 8.2.06 Windows, All Formats | |||
|
| Silver Member |
Francis, Thank you again for your help. I will be looking into these things further to see if I want to go down the route of adding a macro to this job of ours so the customer can have the link as before, but if that isn't possible, they will have to go to the bottom of the worksheet to click on each tab as opposed to clicking it in the first sheet. Thank you again and have a good day. Paul WebFOCUS 8 Windows, All Outputs | |||
|
| Expert |
In my humble opinion, the only reasons to use XLSX over EXL2K are: 1) The resulting file size is a lot smaller 2) Less confusion for the user when opening the report - no confusing message from Excel, no confusion from WIndows when saving the report Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Silver Member |
Thanks once again Francis. I realize that you don't have full context here. We are upgrading our WebFOCUS requests to accommodate Windows 10 and Excel 2016 so we have to move to a version of WebFOCUS that utilizes Java 8. In addition, the way our tight security is set up, saving a report with EXL2K, at this point, isn't going to be allowed, hence XLSX. So we will lose the functionality of having our customer click on a link in the first Excel spreadsheet and automatically jumping to the correct tab unless we are willing to write a macro to do it. So we are being driven by the upgrade process and the fact that EXL2K, right now anyway, comes up in protected mode and we are not allowed to save the file at all. Thank you for your help with this issue. Paul WebFOCUS 8 Windows, All Outputs | |||
|
| Virtuoso |
I just tried the code Paul M. has provided in 8.201M MR2 with PCHOLD FORMAT XLSX. I see a multi-tab file open, the first tab is called Sheet1 followed by CONVERTABLE, COUPE, HARDTOP, ROADSTER, SEDAN. First column of Sheet1 is BODYTYPE with every value as a Hyperlink (blue/underlined). When I click on any of the values I jump to the tab associated with the value I clicked. I see the same behavior when I run the code with XL2K. The only difference is a warning when use XLSX that says: The file you are trying to open is in a different format than specified by the extension. I click Yes to Open anyway and it opens. WebFOCUS 8206, Unix, Windows | |||
|
| Silver Member |
BabakNYC, Thank you for the reply. So it sounds like you are able to run the request in 8.2.01M (what is MR2?) using XLSX and you get the hyperlink. This is contrary to what Francis said about what the link and what I am finding when I run the request in 8.2.01M Unless the MR2 version is a newer version with that issue resolved, please let me know what MR2 is. Thank you again for having a look at the code and testing it out on your end. Thank you. Paul M. WebFOCUS 8 Windows, All Outputs | |||
|
| Virtuoso |
It's not an issue with 8.2, it also occurs in 8.1.05M. It may be more related with XLSX format. WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
| Silver Member |
MartinY, If you are referring to it not being an issue with a particular release of WebFOCUS, I agree. As Francis pointed out it is the excel format that I am using. We aren't allowed for security reasons (at this time)to used EXL2K with our Windows 10 and Excel 2016 applications. I just found out yesterday that we may be relaxing that since our customer doesn't have that restriction from what I am hearing now. So we may be able to continue to use EXL2K as long as our customer isn't restricting access to that older version of Excel. We need to be consistent with them so when we are testing our applications we should be seeing what they see when they run them. Thank you for your help. Paul M. WebFOCUS 8 Windows, All Outputs | |||
|
| Virtuoso |
MR2 is the latest. However, the code seems to run the same in the original 8.201. I don't have an 8.1 to test it on but I'm also confused by others' findings. WebFOCUS 8206, Unix, Windows | |||
|
| Silver Member |
BabakNYC, Thanks again for the reply. It is interesting that you are able to get it to work using XLSX format in 8.2.01 and 8.2.01M MR2. I am not sure why and don't know what else to ask or say about it, but am grateful for your willingness to test on your end. I guess if anyone else know more about why it was able to work, then I am open to hearing. Have a good day. Paul M. WebFOCUS 8 Windows, All Outputs | |||
|
| Virtuoso |
Once again, I stand corrected. When I used your code, I only noticed and changed the last EXL2K to XLSX. I just went back and tested by changing both to XLSX and now I see the crazy code you were talking about. So, that's why Francis and everybody else can reproduce your problem. I apologize for my hasty attempt. It looks like when I OPEN with EXL2K and CLOSE with XLSX, I end up creating an XLS file anyways, making my test meaningless. At this point, I'd say you should contact IB tech support and show them what you are doing in both versions and see if they can tell us all what's happening. Sorry about confusing everybody, it's been a long month  WebFOCUS 8206, Unix, Windows | |||
|
| Silver Member |
BabakNYC, No worries - thanks for double checking. From what Francis says, there really isn't a problem because EXL2K is not really an Excel format, it is more like HTML, and of course, XLSX is XML based, so I understand why it doesn't work when changed. As Francis mentioned in order to make XLSX become a link I would have to change the code a bit and then run some VBA that would change the code into a link. Have a good day. Paul M. WebFOCUS 8 Windows, All Outputs | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] WebFocus 8.2.01 Excel drill-down code not working as expected
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] WebFocus 8.2.01 Excel drill-down code not working as expectedCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |