none
Using Pivot Table to sum from Data files RRS feed

  • Question

  • I've successfully created a portfolio report using Excel Pivot tables, where the VALUES is the Sum of 'ResourceDemand' and is display in monthly columns 'FiscalPerodFinish' Date.  Filter is 'FiscalPeriodYear.'

    I now want to create a report which display the cost in monthly columns, where it is separated by Rows contains 'ProjectName' and Resource 'TypeName'.   I've been trying the Value: Sum of 'AssignmentCost'. My problem is that each column is displaying the full total for each row for the project (I can't display the screenshot until my account is verified).

    I've tried a number of table relationships (possibly keeping too many tables, but this has been out of desperation):

    Assignments (ProjectID) – Projects (ProjectID)
    Assignments (ResourceID) – Resources (ResourceID)
    AssignmentsTimephasedDataSet (AssignmentID) – Assignments (AssignmentID)
    AssignmentsTimephasedDataSet (FiscalPeriodID) – FiscalPeriods (FiscalPeriodID)
    ResourceDemandTimephasedDataSet (FiscalPeriodID) – FiscalPeriods (FiscalPeriodID)
    ResourceDemandTimephasedDataSet (ProjectID) – Projects (ProjectID)
    ResourceDemandTimephasedDataSet (ResourceID) – Resources (ResourceID)
    ResourceTimephasedDataSet (ResourceID) – Resources (ResourceID)

    The Pivot Table is telling me 'Relationships between tables may be needed'!

    Can anyone suggest what I need to do to get fiscal monthly totals in each column, please?

    Thanks.

    Ian

    Thursday, March 29, 2018 4:55 PM

Answers

  • Thanks for the suggestion, Paul.  I ran with it and then discovered that it I removed the fiscal period from the columns and put it into the filter, I could get a one month at a time figure, which meets the requirements I had of getting a monthly costs figure out.

    I'm grateful to you both, Paul and Matt, for your combined help which helped me get there in the end.

    Regards

    Ian

    • Marked as answer by Ian X Thursday, April 26, 2018 8:38 PM
    Thursday, April 26, 2018 8:38 PM

All replies

  • Here is the screenshot of the Pivot Table

    Friday, March 30, 2018 8:00 PM
  • Have you created a relationship between the data tables, i.e. Linking the fiscal periods table to assignment cost on Timebyday? Without a relationship the pivot table can't join the data together correctly. Regards Matt
    Thursday, April 5, 2018 7:29 PM
  • Hi Matt,

    I've tried AssignmentsTimephasedDataSet (FiscalPeriodID) – FiscalPeriods (FiscalPeriodID) (see my table of relationships above) but without success.

    Ian

    Friday, April 6, 2018 7:03 AM
  • Hello,

    You seem to have some tables that might not be required, can you not create this report with 4 tables:

    • AssignmentTimephasedDataSet
    • FiscalPeriods
    • Projects
    • Resources

    With the following relationships:

    • AssignmentTimephased (FiscalPeriodId) to FiscalPeriods (FiscalPeriodId)
    • AssignmentTimephased (ProjectId) to Projects (ProjectId)
    • AssignmentTimephased (ResourceId) to Resources (ResourceId)

    Paul


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

    Friday, April 6, 2018 8:29 AM
    Moderator
  • Hi Paul,

    Thanks for your response.  I've tried your suggestion, but still find that all the costs are amalgamated into one 'blank' column, not sub-divided into one column for each  Fiscal Periods.  The Value below is Sum of AssignmentCost

    Ian

    Friday, April 6, 2018 6:41 PM
  • Hi Ian,

    To achieve what you need we need to be a bit clever with the relationships in the data model.  Here is the result I think you're attempting to get:


    To get to this you need to introduce a common reference point for all of the date time series.  In Project Online there is a table called Timeset:  /sites/pwa/_api/projectdata/TimeSet()

    This returns a huge range of dates from the system so you may like to put some filters in the queries to just get relevant dates.

    Once you have this table, relate TimebyDay in the TimePhasedAssignments to the Timeset and also the FiscalPeriodStartDate field in the FiscalPeriods table  to TimebyDay in the TimeSet.  This joins the data together.  Your data model should look like this:

    

    Pivot table settings:

    Hopefully this gets you to where you need to be.

    Regards

    Matt



    Friday, April 13, 2018 3:34 PM
  • Hi Matt,

    I appreciate the time you put into working this out.  The challenge I'm facing is not to sum Assigment Work, but to sum Assignment Cost.  Your joins diagram was helpful, and I did this:

    The result, however, gave me a total for the whole row in every column:

    Any idea where I'm going wrong, please?

    Regards

    Ian

    Wednesday, April 25, 2018 9:44 PM
  • Hi Ian,

    You might have found the pivot table limitation. Try as test adding a numeric value from the other tables into the values area and see if the assignment cost is then correct.

    Paul


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

    Thursday, April 26, 2018 7:00 AM
    Moderator
  • Thanks for the suggestion, Paul.  I ran with it and then discovered that it I removed the fiscal period from the columns and put it into the filter, I could get a one month at a time figure, which meets the requirements I had of getting a monthly costs figure out.

    I'm grateful to you both, Paul and Matt, for your combined help which helped me get there in the end.

    Regards

    Ian

    • Marked as answer by Ian X Thursday, April 26, 2018 8:38 PM
    Thursday, April 26, 2018 8:38 PM