Excel Web Access in Project Detail Page RRS feed

  • Question

  • In Project Server 2010, I would like to use the Excel Web Access web part on a Project Detail Page to display a spreadsheet containing supplementary information about the project.  The project site for each project contains an .xlsx file of a fixed name within its Project Documents library.

    I would like to be able to go to Project Center, choose one project, and open this Project Detail Page.  I would then like to see the copy of the spreadsheet that has data in it for this project.  To accomplish this I beleive I would need to pass a URL parameter to the Excel Web Access web part -- or perhaps use a relative URL -- so the Excel Web Access URL adjusts based on what project this pertains to.

    I have done a small amount of experimenting with web part connections, but I'd rather not make the user select the name of the project from a filtering web part just to specify which project's spreadsheet to display.

    Any suggestions for ways to accomplish this, either using this general framework or some different approach?  My high level requirement is to store and present information about each project that does not fit neatly into Enterprise Custom Fields.


    Lee Vande Voort

    Saturday, August 24, 2013 3:11 PM

All replies

  • Take a look at some of my blog posts on the topic, but essentially:

    1) Create a SQL query to pull the data into Excel as an Office Data Connection.  As part of that connection, you need to convert the ProjectUID field into something that Excel can render, so your SQL query should look something like CAST(P.ProjectUID AS VARCHAR(36)) AS ProjectUID.

    2) In your Excel chart, ensure that it can filter based on the ProjectUID field.

    3) When pushing the Excel chart to SharePoint, ensure you promote the cell containing the ProjectUID field.

    4) Then, when you make the PDP, add the Query String Filter WebPart.  Configure it to pull the ProjUID parameter from the URL.

    5) Add your Excel Webpart, and connect the Excel WebPart to the Query String Filter Webpart so that it passed the ProjectUID into the Excel document.

    Sounds like a lot, but with a bit of practice, it's pretty quick and easy.  Here's an example:

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky

    Saturday, August 24, 2013 6:44 PM