Slicer does not work for a specific value

Question

Hello!

I have a problem with a slicer. I've got a slicer with emails and I can click on any email and have the tables filtered accordingly. But there is one specific email (lets say it is "[e-mail]") address that if I click on it sharepoint says this:

"Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connection failed to refresh: PowerPivot Data"

It is weird because all other values in that slicer work and all other slicers work also.

I have another report with a similar slicer that filters the same email column and the email doesn't work either.

Can someone help me?

Just for testing I changed the email from "[e-mail]" to "[e-mail]" just to see if it had something to do with the underscore but it doesn't work either.

What can cause a slicer to work with some values but with others don't? I can't eliminate the filter on the slicer either.

Thanks!

Further information: the ULS logs say this:

CDBConsHost::HandleOledbErrors - hr=80004005 text=XML parsing failed at line 796, column 63: Illegal xml character. .

CDBConsHost::HandleOledbErrors - marking connection as stale. id=9C58D0E8

FailGo returned 'hresult error' 0x800a03ec ; Stack Trace:NA

PF_CHECK_ERROR returned 'hresult error' 0x800a03ec ; Stack Trace:NA 

PF_CHECK_ERROR returned 'hresult error' 0x800a03ec ; Stack Trace:NA

---

Carlos Roberto Vargas

Answer 1

Hi Carlos,

In order to separate the issue, Can the Slicer work well in the Excel before uploading to the SharePoint site?

---

Challen Fu

TechNet Community Support

Answer 2

Hi Challen Fu,

Thanks for the answer and yes. It does work well in Excel.

Thank you very much

---

Carlos Roberto Vargas

Answer 3

Hello!

I have a problem with a slicer. I've got a slicer with emails and I can click on any email and have the tables filtered accordingly. But there is one specific email (lets say it is "[e-mail]") address that if I click on it sharepoint says this:

"Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connection failed to refresh: PowerPivot Data"

It is weird because all other values in that slicer work and all other slicers work also.

I have another report with a similar slicer that filters the same email column and the email doesn't work either.

Can someone help me? I have been struggling with this for a long time :(

Just for testing I changed the email from "[e-mail]" to "[e-mail]" just to see if it had something to do with the underscore but it doesn't work either.

What can cause a slicer to work with some values but with others don't? 

Thanks!

Further information:

• If I open the workbook in Excel it works.
• The workbook references another workbook with the PowerPivot database.
• All other slicers and values work.

• ULS logs say this:

CDBConsHost::HandleOledbErrors - hr=80004005 text=XML parsing failed at line 644, column 63: Illegal xml character. .

PivotCache::MapServerAlert called - stridAlert==52B

CDBConsHost::HandleOledbErrors - marking connection as stale. id=202595C8

Connection.MarkFree: Connection was found to be stale when used against the data provider. Connection=+6dYcR307k23iV7lH93gyg==

ConnectionManager.CleanupSiblingStaleConnections: Scanning for connections of the same connection key and data source version to be marked as stale. ConnectionKey=[[50694542, AMERICAS\$CRITools001], [Oledb, Integrated, , True, ], 1033, ] DataSourceVersion=3/12/2012 1:53:49 PM

ConnectionList.CleanupSiblingStaleConnections: Scanning for sibling stale connections. ConnList=39342339

ConnectionList.CleanupSiblingStaleConnections: Marking connection as stale. Connection=+6dYcR307k23iV7lH93gyg==

FailGo returned 'hresult error' 0x800a03ec ; Stack Trace:NA

PF_CHECK_ERROR returned 'hresult error' 0x800a03ec ; Stack Trace:NA

PF_CHECK_ERROR returned 'hresult error' 0x800a03ec ; Stack Trace:NA

ExternalSource.ExecuteOperation: Caught exception: Microsoft.Office.Excel.Server.CalculationServer.PivotAlertException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.PivotAlertException' was thrown.    
 at Microsoft.Office.Excel.Server.CalculationServer.Interop.WorkbookInterop.ApplySlicerSelection(SlicerOptions slicerOptions, SlicerItemRange[] selectedRanges, SlicerItemRange[] unselectedRanges, ExternalSourceStateInterop[]& states, Int32[]& indices, Int32& count)    
 at Microsoft.Office.Excel.Server.CalculationServer.SessionWorkbook.<>c__DisplayClass21.<ApplySlicerSelection>b__20()    
 at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.ExecuteOperation(Request request, ExternalSourceStateInfo externalSourceStateInfo, ExternalSourceStateInfo prevExternalSourceStateInfo, Int32 index, ConnectionInfoManager connectionInfoManager, ExternalDataScenario scenario, DataOperation dataOperation, Boolean verifyPreOperationConnection), Data Connection Name: PowerPivot Data, SessionId: XXXXXX , UserId: XXXXXX

Result=Id=ExternalDataRefreshFailed_Details; Microsoft.Office.Excel.Server.CalculationServer.SessionException: Unable to refresh data for a data connection in the workbook.  Try again or contact your system administrator. The following connections failed to refresh:    PowerPivot Data    
 at Microsoft.Office.Excel.Server.CalculationServer.SessionWorkbook.DoPivotInteractivity(Request request, Int32 externalSourceIndex, DataOperation dataOperation, Boolean verifyPreOperationConnection)    
 at Microsoft.Office.Excel.Server.CalculationServer.SessionWorkbook.ApplySlicerSelection(Request request, SlicerOptions slicerOptions, SlicerItemRange[] selectedRanges, SlicerItemRange[] unselectedRanges)    
 at Microsoft.Office.Excel.Server.CalculationServer.Operations.ApplySlicerSelectionOperation.StartEx

ExcelService.PostProcessRequest: web method: ApplySlicerSelection, got exception Id=ExternalDataRefreshFailed_Details; Microsoft.Office.Excel.Server.CalculationServer.SessionException: Unable to refresh data for a data connection in the workbook.  Try again or contact your system administrator. The following connections failed to refresh:    PowerPivot Data    
 at Microsoft.Office.Excel.Server.CalculationServer.SessionWorkbook.DoPivotInteractivity(Request request, Int32 externalSourceIndex, DataOperation dataOperation, Boolean verifyPreOperationConnection)    
 at Microsoft.Office.Excel.Server.CalculationServer.SessionWorkbook.ApplySlicerSelection(Request request, SlicerOptions slicerOptions, SlicerItemRange[] selectedRanges, SlicerItemRange[] unselectedRanges)    
 at Microsoft.Office.Excel.Server.CalculationServer.Operations.ApplySlicerSelectionOperation.StartExecution()    
 at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync()    
 at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.PrepareComplete(PrepareAsyncArgs args)

CWorkbookWrapper::FinalRelease: Heap is still referenced! refCount=1481, ID=12892

CWorkbookWrapper::~CWorkbookWrapper: Destroying. ID=12892

---

Carlos Roberto Vargas

Answer 4

Hi Carlos,

Since it is not the issue of workbook, could you please make sure you have already gave the correct credentials to the datasource in the PowerPivot workbook?

If you would like an overview on how to configure data refresh for your workbooks then take a look at this article, http://powerpivotgeek.com/misc/my-other-blog-articles/powerpivot-data-refresh/

For more detailed information on PowerPivot data refresh, review this article http://msdn.microsoft.com/en-us/library/ee210690.aspx

---

Challen Fu

TechNet Community Support

Answer 5

Hi Challen,

Thanks. We use secure store service and the workbook refreshes fine and the credentials are current.

But one specific value in a slicer doesn't work.

Thanks!

---

Carlos Roberto Vargas

Answer 6

Could you please show us the other values in your slicer besides [e-mail]?

---

Challen Fu

TechNet Community Support

Answer 7

Unfortunatedly I cannot provide the listing since they're the company's mail addresses and I cannot publish them.

One thing curious is that if I publish the same report but with old data it works, but if I refresh the report it stops working.

---

Carlos Roberto Vargas

Answer 8

Hi Carlos,

I try to reproduce your error message using the same email address [e-mail] and other email address, but everything works fine, based on your information supplied, it seems that your issue is relevant to the email address, however this is a little inexplicable, in order to separate the issue, could you please change [e-mail] to [e-mail] and then test again to see whether the error show up again.

Thanks,

---

Challen Fu

TechNet Community Support

Answer 9

Challen,

I've found it! Wohoo!

In case someone hits this same issue here is how I fixed it:

• After a lot of tests I found out that Depending on the refresh parameters the Excel client crashed.
• So I start making a lot of test with different refresh parameters to see which failed and which didn't

After a lot of tests I found out that a varchar column that the report grabbed from the datasource had a single character that made the entire app crash. The character is the hexadecimal 0x01. So if a refresh bringed the row that had the character then powerpivot crashed.

What I did was to clean out the data before returning it to the PowerPivot Report, and now everything works fine!

The slicer didn't worked for [e-mail] because carlos_v was the person who had the row with this character.

Regards,

---

Carlos Roberto Vargas