none
Project Online / ODATA / POWER PIVOT help needed RRS feed

  • Question

  • Hi

    I hope someone can help me

    I am trying to create a capacity plan for my team by using Odata and power pivots. My aim is to end up with something similar to the Resource usage report in MS project.

    So far, by using ResourceDemandTimephasedDataSet I have created something similar to the attached picture (ive had to recreate it due to confidentiality). With a link to the Resources table via "Resource name" so I can use and filter by Resource Department.

    This gives me the style and format I need but I need the task name to appear under project name. As there is no "task name" in the 2 data sets I have already used I have tried to link all the other tables with "task name" as an option but all it gives me is a list of every task from that project, rather than the ones which relate to the combination of project name and resource name.

    Any help would be greatly appreciated

    Thanks

    Matt



    • Edited by Matt22365 Friday, May 12, 2017 1:55 PM
    Friday, May 12, 2017 1:53 PM

Answers

  • Hello,

    Here are the endpoints you need. Also link via GUID's rather than strings such as names.

    • ResourceDemandTimephasedDataSet - ProjectId to ProjectId in Tasks
    • Tasks
    • Resources - ResourceId to ResourceId in ResourceDemandTimephasedDataSet
    • TimeSet - TimeByDay used to link this to TimeByDay ResourceDemandTimephasedDataSet 
    • Use the TimeByDay property on the report (filters and columns etc.)

    See how you get on with that.

    Paul


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

    • Marked as answer by Matt22365 Wednesday, January 3, 2018 8:20 AM
    Sunday, May 14, 2017 6:37 AM
    Moderator
  • Hi Matt,

    I haven't looked but I don't think you will get the task name in there and there is no unique key between the data sets. You might have to use the Assignments time phased feed directly rather than the resource demand feed if you want to see task name in your report.

    Paul


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

    • Marked as answer by Matt22365 Wednesday, January 3, 2018 8:21 AM
    Sunday, May 21, 2017 9:35 PM
    Moderator

All replies

  • Hi

    I hope someone can help me

    I am trying to create a capacity plan for my team by using Odata and power pivots. My aim is to end up with something similar to the Resource usage report in MS project.

    So far, by using ResourceDemandTimephasedDataSet I have created something similar to the attached picture (ive had to recreate it due to confidentiality). With a link to the Resources table via "Resource name" so I can use and filter by Resource Department.

    This gives me the style and format I need but I need the task name to appear under project name. As there is no "task name" in the 2 data sets I have already used I have tried to link all the other tables with "task name" as an option but all it gives me is a list of every task from that project, rather than the ones which relate to the combination of project name and resource name.

    Any help would be greatly appreciated

    Thanks

    Matt

    Friday, May 12, 2017 1:03 PM
  • Hello,

    Here are the endpoints you need. Also link via GUID's rather than strings such as names.

    • ResourceDemandTimephasedDataSet - ProjectId to ProjectId in Tasks
    • Tasks
    • Resources - ResourceId to ResourceId in ResourceDemandTimephasedDataSet
    • TimeSet - TimeByDay used to link this to TimeByDay ResourceDemandTimephasedDataSet 
    • Use the TimeByDay property on the report (filters and columns etc.)

    See how you get on with that.

    Paul


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

    • Marked as answer by Matt22365 Wednesday, January 3, 2018 8:20 AM
    Sunday, May 14, 2017 6:37 AM
    Moderator
  • Hi Paul

    Thank you for your reply. Unfortunately I cannot link ResourceDemandTimephasedDataSet Project ID to Tasks Project ID as I get the error message:

    "The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values"

    Any ideas?

    These are the links I have to give the report from my previous post

    Thanks

    Matt


    • Edited by Matt22365 Monday, May 15, 2017 9:17 AM
    Monday, May 15, 2017 7:44 AM
  • Hi Matt,

    I haven't looked but I don't think you will get the task name in there and there is no unique key between the data sets. You might have to use the Assignments time phased feed directly rather than the resource demand feed if you want to see task name in your report.

    Paul


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

    • Marked as answer by Matt22365 Wednesday, January 3, 2018 8:21 AM
    Sunday, May 21, 2017 9:35 PM
    Moderator
  • Hi Paul

    Unfortunately I cannot break the link with resource demand as this is the key piece of data for the report unless there is another option in assignments which shows the same information as resource demand, do you know of one?

    Thanks

    Matt   

    Tuesday, May 23, 2017 10:49 AM