다음을 통해 공유

Power View in Excel won’t render from SharePoint

  • 아티클

I originally encountered this issue back in May with a customer.  We had another customer this month that had the same issue.  When you try to load an Excel Document with a Power View Report in it from SharePoint, you may encounter the default unable to load power view report image.

image

Before I get into specifics of the issue we had encountered with a few customers, I’d first like to say that you should validate that Silverlight is installed and working properly.  You may be able to do this by trying a standalone Power View (RDLX) Report outside of Excel. If you have check that and that is working, you may want to go through looking at the details presented in this blog post.

Unfortunately, the image above doesn’t really provide any real guidance as to what to do.  So, with any SharePoint issue, I go to the ULS log to see what is there.  Looking through the ULS log, there is error that stands out.  Nothing that shows an Exception of any kind.

08/21/2014 10:25:26.30 w3wp.exe (0x0C2C) 0x0254 Excel Services Application Excel Calculation Services ah3c5 Verbose MossHost.InteractiveReportServiceUrl: Interactive report rervice URL is missing from the farm properties, property name: InteractiveReportServiceUrl 8ab5b09c-93d3-e002-33bc-206ec188ef95

You may or may not see the following entry:

w3wp.exe (0x16B8) 0x26FC Excel Services Application Excel Calculation Services ahgij Medium Not loading interactive reports since not enabled. 15028e9c-ad22-104d-0114-866c6d407dcd

Looking at a working system, we see the following:

08/21/2014 14:52:27.43 w3wp.exe (0x1330) 0x1508 Excel Services Application Excel Calculation Services ah3c7 Verbose MossHost.InteractiveReportServiceUrl: Interactive report service URL: /_layouts/15/ReportServer/AdHocReportDesigner.aspx?ViewMode=Presentation&DeferredInitialization=true&Fit=true&PreviewBar=false&BackgroundColor=White&AllowSectionNavigation=false&EnableKeepAlive=true&AllowEditViewMode=false&AllowFullScreenViewMode=false d2c4b09c-e336-e002-33bc-2fa5fdb18796

08/21/2014 14:52:27.43 w3wp.exe (0x1330) 0x1508 Excel Services Application Excel Calculation Services ah3da Verbose MossHost.InteractiveReportApiUrl: Interactive report API URL: /_layouts/15/ReportServer/InteractiveReportAPI.js d2c4b09c-e336-e002-33bc-2fa5fdb18796

There are two Properties that will get added to the SharePoint Farm Configuration for Power View.

InteractiveReportServiceUrl – This is the URL Pointer for Excel Services to redirect to the Power View page and render the Power View report.  If this isn’t present, then Excel doesn’t know what to do with the Power View Report that is within the Excel Workbook.

InteractiveReportApiUrl – This is a helper Javascript file for use with Power View.

These properties are part of the SharePoint Farm Configuration.  We can view these via SharePoint by looking at the properties of the SPFarm from PowerShell.

image

You can also see these via SQL via querying the SharePoint Config Database.

SELECT *
FROM [SharePoint_Config].[dbo].[Objects]
Where Properties like '%InteractiveReportServiceUrl%'

image

You won’t get a result back if the values are missing from the farm.  This is a quick way to tell if they are missing.  The Properties output in my farm looks like the following, when those values are present. 

<object type="Microsoft.SharePoint.Administration.SPFarm, Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"><sFld type="Int32" name="m_PersistedFileChunkSize">4194304</sFld><sFld type="Int32" name="m_XsltTransformTimeOut">1</sFld><sFld type="Int32" name="m_cPasswordChangeGuardTime">45</sFld><sFld type="Int32" name="m_cPasswordChangeMaxTries">5</sFld><fld name="m_PasswordChangeEmailAddress" type="null" /><sFld type="Int32" name="m_cDaysBeforePasswordExpirationToSendEmail">10</sFld><sFld type="Boolean" name="m_bUseMinWidthForHtmlPicker">False</sFld><fld name="m_EncodedFarmId" type="null" /><fld type="System.Collections.Generic.HashSet`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" name="m_serverDebugFlags" /><fld name="m_AuthenticationRealm" type="null" /><sFld type="Boolean" name="m_userLicensingEnabled">False</sFld><fld type="System.Collections.Generic.Dictionary`2[[System.Guid, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Version, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" name="m_Versions"><sFld type="Guid">3a9fdacb-c088-420d-9670-043c1417f6f2</sFld><sFld type="Version">14.0.2.0</sFld><sFld type="Guid">66d56e1c-50b2-482e-af1a-7dd7ba0b72cc</sFld><sFld type="Version">15.0.0.0</sFld><sFld type="Guid">00000000-0000-0000-0000-000000000000</sFld><sFld type="Version">15.0.4617.1000</sFld><sFld type="Guid">77e7f90e-1989-46c2-ad65-361a53dcb2e0</sFld><sFld type="Version">15.0.1.0</sFld><sFld type="Guid">54d00007-0f81-42b1-8f06-fb9b981a617d</sFld><sFld type="Version">14.0.1.0</sFld><sFld type="Guid">6ac833ea-3f8d-46b6-8b30-92ac4553a742</sFld><sFld type="Version">15.0.1.0</sFld><sFld type="Guid">6371575d-8eae-41dd-903f-b9fbc2da7aad</sFld><sFld type="Version">15.0.1.0</sFld><sFld type="Guid">c8a0b463-1852-4f3b-8fd3-216c4d19585a</sFld><sFld type="Version">15.0.1.0</sFld><sFld type="Guid">42c6e513-ad52-4d28-93d6-d07d1afd7b14</sFld><sFld type="Version">15.0.2.0</sFld></fld><fld name="m_UpgradeContext" type="null" /><fld type="System.Collections.Hashtable, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" name="m_UpgradedPersistedFields" /><fld type="System.Collections.Hashtable, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" name="m_Properties"><sFld type="String">WopiLicensing</sFld><sFld type="String">HostBIEnabled</sFld><sFld type="String">InteractiveReportApiUrl</sFld><sFld type="String">/_layouts/15/ReportServer/InteractiveReportAPI.js</sFld><sFld type="String">DisableIntranetCallsFromApps</sFld><sFld type="Boolean">True</sFld><sFld type="String">DisableIntranetCalls</sFld><sFld type="Boolean">True</sFld><sFld type="String">InteractiveReportServiceUrl</sFld><sFld type="String">/_layouts/15/ReportServer/AdHocReportDesigner.aspx?ViewMode=Presentation&amp;DeferredInitialization=true&amp;Fit=true&amp;PreviewBar=false&amp;BackgroundColor=White&amp;AllowSectionNavigation=false&amp;EnableKeepAlive=true&amp;AllowEditViewMode=false&amp;AllowFullScreenViewMode=false</sFld><sFld type="String">GuestSharingEnabled</sFld><sFld type="Boolean">True</sFld></fld><sFld type="String" name="m_LastUpdatedUser">BATTLESTAR\asaxton</sFld><sFld type="String" name="m_LastUpdatedProcess">psconfigui (3468)</sFld><sFld type="String" name="m_LastUpdatedMachine">ADMADAMA</sFld><sFld type="DateTime" name="m_LastUpdatedTime">2014-06-20T14:53:36</sFld></object>

So, what do we do if they are missing?  I was able to correct it by adding them back via PowerShell. Here are the PowerShell Commands for SharePoint 2013.

# Get the SPFarm object and list out the existing properties to verify
# the ones we are looking for are missing.
$farm = Get-SPFarm
$farm.Properties

# Set the Property Name and Value to add - InteractiveReportServiceUrl
$propName = "InteractiveReportServiceUrl"
$propValue = "/_layouts/15/ReportServer/AdHocReportDesigner.aspx?ViewMode=Presentation&DeferredInitialization=true&Fit=true&PreviewBar=false&BackgroundColor=White&AllowSectionNavigation=false&EnableKeepAlive=true&AllowEditViewMode=false&AllowFullScreenViewMode=false"

# Add the InteractiveReportServiceUrl Property to the Server Farm
$farm.Properties.Add($propName, $propValue);

# Set the Property Name and Value to add - InteractiveReportApiUrl
$propName = "InteractiveReportApiUrl"
$propValue = "/_layouts/15/ReportServer/InteractiveReportAPI.js"

# Add the InteractiveReportApiUrl Property to the Server Farm
$farm.Properties.Add($propName, $propValue);

# Propagate the changes back to the Database
# true to silently reinitialize the object with an existing object's data if the object already exists in the configuration store;
# false to throw an exception if the object already exists.
$farm.Update($FALSE)

If the properties are there and are blank, or have the wrong value, you can do the following to update the existing properties.

$farm.Properties[$propName] = $propValue; 

instead of

$farm.Properties.Add($propName, $propValue);

Once that is done, you’ll want to do an IISRESET on your servers for the Config information to get loaded.  Then try your Power View Report again and it should come up.

How does the configuration get into this state?  I don’t know for sure, but my thoughts are that it has to do with the order in which the Service Apps were installed.  If the RS Service was configured before the Excel Calculation Service, we may get into this state.  Regardless, the commands above should get it back into working status.

 

Adam W. Saxton | Microsoft SQL Server Escalation Services
https://twitter.com/awsaxton

Comments

  • Anonymous
    January 19, 2015
    Thanks Adam, this really saved our day!

  • Anonymous
    April 30, 2015
    I am having a simple 2 row excel data, Powerview report in SharePoint not render either.  Saying it can't find the EntityDataSource. I created a powerview on top of 2 rows of Excel data and made it into a clustered bar chart.  Works fine in other environments, not in our staging.  Can't seem to figure out why.  Tech support is scratching their heads too.  The values you listed above are set correctly. We are on premise SharePoint 2013 Sp1, SQL 2014 for BI toolset on Windows 2008 R2 Sp1 OS VM's.