locked
Further understanding of Data Connection limitations needed RRS feed

  • Question

  • Hello,

    We are struggling to understand our limitations with data connections and hope to gain some further understanding before moving forward

     

    We have a workbook that we want to publish in which one worksheet will be displayed to the company through an Excel web part.  This worksheet contains one table and two pivot charts. These items contain formulas that calculate values based on data that is pulled into a couple tables (non-pivot tables) which live on other worksheets that will not be published. 

     

    These table are getting data through ODC connections as desired when working with the files locally, and summary table and charts update as desired also.  Upon publishing this workbook we consistently get the standard “Unable to Load Workbook” error. This occurs no matter what combination of worksheets I select to publish.  I then created a new workbook in which I created 3 pivot tables that pull the data using the same ODC connections.  After publishing this, the workbook will open with Excel services just fine, but it will require us completely rebuilding the Summary Data worksheet to reference these new pivot tables rather than the standard tables.  Why is this a problem, even if we don’t want to publish the original standard tables for folks to view?

    Friday, October 28, 2011 11:22 PM

Answers

  • Hello PBFred,

     

    You stated, “This worksheet contains one table and two pivot charts.”

     

    A Query Table is not supported (see below URL).  I assume that is why you are getting the error and why you are not getting the error when you exclude the Table.

     

    Excel Services part 12: Unsupported features

    http://blogs.office.com/b/microsoft-excel/archive/2005/12/01/excel-services-part-12-unsupported-features.aspx

     

    Excel Spreadsheets that will not load on the first version of Excel Services
    Spreadsheets that contain one or more of the following features will not load in Excel Services.

    · Spreadsheets with code. This includes spreadsheets with VBA macros, forms controls, toolbox controls, MS 5.0 Dialogs, and XLM Sheets.

    · IRM-protected spreadsheets

    · ActiveX Controls

    · Embedded SmartTags

    · PivotTables based on “multiple consolidation” ranges

    · External references (links to other spreadsheets)

    · Spreadsheets saved in formula view

    · XML expansion packs

    · XML Maps

    · Data validation

    · Query Tables, SharePoint Lists, Web Queries, and Text Queries

    · Spreadsheets that reference add-ins

    · Spreadsheets that use the RTD() function

    · Spreadsheet that use spreadsheet and sheet protection

    · Embedded pictures or clip art

    · Cell and Sheet background pictures

    · AutoShapes and WordArt

    · Ink Annotations

    · Organization Charts and Diagrams.

    · DDE Links

     

    Additional URL:

    ============

    Excel Services in SharePoint 2010 Feature Support

    http://blogs.msdn.com/b/excel/archive/2009/11/19/excel-services-in-sharepoint-2010-feature-support.aspx

     

    • Marked as answer by PbFred Tuesday, November 8, 2011 11:47 PM
    Monday, November 7, 2011 9:33 PM

All replies

  • Hi PbFred,

     

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

     

    Thank you for your understanding and support.


    Regards, Daniel
    Wednesday, November 2, 2011 11:53 AM
    Moderator
  • Thank you Daniel.  We'll keep watching as this is for a company-wide "dashboard" report that we really want to automate the updates on..
    Wednesday, November 2, 2011 3:29 PM
  • Hello again Daniel,

    Just curious to know if there has been any follow-up discussion about this on your end?

    Monday, November 7, 2011 7:25 PM
  • Hello PBFred,

     

    You stated, “This worksheet contains one table and two pivot charts.”

     

    A Query Table is not supported (see below URL).  I assume that is why you are getting the error and why you are not getting the error when you exclude the Table.

     

    Excel Services part 12: Unsupported features

    http://blogs.office.com/b/microsoft-excel/archive/2005/12/01/excel-services-part-12-unsupported-features.aspx

     

    Excel Spreadsheets that will not load on the first version of Excel Services
    Spreadsheets that contain one or more of the following features will not load in Excel Services.

    · Spreadsheets with code. This includes spreadsheets with VBA macros, forms controls, toolbox controls, MS 5.0 Dialogs, and XLM Sheets.

    · IRM-protected spreadsheets

    · ActiveX Controls

    · Embedded SmartTags

    · PivotTables based on “multiple consolidation” ranges

    · External references (links to other spreadsheets)

    · Spreadsheets saved in formula view

    · XML expansion packs

    · XML Maps

    · Data validation

    · Query Tables, SharePoint Lists, Web Queries, and Text Queries

    · Spreadsheets that reference add-ins

    · Spreadsheets that use the RTD() function

    · Spreadsheet that use spreadsheet and sheet protection

    · Embedded pictures or clip art

    · Cell and Sheet background pictures

    · AutoShapes and WordArt

    · Ink Annotations

    · Organization Charts and Diagrams.

    · DDE Links

     

    Additional URL:

    ============

    Excel Services in SharePoint 2010 Feature Support

    http://blogs.msdn.com/b/excel/archive/2009/11/19/excel-services-in-sharepoint-2010-feature-support.aspx

     

    • Marked as answer by PbFred Tuesday, November 8, 2011 11:47 PM
    Monday, November 7, 2011 9:33 PM
  • Thanks for the clarrification on this Tom. 

    We have updated the report to pull the data into a PivotTable and it looks like this is going to work.

    Just so I know, why does this not work with a "Query table" versus a PivotTable, if we don't want to publish it but are only trying to use the table as the date source for the Pivot charts that we do want to publish.

    My Excel guy is dumbfounded and I didn't have a definitive answer for him.

    Does Excel Web Access support query tables in 2010?

    Tuesday, November 8, 2011 11:55 PM
  • Hey PbFred,

     

    This blog should answer your question about Query Tables.

     

    Query Tables work-around for Excel Services
    http://blogs.msdn.com/b/cumgranosalis/archive/2006/11/03/query-tables-work-around-for-excel-services.aspx

     

    This should answer your questions about 2010:

    Excel Services 2010 Overview
    http://blogs.office.com/b/microsoft-excel/archive/2009/11/03/excel-services-2010-overview.aspx

    Better symmetry across Excel and Excel Services:

    We heard your feedback regarding file support . . . and have changed our paradigm from refusing to open files which contain unsupported features to making our best effort to open any workbook. For features we partially support, we either show cached values (e.g. query tables) or notify the user and remove the feature prior to displaying the workbook (e.g. Office Art shapes).

     

    So, you will be able open the workbook in Excel Services, but you will get the message:

     

    If you want to make changes to the Query Table, you will need to perform that in the Rich Client.

     

    Plan Office Web Apps (Installed on SharePoint 2010 Products)

    http://technet.microsoft.com/en-us/library/ff431682.aspx

     

    Scenarios that require the Office client applications

    Office Web Apps are online companions to Word, Excel, PowerPoint and OneNote. Following is a list of scenarios that cannot be performed in Office Web Apps alone. When you want to use features described in these scenarios, you can use the Office Web Apps command to open the document in the Office client application (Open in Word, Open in Excel, Open in PowerPoint, or Open in OneNote). When you save the document, it is updated in the SharePoint library where it was opened.

     

    Note that most items that cannot be created in Office Web Apps can be displayed in Office Web Apps. For example, comments and tracked changes are visible in Word Web application, but they must be created or edited in the Word client application.

     

    Editing documents

    Protecting documents by using Information Rights Management (IRM).

    IRM is not available in Office Web Apps. If you must protect confidential or sensitive information, use the full Office client applications.

    Copying formatting inside a document.

    The format painter is not available in Office Web Apps. Reapply formatting manually, or use the Office client applications.

    Inserting complex shapes or symbols in documents (text boxes and shapes, equations, dates, and times).

    Complex shapes and symbols are not available in the editing interfaces in Office Web Apps. If you have to insert these shapes or symbols, use the Office client applications. Note that you can view these shapes and symbols in Office Web Apps.

    Looking up synonyms by using a thesaurus or translating words.

    Thesaurus and translation features are not available in Office Web Apps. If you want to use these features, use the Office client applications.

    Drawing images by using Ink in a OneNote notebook.

    Inking is not available in OneNote Web application. Use the Office client version of OneNote if you want to use Ink. Note that you can still use OneNote Web application to edit other types of content in the notebook.

    Embedding media or other files into a OneNote notebook.

    If you are using OneNote notebooks as repositories for media or other embedded files, you must use the OneNote client application to access the embedded files. Note that you can still use OneNote Web application to edit other types of content in the notebook.

    Editing documents and using track changes to mark revisions.

    Tracking changes in a document is not available in Word Web application. Use the Word client application if you want to track specific revisions to a document. Note that you can view tracked changes in Word Web application.

    Real time co-authoring scenarios are different for Office Web Apps:

    For Word and PowerPoint documents, use the Office 2010 client applications for co-authoring, and be sure that no one is editing the document at the same time in Word Web application or PowerPoint Web application.

    For Excel workbooks, use the Excel Web application for co-authoring and be sure that no one is editing the document at the same time in the Excel client application.

    For OneNote notebooks, you can use either OneNote 2010 or OneNote Web application for co-authoring.

    Editing Word objects, such as SmartArt or document headers.

    Some objects, such as SmartArt and document headers and footers, are not available for editing in Office Web Apps. Use the Office client applications to edit these objects. Note that you can view these objects in Office Web Apps.

    Using macros in Word, Excel and PowerPoint documents.

    Macros are not enabled in Office Web Apps. Use the Office client applications if you use macros in Word, Excel and PowerPoint documents.

    Updating external tables or query tables in Excel.

    You cannot refresh or update data in external tables and queries from Office Web Apps. Use the Office client applications or Excel Services in SharePoint to update the data.

    Checking the spelling in Excel and PowerPoint documents.

    The spelling checker is available only for Word documents or OneNote notebooks in Office Web Apps. Use the Office client applications to check the spelling in other document types.

     


    Wednesday, November 9, 2011 2:27 PM