none
Excel PivotTables cannot be edited with Excel 2013 in Project BI Center RRS feed

  • Question

  • Hi,

    when I create a new Excel OLAP Report, save it with Excel 2013 to the BI Center and try to open it with Excel Services 2010 I receive the following message:

    The workbook that you selected cannot be opened.
    The workbook may be in an unsupported file format, or it may be corrupt.
    Would you like to try and open this file in Excel?

    If I then try to open it with Excel 2013 I get the following messages:

    We found a problem with some content in 'MyWorkbook.xlsx'
    Do you want us to try to recover as much as we can?
    If you trust the source of this workbook, click Yes.

    and subsequently:

    Removed Part: /xl/pivotCache/pivotCacheDefinition1.xml part with XML error.  (PivotTable cache) Load error. Line 2, column 0.
    Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)

    Is there any way to get this working?

    Thanks,

    Peter

    Tuesday, November 13, 2012 10:28 AM

Answers

All replies

  • You cannot open Excel Spreadsheets created in 2013 with Excel 2010 until Microsoft releases the forward compatibility add-on for Excel 2010. As you have learned, doing so can corrupt the spreadsheet.

    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG

    Wednesday, November 14, 2012 2:51 PM
    Moderator
  • Hi Peter,

    Have you seen Paul's post? This might be due to a simple issue, since you are using an OLAP report.

    http://pwmather.wordpress.com/2012/11/23/projectserver-2010-excel-services-reports-fail-to-refresh-office2013-sp2010/

    Treb Gatte | @tgatte | http://AboutMSProject.com

    Friday, November 23, 2012 5:37 PM
    Moderator
  • Hello everyone,

    this is an issue that I am experiencing as well. However, it even occurs when only using Excel 2013. Treb, your post will be helpful soon, but I'm not getting error about trusts but file corruption.

    In particular, I cannot open the file I saved:

    1. Launch Excel 2013
    2. Connect to an OLAP cube, create a pivot table. (It only occurs with OLAP based reports.)
    3. Play with the table to see that it works. (Yes, it does).
    4. Save and exit.
    5. Reopen the file in Excel 2013.
    6. File corruption, exactly like in Peter's case.

    Note: Unlike many other OLAP Excel issues, this cannot be fixed by simply switching the client locale to English (United States).

    Can anyone reproduce these steps? Create a local file with a pivot table based on an OLAP Cube, save the file and then try to reopen it?

    Note: This all works perfectly fine when using Excel 2010.

    Kind regards,
    Adrian


    Friday, February 1, 2013 5:33 PM
  • Update: Apparently, it gets even stranger. It works fine if you use an OLAP pivot table with the Task List dimension, howerver using either of the Project List or Resource List dimension will cause the aforementioned corruption. Note that you can use the Task List Dimension as a makeshift project list, but there is no real replacement for the Resource List.

    Source and verified by myself:
    http://social.technet.microsoft.com/Forums/en-US/projectonline/thread/9a6a76fd-7d93-4283-9838-b874e69a4a80/
    http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/86e7e690-316e-43b4-96c5-48bfbfa52598/#86e7e690-316e-43b4-96c5-48bfbfa52598

    Kind regards,
    Adrian

    Friday, February 1, 2013 6:31 PM
  • Hello everyone,

    this is an issue that I am experiencing as well. However, it even occurs when only using Excel 2013. Treb, your post will be helpful soon, but I'm not getting error about trusts but file corruption.

    In particular, I cannot open the file I saved:

    1. Launch Excel 2013
    2. Connect to an OLAP cube, create a pivot table. (It only occurs with OLAP based reports.)
    3. Play with the table to see that it works. (Yes, it does).
    4. Save and exit.
    5. Reopen the file in Excel 2013.
    6. File corruption, exactly like in Peter's case.

    Note: Unlike many other OLAP Excel issues, this cannot be fixed by simply switching the client locale to English (United States).

    Can anyone reproduce these steps? Create a local file with a pivot table based on an OLAP Cube, save the file and then try to reopen it?

    Note: This all works perfectly fine when using Excel 2010.

    Kind regards,
    Adrian



    I have the same problem :(
    Sunday, August 25, 2013 7:22 AM
  • "We have found that this will occur if Excel tries to read a 0 value in where it expects a date. Microsoft is currently investigating the issue further. In the meantime, you can work around this issue as follows:

    1. Right Click on the PivotTable
      Click PivotTableOptions
    2. On the Display tab, clear the checkbox labeled
      “Show Properties in ToolTips.”
    3. Save the file with the new setting intact. Note:
      If you fail to use the workaround before saving and closing the file, we cannot
      recover the PivotTable."

    Or you can find complete answer here http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2013/03/22/problem-reopening-excel-2013-pivottable-workbooks.aspx

    Sunday, August 25, 2013 7:33 AM