none
Assignments report RRS feed

  • Question

  • I have spent literally days on this problem.. all I need is the correct syntax for a data connection into Excel to allow me to specify only certain columns to be downloaded, can anyone put me out of my misery on this?

    this is where I have got to -

    Data Source=https://xxx.sharepoint.com/sites/pwa/_api/projectdata/[en-us]/AssignmentTimephasedDataSet;Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=SSPI;Keep Alive=true;Persist Security Info=false;Service Document Url=https://xxx.sharepoint.com/sites/pwa/_api/projectdata/[en-us]/AssignmentTimephasedDataSet?
    $select=ProjectId

    Thanks

    Dan

    Wednesday, May 10, 2017 11:31 AM

Answers

All replies

  • Here's one taken directly from Excel...

    Data Source=https://Applepark.sharepoint.com/sites/pwa/_APi/ProjectData/[en-US]/Projects()?$filter=ProjectType ne 7&$Select=ProjectId,ProjectName,ProjectOwnerName,ProjectWork,ProjectCost,ProjectStartDate,ProjectFinishDate,ProjectPercentCompleted,ProjectCostVariance,ProjectWorkVariance,LeadOrganisation,EnterpriseProjectTypeName,ProjectRAG,ProjectStatus,ProjectSchedule,ProjectRegion;Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=SSPI;Keep Alive=true;Persist Security Info=false;Base Url=https://Applepark.sharepoint.com/sites/pwa/_APi/ProjectData/[en-US]/Projects()?$filter=ProjectType ne 7&$Select=ProjectId,ProjectName,ProjectOwnerName,ProjectWork,ProjectCost,ProjectStartDate,ProjectFinishDate,ProjectPercentCompleted,ProjectCostVariance,ProjectWorkVariance,LeadOrganisation,EnterpriseProjectTypeName,ProjectRAG,ProjectStatus,ProjectSchedule,ProjectRegion


    Ben Howard [MVP] | web | blog | book

    Wednesday, May 10, 2017 1:17 PM
  • thanks Ben,  I have got your connection to work ok on our instance.  I then converted it to the eblow to pull out timephased assignment data but it keeps saying the service document url is invalid.  Is the eg table name AssignmentTimephasedDataSet wrong?

    Dan

    Data Source=https://xxx.sharepoint.com/sites/pwa/_api/projectdata/[en-Us]/AssignmentTimephasedDataSet()?;$Select=AssignmentId;Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=SSPI;Keep Alive=true;Persist Security Info=false;Service Document Url=https://xxx.sharepoint.com/sites/pwa/_api/projectdata/[en-uS]/AssignmentTimephasedDataSet()?;$Select=AssignmentId

    Wednesday, May 10, 2017 2:03 PM
  • Hi,

    The value AssignmentTimephasedDataSet should be correct.  What happens if you enter the following URL into the browser (change xxx) - you should see the feed...  You can also try this in Excel using the oData feed there... 

    http://xxx.sharepoint.com/sites/pwa/_api/ProjectData/AssignmentTimephasedDataSet()


    Ben Howard [MVP] | web | blog | book

    Wednesday, May 10, 2017 2:49 PM
  • Hello,

    It will be the semi colon after the ? here ...TimephasedDataSet()?;$Select. Try removing the ; and see if that works.

    Paul


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

    Wednesday, May 10, 2017 7:52 PM
    Moderator
  • Hi Ben, when I enter the URL into the browser it reaches the data fine.

    Dan

    Thursday, May 11, 2017 10:38 AM
  • Hi Paul, I have removed the semi-colon but still I cannot get the connection to work.   My connection string is now as below.

    Data Source=https://xxx.sharepoint.com/sites/pwa/_api/projectdata/[en-Us]/AssignmentTimephasedDataSet()?$Select=AssignmentId;Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=SSPI;Keep Alive=true;Persist Security Info=false;Service Document Url=https://xxx.sharepoint.com/sites/pwa/_api/projectdata/[en-uS]/AssignmentTimephasedDataSet()?$Select=AssignmentId

    If I select the standard assignmentstimephased data table it works ok but what i am trying to with the above is to be able to select minimal columns and then to filter them.

    Dan

    Thursday, May 11, 2017 10:47 AM
  • Hi Dan,

    I haven't used a data connection file for a while...  If this is Project Online I suggest you use the Query Editor to shape the data that you bring into Excel, it's actually much easier IMHO than editing ODC files...


    Ben Howard [MVP] | web | blog | book

    Thursday, May 11, 2017 11:29 AM
  • thanks Ben, I really like query editor as it is so easy to use. my problem then lies in the fact that after I have built my query and saved it within the Excel workbook I load it upto the BI center area in Project Online and the page won't refresh. I am wondering if queries within workbooks are unable to refresh once online??

    Dan

    Thursday, May 11, 2017 12:01 PM
  • Okay, well let's concentrate on one issue at a time.  Can you get the data connection working in Excel in terms of shaping the data via Power Query?

    Ben Howard [MVP] | web | blog | book

    Thursday, May 11, 2017 12:19 PM
  • yes Ben, I can get what I need in PowerQuery..

    Dan

    Thursday, May 11, 2017 1:32 PM
  • finally managed to get it to work :-)

    Dan

    • Marked as answer by Dan Kient Wednesday, May 17, 2017 7:41 AM
    Wednesday, May 17, 2017 7:40 AM
  • Hi Dan,

    So that others can benefit from the combined knowledge of the forum, would you please post your solution?


    Ben Howard [MVP] | web | blog | book

    Wednesday, May 17, 2017 9:06 AM