Focal Point
[CLOSED] Drilling down thru Excel

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5867037806

August 23, 2011, 10:21 AM
Jveselka
[CLOSED] Drilling down thru Excel
I have a report that is set to output with either HTML, PDF or EXL2K. When I run the report and output it with HTML or PDF my drilldown links on the page work fine. When I run the report and output to Excel my drilldown links are active but when I click them I get the following error message....

You are not allowed to access this viewer file.

We are using Office 2010 and we have other reports that are able to drilldown in Excel.

I tried to run the report on another machine that was using Office 2007 and when I output to Excel the links appear to be active but when I clcik them nothing happens.

Any help would be appreciated.

Thanks
Jeremy

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


WebFOCUS 8105m
Windows, All Outputs
August 24, 2011, 09:42 AM
RSquared
Jeremy,

Can you please posst your code. I have done many drilldownds with Excel output and they work just fine.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
August 25, 2011, 09:40 AM
<JJI>
Hi,

Jeremy,
I think this has something to do with the properties of the file extension .xls.

Possible solution could be this:
-In Windows explorer go to Folder option/File Types.
-Select the extension that you need (XLS in your case)
- Click the Advanced button
- Check the "Browse in same window" option.

Rerun your report and now it should display in your browser and the url should work.

Hope this works for you.

Kind regards,
August 25, 2011, 10:48 PM
njsden
I was looking for an explanation as to why Excel reports opened inside of IE in one computer and would start and open inside of Excel in a different one. We knew there had to be a setting somewhere but ended up as one of those misterious things that someday would be solved.

Well, the day has finally come! Thanks Dirk.

Sorry to hack your post Jeremy as this is not necessarily an answer to your issue.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
August 26, 2011, 07:29 AM
<JJI>
Hi njdsen,

Glad this solved your problem.
Jeremy,I hope it works for you to.
August 31, 2011, 11:55 AM
Jveselka
Thanks for the feedback unfortunately that didn't work for me. I am set to open with .xls and the Browser in same window box was checked.

RSquared here is my code. It works fine thru HTML but not Excel. I want to say I am missing something in my FOCEXEC command but I am not sure what that is.....


ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=DATA,
COLUMN=N4,
TARGET='_top',
FOCEXEC=uw_cas_wrtcty_drilldown(WrtCty=N4),
$
TYPE=DATA,
COLUMN=N3,
TARGET='_top',
FOCEXEC=uw_cas_agcy_drilldown(AgcyNum=N3),
$
TYPE=DATA,
COLUMN=N1,
TARGET='_top',
FOCEXEC=uw_cas_cl_drilldown(ClmNum=N1),
$
TYPE=DATA,
COLUMN=N5,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N6,
JUSTIFY=RIGHT,
$
TYPE=DATA,
COLUMN=N7,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N8,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N9,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N10,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N12,
JUSTIFY=RIGHT,
$
TYPE=DATA,
COLUMN=N13,
JUSTIFY=RIGHT,
$
TYPE=DATA,
COLUMN=N11,
JUSTIFY=CENTER,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N5,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N6,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N7,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N8,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N9,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N10,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N12,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N13,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N11,
JUSTIFY=CENTER,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=2,
JUSTIFY=CENTER,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N11,
SQUEEZE=1.277778,
$
ENDSTYLE
END
-RUN
-IF &LINES NE 0 THEN GOTO ENDING;
-HTMLFORM BEGIN


No Data for "Current Selection".

-HTMLFORM END
-ENDING


WebFOCUS 8105m
Windows, All Outputs
September 12, 2011, 04:49 AM
Wep5622
Is there any progress on this? We are starting to get complaints from users who can't use drilldowns in Excel 2010.

I realise the problem is likely to be somewhere in Excel and not in WebFOCUS, but having a documented and usable workaround would be a good start.

Perhaps if we figure out what is causing the problem, we can figure out a solution from that?

The problem appears to be related to some new security feature in Excel 2010 that doesn't exist in Excel 2003.
Another possibility is that Excel gained the ability to perform web browsing requests by itself, whereas Excel 2003 dispatched those requests to an Internet Explorer session.
I'm just guessing here, having little knowledge of Windows or Excel, so correct me if I'm wrong.

Anyway, let's get this ball rolling, shall we?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
September 14, 2011, 04:11 AM
Wep5622
Nobody? I guess I'll have to suffer opening a case with IBI then... Frowner


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
September 14, 2011, 10:50 AM
j.gross
quote:
You are not allowed to access this viewer file.

Try Googling that message.

Here's a 2005 post on the same message (in a similar different context), with a response.
September 15, 2011, 03:41 PM
Jveselka
Ok, I have taken another look at this and I think it may be that drilldowns will not output to Excel from an Excel document. Clear as mud?

Ok,
My report is setup to output to HTML and when I run it this way the drilldowns on the output work fine. They open in Excel like they are supposed to.

When I run a report and output it to Excel it works fine. My report opens and all my drilldowns are active but here is where I run into problems. My drilldowns are also set to output to Excel and it is here that I get the error.

I changed the output for my drilldowns to HTML and it all works fine. So this is why I think it may have something to do with open an Excel document from and Excel document. Maybe it needs to get out and come back in before it will open.

What I may have to do is set my drilldowns to output to HTML and then place a link on the HTML report to convert it back to Excel.

If anyone has a better idea please let me know.

Thanks

Jeremy


WebFOCUS 8105m
Windows, All Outputs
September 16, 2011, 05:54 AM
<JJI>
quote:
When I run a report and output it to Excel it works fine


Jeremy,

Could you please tell if the output displays in the browser or if it displays directly in Excel Gui?

Regards,
October 04, 2011, 09:12 AM
Wep5622
I just got a solution from customer support for the viewer file error on drilldowns from Excel 2010 to Excel 2010:

quote:
This issue is likely cookie related.

To address this issue, change the setting for REDIRECT_COOKIE from "ON" to
"OFF":

  • Log into the WebFOCUS administration console:
    h t tp:/ /server: port/ibi_apps/console/webfocusconsole.jsp
  • Go to Configuration then go to Cookies.
  • Change the value of "REDIRECT_COOKIE" to "OFF".
  • Save changes.
  • Click on "Clear cache".




WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
October 05, 2011, 09:52 AM
ERINP
Wep5622,
Did you open a case for this issue? If yes, what is the case number? Do you know what other items may be impacted by setting the value for REDIRECT_COOKIE to OFF?

Thanks,
ERINP


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
October 05, 2011, 10:27 AM
Wep5622
quote:
Originally posted by ERINP:
Did you open a case for this issue? If yes, what is the case number?


I did, the case number is 72572508.

Turning off those cookies does impact security. The cookie is meant to uniquely identify a client to the web(focus)-server. As far as I see the risk is fairly small; normally the same user performs both GET-requests.

I think it requires a malignant user to try and perform some kind of man-in-the-middle attack to trigger this security issue. I expect that most of us run WebFOCUS on a corporate intranet, in which case the risks are negligable to begin with and anyone successfully performing such an attack would be standing outside carrying a cardboard box with their private belongings in no time Wink


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
October 05, 2011, 10:34 AM
ERINP
Wep5622,
Thanks for the info. I will have the director of MIS take a look at this case number and your post to see if the possible security issue is a concern or not. This may impact our current security model and may be an issue with newer releases of webFOCUS and Excel.

Thanks,
ERINP


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
January 06, 2012, 05:34 AM
Wep5622
We just confirmed that there is another way to solve this issue, that does not have earlier mentioned security issue.

In our case, we have a number of "Custom Settings" (see webconsole/configuration) in which we used <sendvar> parameters to have some global parameters available to us (in our case the user ID and the users' Active Directory key). It turns out that <sendvar> is deprecated or something and that you're supposed to use <SET> now.

Converting those <sendvar>-options to <SET>-options actually solved this problem! So now we can follow drilldown links in Excel without the security implications of turning the redirect-cookie off.

Thank you Maryann Federico for figuring this out for us Wink


The only remaining problem now is that annoying popup each time saying that the file is not in the format that the .xls extension suggests it is. And Excel is right about that! The file is actually an xhtml file! It starts with:
<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
  
<HEAD>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">


Is there a solution to that too?

(EDIT: Just noticed that the parameter-tags had disappeared from within the text, which made this a bit of a non-sensical post)

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


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
January 09, 2012, 04:57 PM
MathematicalRob
quote:

The only remaining problem now is that annoying popup each time saying that the file is not in the format that the .xls extension suggests it is. And Excel is right about that! The file is actually an xhtml file! It starts with:
xmlnsRed Face="urn:schemas-microsoft-comRed FacefficeRed Faceffice"
xmlns:x="urn:schemas-microsoft-comRed Faceffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">




Is there a solution to that too?


In Office 2007, this was a security feature known as "extension hardening". A .reg file could be created with the following contents to turn extension hardening off and rid one's self of the message:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security]
"ExtensionHardening"=dword:00000000



WebFocus 8201m on Windows; App Studio 8201; Procedures: WebFocus with SQL; HTML Parameter Pages & Dashboard; Output: Excel, HTML, & PDF.