topic Excel Online not working in PBIRS in Report Server

Excel Online not working in PBIRS

JJones1505 — Mon, 18 Sep 2017 14:02:35 GMT

We downloaded and installed the PBIRS preview for August. Pbix files work as do RDL reports. I can upload Excel files as well. We also installed OOS on the test PBIRS server and configured using http. The issue I have is when i click to open the Excel file, I receive an error: "Unable to Open the file. We couldn't find the file you wanted. It's possible the file was renamed,moved or deleted." The file was not any of these suggestions. It is where I specified on upload. I have searched for this issues but i get info related to SharePoint, which this is not. What am I missing? Thanks!

Re: Excel Online not working in PBIRS

jbeauche — Tue, 19 Sep 2017 17:02:11 GMT

Similar issue here.

Excel files can be rendered properly in Excel Online (SP 2016 + Office Online On Prem), but not from PBIRS August 2017 Preview.

Received an error regarding the address https://server.xyz.com/hosting/discovery , to check if it is correct.

The settings is Ok and the address is good and it's working properly.

At the end, can't render Excel file from PBIRS.

Any help or suggestions would be appreciated,

Thanks

Jean

Re: Excel Online not working in PBIRS

chrisfin — Thu, 21 Sep 2017 22:01:29 GMT

Are both OOS and PBI Report Server on the same machine? If so, try restarting the Power BI Report Server service on the machine and see if that addresses the issue. Don't restart the entire server, just the service itself. That should clear up the issue.

Re: Excel Online not working in PBIRS

JJones1505 — Fri, 22 Sep 2017 12:33:03 GMT

Yes, both PBIRS and OOS are on the same server. I tried restarting the services first thing as well and the error still comes up.

Re: Excel Online not working in PBIRS

chrisfin — Fri, 22 Sep 2017 15:35:13 GMT

Something to check is the following folder location - C:\ProgramData\Microsoft\OfficeWebApps\Data\FarmState\Topology.xml

It'll show you the current machine state. If it says "Unknown", then the machine has somehow gotten into an unrecoverable state for Office Online Server and you'll need to start fresh on a new VM. There are some articles on the web stating you should be able to fix the XML and have it start working again, but we've not seen that actually work in our case. This happens very infrequently, but we've had it occur once or twice and haven't found a way to recover from this state. It doesn't have anything to do with Report Server from what we can tell, but rather some combination of items/settings on the machine. We've asked for additional details from the Office server team to understand what that may be.

If it says "Healthy" or "Unhealthy", then it should still be in a state where it can be recovered and usually restarting the service will do that. You might want to look at the ULS logs in OOS to see if anything jumps out there as well (the logs are in the Data folder in the path I gave you above). Beyond that, you could look to set things up on two different machines (OOS on one, PBI RS on another) and see if you still have the issue.

FWIW, when the features in August preview go live in the next update, we have improved this setup experience so you shouldn't ever need to restart the service after initial setup just to get things working in certain cases.

Re: Excel Online not working in PBIRS

JJones1505 — Mon, 25 Sep 2017 12:57:16 GMT

The Topology.xml file states that everthing is "healthy". There isn't much else in that file, should there be? Also, i did not see anything that stood out in th ULS logs so i am at a loss, other than setting up two different machines for this configuration.

Re: Excel Online not working in PBIRS

chrisfin — Mon, 25 Sep 2017 14:07:50 GMT

Hmm - one last thing, are you using the "internal" or "external" url you setup for OOS as the URL? You could try switching the URL you're using from one to the other (though it shouldn't make a difference). Failing that, I'd ask you contact Microsoft support so they can dig into it more thoroughly with you.

Re: Excel Online not working in PBIRS

JJones1505 — Mon, 25 Sep 2017 14:12:50 GMT

That was the only thing i had left to try. I am using the internal URL. I need to configure it for the External. Thanks for the help!.

Re: Excel Online not working in PBIRS

jbeauche — Mon, 25 Sep 2017 19:15:41 GMT

On my side the machine state is Healthy. And I still receive the following error in PBIRS:

"The page cannot be displayed

Check the Web address https://ab-cdef12.ghi.klmn is correct"

This address correspond to the prefix of the url for the Office Online Server Discovery Endpoint URL, but truncated by 1 character.

The full url in PBIRS is:

https://ab-cdef12.ghi.klmnO/hosting/discovery

I'm not sure why in the error message, the url prefix is truncated by 1 characters.

Is there a limitation on the length for the discovery endpoint URL ?

If I check the URL, https://ab-cdef12.ghi.klmnO/hosting/discovery it is working properly.

And on SharePoint , Excel files can be rendered properly in the browser.

Any help would be appreciated,

Thanks

Jean

Re: Excel Online not working in PBIRS

chrisfin — Tue, 26 Sep 2017 12:34:09 GMT

If it's on the same machine, can you try using localhost vs. that URL? Also, do you have the external URL configured? You could try that as well. Also, if you are using a self-signed certificate, you need to make sure it is trusted on the server for the connection to work properly.

We'll look at the URL dropping a character - seems a little odd, obviously.

Re: Excel Online not working in PBIRS

valentiniliescu — Tue, 26 Sep 2017 15:34:16 GMT

Can you check (in the browser) the IFRAME URL? Search for a form with ID "office_form" in the HTML source code, and it should have an action attribute of format https://ab-cdef12.ghi.klmnO/x/_layouts/xlviewerinternal.aspx?WOPIsrc=...

Also, in the discovery document, what is the corresponding URL? Search for an action element of format

<action name="view" ext="xlsx" default="true" urlsrc="https://ab-cdef12.ghi.klmnO/x/_layouts/xlviewerinternal.aspx?<ui=UI_LLCC&><rs=DC_LLCC&><dchat=DISABLE_CHAT&><IsLicensedUser=BUSINESS_USER&>"/>

In both cases, see if the URL hosts are truncated or not.

Re: Excel Online not working in PBIRS

valentiniliescu — Tue, 26 Sep 2017 15:36:39 GMT

Also, is the last character in the host URL (the O in your example) a special character?

Re: Excel Online not working in PBIRS

jbeauche — Tue, 26 Sep 2017 17:51:55 GMT

No. I've put O in upper case as an example (have to hide the real url) . It is not a special character.

To answer the previous question, here what I've found:

<form name="office_form" class="ng-pristine ng-valid" id="office_form" action=" https://ab-cdef12.ghi.klmn/x/_layouts/xlviewerinternal.aspx?WOPIsrc=http%3A%2F%2Fsm-dapp96%3A80%2Fwopi%2Ffiles%2Fde99c5a3-9273-4599-a7d1-8c8f5e4cdb82&ui=fr-FR" method="post" target="office_frame">
            <input name="access_token" type="hidden" value="m+hMpOGOnYIXCYbAtv5cvvyTPVexeU5M2/vkqVylW0FxR5XVA65trJNFPfhVI7UfRBdNIlDhWLGEekFaQlvZe+3QkF6q311hOMYsUbbQ5W8aVT/RAigQeY7Tmwhc59EKoXYfTzsuo5amOcw7ZbPvZobRlurfy54b6/5PLMYqgXxltrG4H6XvyMupOPfSHOkkUex+4Wf1TNLXL4p7UFSz93nibLO0ifzQxWyfurCg7e7vrulu8H0kvVqE8KnyEJNT8fc+PtaziQORb7DkHj8qSLv1uZiW3F6EIs/kxMztmPME/MxIPCmICV1AvuRrw1ER2nVjSRzhbDIiYCo35LI47EKSVVTYmWKHt2DodCNjDIEVXeDrl95MdOh22ChreSCBkYo/A5uU2LqgGy727A3o00ZoJddIHOV2nNJnCjseiM4Ww6L6XpDOzQ==">
            <input name="access_token_ttl" type="hidden" value="0">
        </form>

Discovery document:

<action name="view" urlsrc=" https://ab-wxyz89.ghi.klmnO/x/_layouts/xlviewerinternal.aspx?<ui=UI_LLCC&><rs=DC_LLCC&><dchat=DISABLE_CHAT&><IsLicensedUser=BUSINESS_USER&>" default="true" ext="xlsx"/>

Please note that SharePoint Server is on a separate server in the same Domain.

PBIRS server : https://ab-cdef12

SharePoint server: https://ab-wxyz89

It is truncated only in PBIRS. But in the PBIRS setting page, the full url not truncated is there.

Thanks for your help.

Re: Excel Online not working in PBIRS

valentiniliescu — Tue, 26 Sep 2017 18:23:13 GMT

What is the URL of the Office Online Server? https://ab-wxyz89 or https://ab-cdef12?

Re: Excel Online not working in PBIRS

jbeauche — Tue, 26 Sep 2017 18:41:02 GMT

Sorry for the confusion

https://ab-wxyz89 is for Office Online Server

https://ab-cdef12 is for PBIRS

and
https://ab-rstu34 is for SharePoint Server

OOS is on a separate server than SPS. From SPS we can render an Excel document using the same OOS as we are trying with PBIRS

Re: Excel Online not working in PBIRS

valentiniliescu — Tue, 26 Sep 2017 19:38:44 GMT

To make sure I got it correctly:

https://ab-wxyz89.ghi.klmnO is the full URL of Office Online Server

https://ab-wxyz89.ghi.klmnO/hosting/discovery is the Office Online Server discovery URL, which is what was input in the PBIRS Site Settings

The discovery document contains this
<action name="view" urlsrc="https://ab-wxyz89.ghi.klmnO/x/_layouts/xlviewerinternal.aspx?<ui=UI_LLCC&><rs=DC_LLCC&><dchat=DISABLE_CHAT&><IsLicensedUser=BUSINESS_USER&>" default="true" ext="xlsx"/>

The form is of format:
<form name="office_form" class="ng-pristine ng-valid" id="office_form" action="https://ab-wxyz89.ghi.klmn/x/_layouts/xlviewerinternal.aspx?WOPIsrc=http%3A%2F%2Fsm-dapp96%3A80%2Fwopi%2Ffiles%2Fde99c5a3-9273-4599-a7d1-8c8f5e4cdb82&ui=fr-FR" method="post" target="office_frame">

So basically, all of them should have the same URL (https://ab-wxyz89.ghi.klmnO) but the last one is actually missing the last character.

Am I right?

Re: Excel Online not working in PBIRS

jbeauche — Tue, 26 Sep 2017 19:58:19 GMT

Yes you're right.

I imported an Excel document into a folder in PBIRS. Then I click on the Excel document to run it I've received the following error:

"The page cannot be displayed
Check the Web address https://ab-cdef12.ghi.klmn is correct"

where the O is missing in the url in the error message... and the setting is correct in PBIRS: https://ab-wxyz89.ghi.klmnO/hosting/discovery

And looking in the HTML source code I can see it's truncated:

The Excel document can't be rendered within PBIRS

Thanks again!

Re: Excel Online not working in PBIRS

valentiniliescu — Tue, 26 Sep 2017 20:34:10 GMT

Can you post the internal and external URLs (obfuscated, of course) for Office Online Server?

Go to the OOS machine and run this PowerShell command: Set-OfficeWebAppsFarm

This will list a lot of settings, but I am interested only in InternalURL and ExternalURL

Re: Excel Online not working in PBIRS

JJones1505 — Wed, 27 Sep 2017 12:03:54 GMT

I reconfigured the OOS to use the FQDN. Set up worked without issues. Still get the same error when clicking on the Excel file to open. I debugged the session and found these errors:

SCRIPT7002: XMLHttpRequest: Network Error 0x80070005, Access is denied.

http://<SERVERNAME>/reports/assets/css/bootstrap.css.map HTTP GET 404 0 B 233.82 ms XMLHttpRequest

So the 404 is cause by the access denied? Why do i not have access to my Excel files?

Re: Excel Online not working in PBIRS

jbeauche — Wed, 27 Sep 2017 13:17:48 GMT

On the OOS server both InternalURL and ExternalURL are identical: https://ab-wxyz89.ghi.klmnO

Not truncated...

I don't understand why the PBIRS seems to truncate the OOS Discovery Endpoint URL settings before trying to call the OOS server.

Thanks!