Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I am using an excel workbook with pivot in it. I am using the Excess webpart to display it. As I click on the Refresh all data connection.

It throws an error.The scree shot is attached. enter image description here

I have configured the secure store service and Excel service application. Any Suggestions ??

Thanks in Advance

Regards Kishan

Now the error showing is :

Updated Error

Hi,

Thanks for the reply @1c1cle , @Mauro and @Stuart Pegg.

I have looked into your recomendations. Finally I found where the problem is :

The current senario is :

I have installed SP2010 + Sql Server 2008R2 in server. I have an excel sheet which contains the pivot(the data comes from another sql server).

I want to show that excel file in Sharepoint. I am using the Excel Web Access webpart to show it, but on clicking the Refresh All Data conections, it throws an error. Firstly it was gving an error of Delegation. When i configured the Secure Store Service for Excel services, then the error changes to unable to refresh the Data Connection ,contact your system administrator.

Note: I tried to deploy powerpivot solution in SP 2010, but that solution was not found in the Farm Solution().

I am installing the Powerpivot(Sql Server) in existing setup.So please can you tell me how to proceed with the above mentioned scenario once when the installation of powerpivot is complete ?

Thanks In advance.

share|improve this question
    
Kishan, could you try to perform a Excel connection refresh from a workbook outside a webpart? ( I mean, set up a XLSX file with the data connection, load the file to a document library, view xlsx in browswer). I would like to see if the issue has to do with your webpart or with your Secure Store and connections. Also, check out your IMLS logs. –  1c1cle Jun 7 '12 at 20:57
    
Much Thanks @1c1cle for the reply...!! I have checked it with the browser also, still it is throwing the same error. Just to check i have created the pivot with the Adventure Works database. However the pivot doesn't get refreshed. this time it's showing different error.The screeshot is attached in the Main Question for your reference. I have also checked the logs, but didn't find anything useful. Thanks –  Kishan Jun 8 '12 at 16:18
    
To get the excel services refresh working, if I remember correctly, you need to save the data connection info as a data connection file in a trusted library connection in sharepoint. You can do this by, while in excel client view your data connection info, saving/exporting the connection to a file. then loading the to sharepoint. Then, later your excel file to point to that library location. Also ensure that your SSS field in your authenication settings in your data connection file is set to the name of the Unattended service store. –  1c1cle Jun 11 '12 at 14:40
add comment

3 Answers

The problem that causes this error is when the Claims to Windows token service is using a service account and not the Local System account. It's an easy fix that will get rid of the "Unable to refresh data for a data connection in the workbook" error. To fix the problem follow these steps:

Logon to the server you installed Powerpivot on

Click Start

Click All Programs

Click SharePoint 2010 Products

Click SharePoint 2010 Management Shell (run as administrator)

Type Get-SpServiceInstance (hit enter)

Find and copy the Claims to Windows Token Service ID

Type Get-SPServiceInstance -identity (hit enter)

Type $claims = get-spserviceinstance -identity (hit enter)

$claims.Service.ProcessIdentity.CurrentIdentityType=0 (0 will set the Claims to Local System account)(hit enter)

$claims.Service.ProcessIdentity.Update() (hit enter)

$claims.Service.ProcessIdentity.Deploy() (hit enter)

$claims.Service.ProcessIdentity (hit enter)

Once you have done this reboot your server and go test your workbook and see if it's refreshing the data in the workbook. It should now work with no problems and the nasty error will be gone forever.

share|improve this answer
add comment

I had same problem and resolved it by doing these steps:

  1. Go to Central Administration -> System Settings -> Manage services on the server
  2. Select Claims to Windows Token Service and click Start
  3. Verify that the Unattended User Account has access to the SQL data source
share|improve this answer
    
Much thanks @Mauro but i have Done this . Can you give some more detail about 3rd point ? for the 3rd Point i have given access to the Unattended User Account in Database. Any Suggestion ..?? –  Kishan Jun 8 '12 at 16:24
add comment

If your Unattendend user account have access to your database the problem could be the position of your Excel file . If you save your Excel file on Sharepoint root site level can get error. Save your excel and put the Excel Viewer on a subsite site "blank template". If you have sharepoint 2010 +sp1 you must install October 25 2011 Culmulative update http://support.microsoft.com/kb/2596582.

share|improve this answer
    
Much Thanks @Mauro for reply. I will try this also. Please can you look up at the posted question above ? I have explanined the senario. –  Kishan Jun 11 '12 at 15:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.