How to mashup data from workspaces and reporting db on Project Online RRS feed

  • Question

  • I'd like to build a "report" that shows data from both SharePoint lists on the project server workspaces as well as the reporting database. Specifically, I'd like the workspace piece to be dynamic, meaning, I don't want the "report" to have to be modified each time a project is added to the server.

    I've put "report" in quotes, as I don't have a preference for the technology used. Excel Services, PowerPivot, SSRS, it doesn't matter if it's able to accomplish the above.

    To complicate this, I'm trying to do this with Project Online.

    Any suggestions?

    Mike G.

    Thursday, June 13, 2013 1:13 PM

All replies

  • Hi Mike,

    If you are using Project Online, all the possibilities you mentioned, end at the same place: OData.

    Here you will find end-points to access to the Issues, Risks and Deliverables of a Project (don´t if you are only limited for these lists or you want some that are not those).

    If you are looking for more lists, not created OOB, I suggest you to have a look in the OData end-points of SharePoint. Here there is an example of what you can query (it´s Javascript, but doesn´t matter, you could get the idea):

    Hope that helps!

    Thursday, June 13, 2013 3:47 PM
  • Thanks for the suggestions! The blocking issue I have for OData is making the data sources dynamic. Meaning, if I have 3 projects, through PowerPivot I can connect to lists on the the 3 project workspaces via OData, join that to data from the database, and it all works great. But when a project is added to the system, how do I automatically get that added to the PowerPivot report?

    With SSRS, I could easily have a dropdown parameter that allows the user to select a project, and then have a dynamic data source that retrieves data from the appropriate sharepoint site. But SSRS isn't supported for SharePoint online.

    I could host SSRS on-prem and connect to the sharepoint lists, but then how do I get access to the Project Server data?

    Mike G.

    Thursday, June 13, 2013 3:58 PM
  • Mike,

    I don´t know if I get your last point, but do you mean you have problems to connect OData in a On-prem? In a On-prem you have two end-point of OData for Project Server: /_api/ProjectData and /_api/ProjectServer.

    Anyway, if I´m wrong, correct me :o)



    Thursday, June 13, 2013 9:09 PM
  • Thanks for the odata tip on Project server, I initially was thinking that was all I needed. But.

    1. I can't connect a local instance of SSRS to Project Online

    2. Project Online doesn't support SSRS(?)

    So, SSRS seems to be out. Also:

    3. Excel doesn't support paramaterized OData data connections (I can't specify a connection to: http:/, and pass in a value to by used in place of A1 at runtime).  (Well, it does support parameterized web queries, but the web query tool in Excel doesn't support the Project Online authentication)

    So, I think I'm back to the beginning. How do you enable the user to select a project from a list, and see data from both the project database as well as the project workspace?

    Mike G.

    Friday, June 14, 2013 1:26 PM