none
Is it possible to save data to a non-project DB and later link that for reporting? RRS feed

  • Question

  • I'm looking to save custom information in another DB (we are also using custom fields but are looking to minimize them) associated with a task and then later join that table to the Project tables for reporting purposes.

    We are going to send the data from MS Project Pro via a REST API (over HTTP) and our environment is Project Server.  The reason to store more data is that we'd like to capture rationale for changing due date, or removing a task, after the task is locked (also a custom field in our system).  And some other possible long text fields that we don't want stored as custom fields.  I guess we could always store them as custom fields as well.  I believe I have the VBA part figured out to send over HTTP.  More asking for the reporting piece.

    it seems like it should be possible but I'd like to make sure here, so is it possible?

    Thanks!

    Rich


    • Edited by Rich Franzmeier Tuesday, February 21, 2017 10:20 PM Clarification to question.
    Tuesday, February 21, 2017 9:54 PM

Answers

  • Hi Rich,

    Assuming you have the data from the Project client and your custom system into your custom database table then the reporting bit should be simple. You can write the SQL query to query the PWA Reporting database table and the custom table with a join, if the custom database table is on a different SQL server then you can create a linked SQL Server to get data from the other SQL server. You will need to ensure that you have the unique keys for each entity in the custom SQL database table such as ProjectGUID, TaskGUID, AssignmentGUID etc. so that you can join the data to the PWA Reporting tables / views. These GUIDs can be taken from the Project client via VBA and sent using your custom REST API.

    Hope that helps

    Paul 


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, February 22, 2017 6:36 AM
    Moderator

All replies

  • Rich,

    Can you expand on that a bit? Are you in a Project Server environment? What custom fields?

    It's not quite clear why you would want to save Project data in another format and then later bring it back into Project.

    John

    Tuesday, February 21, 2017 10:04 PM
  • I clarified the question for you.
    Tuesday, February 21, 2017 10:20 PM
  • Rich,

    Thanks for the additional detail. I don't do Project Server, REST or APIs so I can't really help you with this. Hopefully someone else will jump in with suggestions.

    John

    Tuesday, February 21, 2017 11:27 PM
  • Hi Rich,

    Assuming you have the data from the Project client and your custom system into your custom database table then the reporting bit should be simple. You can write the SQL query to query the PWA Reporting database table and the custom table with a join, if the custom database table is on a different SQL server then you can create a linked SQL Server to get data from the other SQL server. You will need to ensure that you have the unique keys for each entity in the custom SQL database table such as ProjectGUID, TaskGUID, AssignmentGUID etc. so that you can join the data to the PWA Reporting tables / views. These GUIDs can be taken from the Project client via VBA and sent using your custom REST API.

    Hope that helps

    Paul 


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, February 22, 2017 6:36 AM
    Moderator
  • Paul,

    Thanks for jumping in. I hate to leave a poster hanging if the question is beyond my expertise.

    John

    Wednesday, February 22, 2017 2:24 PM