Sign in
 
HomeOnline20132010Other VersionsLibraryForumsGallery

none
Project Server 2013 Olap Data Connection Problem

 , 
 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have created a number or Excel 2013 reports which use OlapPortfolioAnalyser data connection. Typically creating a pivot table. I can select any dimensions and measures and excel works as expected.  However if I include the Project list or Resource list it does not.  I have tried this in two separate instances but do not have a separate environmnent availabale but would appreciate confirmation or othrewise of the misbehaviour.

    Steps are: (Assuming cube is built and permissions set correctly and using Excel 2013 client)

    In PWA\Bi\Data connections navigate to cube folder

    Double click on OlapPortfolioAnalyser – opens in Excel 2013 (warning about data connections being blocked)

    Click Enable

    In pivot select project list from pivottable fields

    Save file (locally or to sharepoint)

    Close file

    Now re-open file

    Excel displays warning - "We found a problem with some content in "xxx.xslx". Do you want us to try and recover as much as we can? If you trust the source of the workbook, click Yes.

    Click Yes

    Another warning is displayed (Repairs to xxxx.xslx)

    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)

    Click Close - you now see data but pivottable functionality is gone.

    Note: this also happens with Resource list data.

    How do I resolve this?

    Thx

    Mike

    Mike Mahoney

    Tuesday, December 18, 2012 4:08 PM
    |

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Mike,

    this could be related to this documented issue http://support.microsoft.com/kb/2782523/en (although it's not a IRM protected file)

    Can you try to disable the protected view and add the document/document location in your trust center settings?

    Greetings,
    Markus


    Wednesday, December 19, 2012 7:36 AM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Markus

    Thanks for the suggestion unfortunately it has no effect on this problem. It is important to note that Excel 2013 works as expected with cube data so long as it does not include project list or resource list dimensions, which is pretty strange. It would be nice if someone can confirm this finding (or otherwise - in which case it will be a problem in my environment).

    rgds and Happy Xmas

    Mike

    Mike Mahoney

    Wednesday, December 19, 2012 9:43 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    I checked my PS 2013 environment by doing this exercise with Excel 2010 client without any problems, so it seems to be an Excel 2013 bug.

    rgds

    Mike

    Mike Mahoney

    Wednesday, December 19, 2012 5:42 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Mike, as the OLAP cube reporting feature will only be available for on-premise installations of Project Server 2013 you may wish to re-ask this in the http://social.technet.microsoft.com/Forums/gu-IN/projectserver2010general/threads forum.  These forums are not version specific, despite the names - but this one is just for the currently in preview Project Online.

    I do however repro the condition you are seeing and am researching if this is a known issue and if there are workarounds.

    Best regards,

    Brian.

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    • Marked as answer by Sleekstone Friday, January 04, 2013 11:03 AM
    Wednesday, January 02, 2013 11:16 PM
    |
    Owner
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Brian

    Thanks for the response. I will repost

    rgds

    Mike

    Mike Mahoney

    Friday, January 04, 2013 11:03 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Brian,

    have you been able to find the source of this issue or escalate to the proper technical teams?

    Please see http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/29dd170e-336d-4912-a534-90058dab6443/ for further details. This issue only occurs with Excel 2013 and only when using the Project List or Resource List dimensions. I.e. when using the Task List dimension, the file saves correctly and can be openend again.

    Kind regards,
    Adrian


    Hi Mike, as the OLAP cube reporting feature will only be available for on-premise installations of Project Server 2013 you may wish to re-ask this in the http://social.technet.microsoft.com/Forums/gu-IN/projectserver2010general/threads forum.  These forums are not version specific, despite the names - but this one is just for the currently in preview Project Online.

    I do however repro the condition you are seeing and am researching if this is a known issue and if there are workarounds.

    Best regards,

    Brian.

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page


    Sunday, February 24, 2013 6:14 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Adrian,

    The issue is now under investigation by the Excel team and hopefully I will have more news soon.  This appears to be a bug that can affect Analysis Services based PivotTables and not specifically Project Server ones - the Project and Resource list seem to trigger the issue - but the Task list does not.  I haven't been able to find any workaround that avoids the bug for the project ones though - If I do find something I'll post back. 

    Best regards,

    Brian.

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Monday, February 25, 2013 10:16 PM
    |
    Owner
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Brian,

    Do you have any update on this issue? We have recently upgraded to Excel 2013 and cannot refresh our OLAP reports. This is causing significant frustration. Any update or workaround would be appreciated,

    Many thanks,

    Suzanne.

    Wednesday, March 20, 2013 4:41 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Suzanne, still not aware of any workaround but reviewing the bug it does appear that it has been fixed and should be appearing in an future update.  As Excel isn't my specialty (I came across this as many users of Project Server use OLAP reporting) I want to ask the right people internally before committing to a specific update.  Thanks for your patience.

    Best regards,

    Brian.

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Wednesday, March 20, 2013 4:53 PM
    |
    Owner
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for the response Brian. Can you let us know if you hear of a confirmed date for the update?

    Kind Regards,

    Suzanne.

    Thursday, March 21, 2013 8:13 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    'Confirmed' is the tricky word Suzanne.  It currently looks like it is planned to be in the May Public Update due out May 14th.  Things can however change depending on test results etc. - if I hear more I will update this thread.

    Best regards,

    Brian.

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Thursday, March 21, 2013 7:22 PM
    |
    Owner
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    I'm experiencing the same issue with our Project Server 2013 OLAP Reporting through Excel 2013. I managed to find a "workaround" to keep existing excel pivot reports alive and working. I did that by saving the excel report in an older version, in my case excel 2003. You do lose some of the more advanced formatting which is available since Excel 2007 as well as any slicers you might have foreseen.Nevertheless the pivot functionality, conditional formatting and data refresh functionalities which my customer really requires, are more important than some advanced formatting.

    Looking forward to a real fix though!

    Hope this workaround also works for other people.

    Best Regards,
    Kjell

    Wednesday, April 17, 2013 10:56 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Brian, hi

    Definitely May Public Update due out May 14th?

    Monday, April 29, 2013 8:44 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Brian,

    apparently, there are do not seem to be any Excel updates in the May update for Office 2013: http://support.microsoft.com/kb/2847265
    Having installed all updates for Excel 2013 on my testing rig, the OLAP issue still occurs.

    The fix has been moved to a later update, I presume?

    Kind regards,
    Adrian

    Thursday, May 23, 2013 3:43 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Adrian, I just reviewed the bug and it is now scheduled for release on July 9th 2013, in the July Public Update.  I'm not on the Excel team, but on the Project team so not able to comment on the delay - but sorry this fix didn't get out on time. 

    Best regards,

    Brian.

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Thursday, May 23, 2013 4:01 PM
    |
    Owner
  • Question
    Sign in to vote
    0
    Sign in to vote

    I have the latest Microsoft Office updates installed on July 10th 2013, but the problem still exists. Does anyone know how to solve this problem?

    Best regards,

    Nenad.

    Saturday, July 13, 2013 9:12 AM
    |
  • Question
    Sign in to vote
    2
    Sign in to vote

    While still being broken (there was no explicit mention change for Excel 2013 in the July PU), a workaround has been published by the Excel team that works for me:

    Create your PivotTable as usual. Then Right Click on the PivotTable / Click PivotTableOptions / On the Display tab, clear the checkbox labeled “Show Properties in ToolTips.” Of course, this needs to be done before the PivotTable is corrupted.

    Source: http://social.technet.microsoft.com/Forums/projectserver/en-US/9a6a76fd-7d93-4283-9838-b874e69a4a80/project-server-2013-olap-data-connection-problem

    Kind regards,
    Adrian


    • Edited by adrian_rho Wednesday, July 17, 2013 7:21 PM Typo
    Wednesday, July 17, 2013 7:20 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for the update and tip Adrian.  I'll check the bug status too and see what happened to the fix when I return to the office next week.

    Best regards,

    Brian

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Sunday, July 21, 2013 6:31 PM
    |
    Owner
  • Question
    Sign in to vote
    0
    Sign in to vote

    This appears to be fixed with the 15.0.4517.1004 build Adrian - I am unable to repro now, even with the box checked to show properties in tooltips.  This patch level fits with the July Public Update.  Click to run users would get this automatically.

    Anyone else still seeing that issue with that build?

    Best regards,

    Brian.

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Tuesday, July 30, 2013 7:31 PM
    |
    Owner
  • Question
    Sign in to vote
    1
    Sign in to vote

    More research and KB 2817339 appears to have the fix - see http://blogs.technet.com/b/projectsupport/archive/2013/07/30/excel-2013-error-opening-saved-pivottable-reports-from-pwa-bi-center.aspx 

    As I mention in the blog - the fix will not recover a broken xlsx as the break appeared to be on save rather than open.

    Best regards,

    Brian

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Tuesday, July 30, 2013 8:41 PM
    |
    Owner
  • Question
    Sign in to vote
    0
    Sign in to vote

    More research and KB 2817339 appears to have the fix - see http://blogs.technet.com/b/projectsupport/archive/2013/07/30/excel-2013-error-opening-saved-pivottable-reports-from-pwa-bi-center.aspx 

    As I mention in the blog - the fix will not recover a broken xlsx as the break appeared to be on save rather than open.

    Best regards,

    Brian

    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Unfortunately the blog post you linked is in error - it reports that the issue is fixed in build 15.0.4517.1001. However I am running 15.0.4517.1504 and still hit the problem. The only workaround is the one post by adrian_rho, above, which solved my problem. Thanks Adrian for the workaround, and to Brian for the great job tracking and assisting people on this issue, even though it is Excel and not Project - much appreciated!

    Mike

    Tuesday, August 20, 2013 10:19 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    The issue is not fixed and is not related to project server, it is related to excel olap pivot.  The insidious aspect is that you might successfully save the document with no mention of any problems, but when you go to open it, it is corrupt.  The "recovery" usually turns all your pivot tables and pivot charts into regular charts and simple worksheets of data with no pivot connections, in other words it irretrievably destroys all your work, requiring you to completely recreate the spreadsheet.  I believe it occurs if there are too many pivot objects in a single document.  Sometimes this will cause excel to crash or create unstable behaviors, but other times it doesn't give any warning, but saving the doc at that point saves a corrupt doc.  It's a terrible inconvenience and unfortunately appears that Microsoft is not planning to correct it.

    Wednesday, July 16, 2014 3:00 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    The issue is not fixed and is not related to project server, it is related to excel olap pivot.  The insidious aspect is that you might successfully save the document with no mention of any problems, but when you go to open it, it is corrupt.  The "recovery" usually turns all your pivot tables and pivot charts into regular charts and simple worksheets of data with no pivot connections, in other words it irretrievably destroys all your work, requiring you to completely recreate the spreadsheet.  I believe it occurs if there are too many pivot objects in a single document.  Sometimes this will cause excel to crash or create unstable behaviors, but other times it doesn't give any warning, but saving the doc at that point saves a corrupt doc.  It's a terrible inconvenience and unfortunately appears that Microsoft is not planning to correct it.

    Hi Lee,

    last year's http://support.microsoft.com/kb/2817339/en-us for Excel fixed the issue. I've been working without OLAP in Excel ever since. Please retry or verify before claiming that it is not fixed yet.

    Best regards,
    Adrian

    Thursday, August 28, 2014 10:44 PM
    |