none
Project Online Excel ODATA report failing to retrieve 'Assignment Timephased' data RRS feed

  • Question

  • Hi, 

    We recently migrated to Project Online (2016) from Project 2010.

    Therefore, We recently started creating ODATA excel reports.

    I have two questions:

    1. We have huge data on our Project Server, which makes it impossible to get data from 'AssignmentTimephasedDataSet' table. After giving connection, excel keeps on retrieving data for around an hour and fails in the end.

    Q:  Is there any better way to get this data and use it in reports?

    Here is the connection we use:
    https://<SiteName*>/sites/PWADev/_api/projectdata/AssignmentTimephasedDataSet?$select=ProjectId, AssignmentId, TaskId, TaskName, TimeByDay, AssignmentCost, ResourceId&$TimeByDay gt datetime'2016-12-31'

    2. We need to combine 3 tables (Projects, AssignmentTimephasedDataSet and Resources) to get few specific columns to use.

    Q:  How to combine (like we 'join' in SQL) multiple tables and get few specific columns to be used in report?

    For this report we need = ProjectName, TaskName, AssignmentCost, TimeByDay, ResourceDepartment.

    Any suggestion will be very Helpful!

    Thankyou,

    Akash

    Wednesday, December 13, 2017 1:47 PM

Answers

  • Hi, the other way you could do this is by further restricting the filtering so you get the data for 2016 in one query, and then use a 2nd query to get 2017 data.  Append the datasets using PowerQuery.   I guess it will take the same amount of time, but it hopefully won't hang...

    In answer to 3) specifically, you can create the relationships in Excel when you use the Manage Relationships button in the PowerPivot section in Excel - you can see how this works in the following download from TechNet which displays Timesheet data in Excel - https://gallery.technet.microsoft.com/Online-Timesheet-Excel-3b165651 


    Ben Howard [MVP] | web | blog | book | downloads | P2O

    • Marked as answer by Trekker7619 Wednesday, January 10, 2018 5:48 PM
    Thursday, December 14, 2017 7:50 AM

All replies

  • Hello Akash, 1. To reduce the amount of data which is given back from timephased data, there was recently rolled out a new feature where you can change the granularity of the results (e.g. weeks, months). In Pauls blog, you will find more about it: https://pwmather.wordpress.com/2017/11/17/projectonline-time-phased-data-rollup-for-odata-reporting-ppm-pmot-bi-excel-powerbi/ You should also explicity use the filter statement in the query. 2. Connect the tables via a unique identifier like with SQL (e.g. ProjectId, TaskId) by using PowerPivot or PowerBI.


    Best regards,

    Julian, PMP, MCP, MCSA, MCSE

    Blog: https://project-brain.com

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.


    Thursday, December 14, 2017 7:06 AM
  • Hi, the other way you could do this is by further restricting the filtering so you get the data for 2016 in one query, and then use a 2nd query to get 2017 data.  Append the datasets using PowerQuery.   I guess it will take the same amount of time, but it hopefully won't hang...

    In answer to 3) specifically, you can create the relationships in Excel when you use the Manage Relationships button in the PowerPivot section in Excel - you can see how this works in the following download from TechNet which displays Timesheet data in Excel - https://gallery.technet.microsoft.com/Online-Timesheet-Excel-3b165651 


    Ben Howard [MVP] | web | blog | book | downloads | P2O

    • Marked as answer by Trekker7619 Wednesday, January 10, 2018 5:48 PM
    Thursday, December 14, 2017 7:50 AM
  • Hello,

    It might be worth downloading this example report pack and see the filters and relationships here as examples / a solution starter:

    https://pwmather.wordpress.com/2017/10/31/projectonline-ppm-powerbi-report-pack-v2-bi-reporting-powerquery-dax-office365/

    That is using Power BI but you can get the data the same way in Excel using Power Query.

    Paul


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

    Friday, December 15, 2017 9:05 AM
    Moderator