none
cost of activity regarding a specific time period (start/finish) RRS feed

  • Question

  • Hi

    I need to export into an excel a field in which there's the cost (of each task) for 2015 and for 2016 years.

    For example. I insert a row "TaskA" : start at jan-2015 and finish at oct-2016. I need to know how much cost in 2015 and how much in 2016.

    Inside MS Project I can see (and paste into excel) that information in timeline table. But what about have this information in custom cost fields?

    thank you.

    regards

    daniele


    Daniele.b75

    Tuesday, October 14, 2014 2:32 PM

All replies

  • Danielb75,

    Sorry but custom fields do not have capability to show timescaled data. If you need the data in a custom cost field by year, you will need two fields, one for 2015 and one for 2016. Populating those two fields will depend on how you calculate the cost for the custom field.

    John

    Tuesday, October 14, 2014 2:50 PM
  • Hi Daniele,

    Unfortunately there is no easy way to do that. You'll have to write some code to populate the custom field "cost 2015", "cost 2016", ... .

    Another way would be to use the OLAP BD.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Tuesday, October 14, 2014 3:06 PM
    Moderator
  • Hi, John, 

    thank for quick response.

    1) Sorry but I don understand what you mean for 

    "Populating those two fields will depend on how you calculate the cost for the custom field."

    Do you mean that I need to insert manually the cost for 2015 and 2016?

    In that way I'll have two fields (2015 and 2016) absolutely un-linked...each time that I change a task I have to insert MANUALLY the values inside 2015 and 2016 fields?

    2) Also using formulas is not possibile have a time-section of costs? 

    regards

    db


    Daniele.b75

    Tuesday, October 14, 2014 3:07 PM
  • Hi Daniele,

    Unfortunately there is no easy way to do that. You'll have to write some code to populate the custom field "cost 2015", "cost 2016", ... .

    Another way would be to use the OLAP BD.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    can you give me some links about write some code (which native function I have to use?) and/or how I could use OLAP BD to have that info?

    thank you a lot in advance

    regards

    db


    Daniele.b75

    Tuesday, October 14, 2014 3:12 PM
  • Here is how to configure OLAP cubes in PS2013. OLAP cubes allow you to report data along time periods.

    For the code to calculate timephased cost in custom fields, since I'm not a developer, I'll let other folks help you on this.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, MCP |

    Tuesday, October 14, 2014 3:36 PM
    Moderator
  • Danieleb75,

    Let's back up just a moment. From your original post it sounds like you are using custom cost fields. For example, maybe Cost1, Cost2, etc. Assuming that is the case, how are you entering the cost information into that/those fields? Is it with a formula? If so, what is the formula?

    The value in a custom field will be singular, that is, only one value regardless of the time period. If you want to spread that value over a time period, (e.g. weeks, months, years, etc.), it will require that you either manually break it into those periods (e.g. 2015 and 2016) and enter each value in two separate custom cost fields, or you will need some VBA to spread the value over a time period, for example if want to export that data to Excel.

    To answer you second question, it may be possible to break the cost down by year with two separate formulas in two separate custom cost fields, but in order for us to help you with that, we need to know more about how you calculate that cost.

    John

    Tuesday, October 14, 2014 4:05 PM
  • I was TRYING to use custom fields to export it to an excel.

    Le'ts try me with an example.

    I had simply planned a task from 01-jan-2015 to 31-dec-2016

    I assign a resource R1 with cost 100€ / day  (suppose there're 400 days work in 2 years)

    The TOTAL cost is calculated = 400 x 100 = 40.000€

    Well.

    Now :

    * in automatic COST field there 40.000€ (total cost)

    * into the timeline I can see that 20.000€ are in 2015 and 20.000€ are in 2016.


    Obviously the task can change ... change on efforts or add/remove resource...  so real 2015 / 2016 costs could change it.

     

    But how can I have two fields 2015 and 2016 updated? So when I need to export data to excel I'll sure that the fields 2015 and 2016 are updated with new schedule...

    thank you


    Daniele.b75

    Tuesday, October 14, 2014 4:16 PM
  • danieleb75,

    Okay, I'm a little confused. Now it doesn't sound like you are using a custom cost field, rather, it sounds like you are simply using the normal Cost field. I assume by "timeline view" you mean the Resource or Task Usage view. Using either of those views you can set the timescale to be years such that the cost is shown by years. Given all this is how you have it set up, then it should be a simple matter of using the visual reports feature to export the timescaled data to Excel, probably using the Resource Cost Summary Report.

    Why won't that work?

    John

    Tuesday, October 14, 2014 4:45 PM
  • Yes I'm using normal cost fields.

    Yes for Timeline I mean Task Usage.

    Ok I can use Visual Report and I'm able to see data spanned over time (year, months...). So ...generally I have the solution to my first question. But now...

    It's possibile to extract also other custom fields inside report?

    I had labeled the tasks using a custom field (specific of my organization).

    Let's suppose for example "PHASE_A" and "PHASE_B", "PHASE_C".... 

    I need the spread the cost over year... but grouped for PHASE_A, B and C.

    thank you.


    Daniele.b75

    Wednesday, October 15, 2014 7:56 AM
  • Danieleb75,

    In order to get your custom field included in the visual report export, you will need to edit the template (Cash Flow Report) by adding your custom field (e.g. Text1). Once you have the pivot table in Excel, you will need to show that by right clicking on the Project row in the pivot table. That will bring up a drop down menu and allow you to Show Properties/Show all Properties in the report. From there you can sort on the custom field.

    Unfortunately my expertise with pivot tables is very limited, (I'm a VBA guy), so if you need more help setting up the data in Excel, someone else will need to jump in an guide you.

    Hope this helps.

    John

    Wednesday, October 15, 2014 8:18 PM