Friday, November 8, 2013

Sharepoint Guidance - Excel Web Services : Named Item Error Resolution

The named item cannot be displayed

Before we start troubleshooting on this, let’s try to understand the term-

What is meant by name item? - In this view, you can only display one or more named items in the workbook. A named item can be one of the following:
1)      A chart
2)      An Excel table
3)      A PivotTable report
4)      A PivotChart report
5)      Named range of cells.

How I encountered this error? – I have added an excel web access web part on my SharePoint site collection, modify the webpart and entered the details for workbook location and name item field by specifying tab7 inside the excel spreadsheet as I don’t want to display my spreadsheet in the EWA.

As soon as I clicked ok, the excel web access webpart throws an exception as follows:
The named item cannot be displayed. The name item is either not available in the workbook or was not selected as a name item for viewing when the workbook was published. The name item may also be located in a hidden range of cells or in a collapsed outline.










Action taken / troubleshooting done:
1.       Checked the status of excel service application and it was fine
2.       Checked the excel service status by means of Central Administration- services on server and it was fine too.
3.       Need to isolate the problem first- whether its farm specific or file specific or EWA web part specific.
4.       Checked the document library where all the excel documents were stored. Clicked on the dropdown of any excel file and selected ‘view in browser’ – it opens up fine!
5.       Came back to the excel web access webpart- modify it and remove the name item field and kept it blank – click apply & checked the results
6.       With the above action- the excel web access web part loads the excel file without any problems.
7.       So far so good! Still trying to find out the resolution!

Resolution:-

Change Published Items within a Workbook: -
When we initially published our workbook to Excel Services, we simply gave it a name and accepted the default values. Whenever we click the Save icon, rather than re-publishing the workbook, we’re merely saving the data back to the document library. The significance here is that when publishing a workbook to Excel Services, we have the option of specifying additional metadata, but when saving, the metadata is not changed. We received the error because the metadata did not contain details of our new named item.

1.       Open the excel file on your desktop (not from the SharePoint site, if you don’t have saved on the desktop then please save it.)
2.       On the top ribbon- click on File
3.       Click on Share
4.       Publish to Excel Services
5.       Current location
6.       Save as - automatically displays the contents of our Excel Workbooks document library
7.       Click Publish to Excel services to open the save as dialogue
8.       Excel Services Options button now appears in the bottom
9.       Excel Service Options button
10.   Here we need to define or overwrite metadata for the document

In the Excel Services Options dialog’s Show tab, select Items In The Workbook from the drop-down list.

Check the All Named Ranges and the All Charts options to ensure that they will be available for use by the EWA web part

Click Save to complete the publishing process

That’s it- You are done!

Make the necessary changes inside the excel web access (EWA) and check the results.

No comments:

Post a Comment