none
Visual Report doubling data from projects RRS feed

  • Question

  • Just wondering if I've set this up wrong or if it's a limitation of Project.

    Using Project 2013, I've created a visual report template to extract work & cost per resource on a monthly basis, plus a few other categories to group the data by.

    Some of my projects build up to the same Financial ID so I want to open multiple projects at once and run the report to get the aggregated view - when I do this all the effort and cost doubles.

    So total effort by me for May is 168h (across ALL projects), and when I run my report my effort is 336h.

    Wednesday, May 6, 2015 9:00 PM

Answers

  • Jessica,

    Where do you see the option to "load resource summary data" when you open a project? Is that part of the Project Server environment?

    It sounds like you are doing the right thing by not saving any of the files after you create the master. My concern was that if you saved the master, you would be creating a linked structure and that is prone to corruption. Just for reference you might be better off to uncheck the option to "link to project" option in the lower right corner of the Insert Project window. That will create a static master and combine all the resources into a single independent file. Run your report and then close without saving.

    Since you are using a resource pool, you do have a linked structure and therefore the possibility of corruption is present. Try the static master approach and see if that works. If it does not, you may need to re-create the structure by unlinking from the pool. To do that you might find the following thread useful but I'm not sure if it works the same in a Project Server environment since I don't use Project Server. Maybe Dale will check back in with some more savy advice. (http://answers.microsoft.com/en-us/office/forum/office_2010-project/correct-use-of-a-resource-pool-for-a-master/6f4d12cf-d76d-4699-a618-8b900509ee59)

    With regard to your latest test, which Resource custom field seems to create the issue?

    John


    Tuesday, May 12, 2015 4:24 PM

All replies

  • Jessica,

    First of all, which version of Project are you using? Second, is it updated with the latest service pack and latest Public Update (formerly cumulative update)?

    We can't tell if you've done something wrong since we don't know what you did nor do we know if your version is updated. For example, are you using a resource pool? Are you using a master project? How do you have it set up to run your multiple project report?

    More information is necessary in order for us to help you.

    John

    Wednesday, May 6, 2015 10:34 PM
  • Hi John, 

    Thanks for trying to help. 

    I'm using Project Pro 2013. Neither project is a master or sub project. I open a new blank project, select top row, go to Project tab and insert project. Choose first project name from list of available and wait for it to be inserted. Select next empty row and repeat for project two. Go to visual reports, select my Excel report template and click view. Opens it up in Excel, displayed as pivot table, and all the data for work and cost columns are doubled. 

    Enterprise resource pool = yes. 

    When I open Project I uncheck 'load summary resource data'. 

    Visual report template is based on Assignment Usage type. Usage is months.

    Report not specifically set to run for multiple projects but I assumed it would be the same as for a singular project which works perfectly.

    Thursday, May 7, 2015 8:16 AM
  • Jessica --

    Dumb question:  In your master project, are you expanding the tasks and summary tasks in each subproject before you run the Visual Report in Excel?  I am trying to duplicate your situation, but cannot duplicate it.

    Also, it would help us if you could tell us the specifications for your Visual Report.  What fields do you have displayed in the following PivotTable sections:  Filters, Columns, Rows, and Values?  And in what order if you have multiple items in any of those sections?  Let us know and we will try to help you.


    Dale A. Howard [MVP]

    Thursday, May 7, 2015 1:29 PM
    Moderator
  • Jessica,

    You didn't tell us if your installation of Project 2013 is updated with SP1. It should be. It also should be updated with the April 2015 public update, which will automatically occur if you have Office set to auto-update. Or if you don't have auto-update enabled, check the following: http://blogs.technet.com/b/projectsupport/p/msp13.aspx

    Just for reference, you DO have a master file. That's what is created when you do an Insert Project.

    You also mention that you have an Enterprise resource pool, indicating you are in a Project Server environment. Since Dale checked into the thread and  is a Project Server guru, I'll let him take this one from here.

    John

    Thursday, May 7, 2015 2:45 PM
  • I've tried it with all tasks expanded and no tasks expanded - same result.

    Report specs:

    Template data type was assignment usage and usage data is months.

    Fields included are: Cost, Work, Resource Category (resource field, lookup), Cost Type (task field, lookup), Role (resource field, lookup), Resource Name, Resource ID (resource field, text).

    Pivot table: No Filter. Columns = Monthly calendar, values. Values = Work, Cost. Rows = Cost Type, Resource Category, Role, Resource name, Resource ID (id field is included as a 'property') so doesn't actually appear in my rows list.

    Cost type,resource category, months, and years are subtotalled and I have grand total on.

    Running the report on each project separately gives the correct numbers. I have a couple of resources who only appear in one project so I'm certain it's not a mistake of overallocation.

    I also noted previously that I wasn't using a master project - meaning, neither of these projects has the other inserted into it already, and then again when I create the new master to run the report. So no duplication that way.

    Appreciate your help.

    Thursday, May 7, 2015 8:07 PM
  • Jessica --

    I do not believe this is an issue related to Project Server, so I would welcome John's further input on the problem.  Have you tried removing any of the fields from the PivotTable to see if the numbers stop doubling?  I would try that first if it were me.  My gut tells me this has something to do with one or more of the custom fields with lookup tables that you are using.  Please try that and let us know the results.  Thanks.


    Dale A. Howard [MVP]

    Thursday, May 7, 2015 8:25 PM
    Moderator
  • Thanks, Dale.

    I have tried removing everything from the pivot - basically leaving just the values - and the problem remains.

    Monday, May 11, 2015 10:21 PM
  • Jessica,

    I don't mean to harp, but you still haven't told us if your version of Project 2013 is fully updated. If it is not, then you need to do the update before we can be of any help.

    John

    Monday, May 11, 2015 10:35 PM
  • sorry, yes, all up to date.

    It does seem to be something in the pivot table. I cannot double click on an entry to see the data behind it - I get an error. See pic attached.

    I just started another report from visual report template, which has the same issue with doubling the numbers, even for those that are only in one project. However, this one I can double click a field and see the data, which is clearly the correct figure.

    I'm stumped.

    Tuesday, May 12, 2015 2:18 AM
  • Jessica,

    Okay Project is up to date. May I also assume Office is up to date?

    Let's back track a bit to make sure we understand what you do and don't have. If I understand correctly from one of your previous posts, if you run a report for a single project, all the data is correct but if you run it for a master project, the data is doubled. Is that correct?

    When you created your master to run the report, what did you do with the master after you were done? Did you save it? If so, did you also save all the subprojects? Have you ever moved, renamed, written over, or saved off any of the subprojects?

    John

    Tuesday, May 12, 2015 2:40 AM
  • Yep - to my knowledge everything is up to date.

    Yes to your summation of the problem.

    I'm creating the master as a new project and then inserting both of the other projects into what is then 'Project 1'. Here's my steps:

    Open Project, uncheck box for 'load resource summary data'

    Create blank project (project 1)

    Go to Project ribbon and insert subproject (project a)

    Click next blank row and insert subproject (project b)

    Go to visual reports and select report, change usage to months and click 'view'

    Report loads in Excel, shows wrong data

    Go back to Project and close without saving any of the projects

    I don't believe project a or b has ever been renamed or written over. Certainly never moved. Neither of them should ever have had a sub-project inserted into them either.

    Tuesday, May 12, 2015 2:49 AM
  • I've tried to re-create the report again, just using one dimension at a time to see if one in particular causes the problem. I did try in my current template to remove each of them to see the impact and there was no change, but in re-creating with only one at a time the error only happens when I use one of my Resource custom fields.
    Tuesday, May 12, 2015 3:20 AM
  • Jessica,

    Where do you see the option to "load resource summary data" when you open a project? Is that part of the Project Server environment?

    It sounds like you are doing the right thing by not saving any of the files after you create the master. My concern was that if you saved the master, you would be creating a linked structure and that is prone to corruption. Just for reference you might be better off to uncheck the option to "link to project" option in the lower right corner of the Insert Project window. That will create a static master and combine all the resources into a single independent file. Run your report and then close without saving.

    Since you are using a resource pool, you do have a linked structure and therefore the possibility of corruption is present. Try the static master approach and see if that works. If it does not, you may need to re-create the structure by unlinking from the pool. To do that you might find the following thread useful but I'm not sure if it works the same in a Project Server environment since I don't use Project Server. Maybe Dale will check back in with some more savy advice. (http://answers.microsoft.com/en-us/office/forum/office_2010-project/correct-use-of-a-resource-pool-for-a-master/6f4d12cf-d76d-4699-a618-8b900509ee59)

    With regard to your latest test, which Resource custom field seems to create the issue?

    John


    Tuesday, May 12, 2015 4:24 PM
  • Success (and failure)!

    Unlinking the projects once they were in the master did help. I also had to change my master project start date to the date the sub-projects started, else my report only displayed data from the master start date onwards (originally was set at today). After this the work resources hours and cost displayed correctly.

    However, it does something funky with my cost resources - it still says they are cost resources but it takes away all the cost and displays a number of hours. It did this as soon as I unlinked to the real project. I can't even see any correlation between the original cost and the hours displayed, e.g. one vendor had no cost assigned in May, but this came through on the report as 22 hours.

    Load Summary Resource Assignments is on the log-in menu when I open Project. I have a 'computer' profile and a Project Server (we actually use Project Online but same same?) profile. On this menu there is a check box. It essentially lets you see what allocation your resources have on other projects when you've opened your own project.

    Re the other test - it was any/all of the resource fields. Even if I brought them into the report but didn't use them in the pivot it still had the problem. I also tried creating new fields and no change.

    Really appreciate your ongoing help with this.


    Tuesday, May 12, 2015 9:05 PM
  • Jessica,

    I don't know how much of this relates to the Project Online environment but it sounds like using the static master instead of a dynamic master is the first step you need to take. With regard to cost resources I'm not sure what to say. I created a very simple file with three tasks and one vendor task. I also created a resource pool with three resources, two work and one cost type. I then set up the file to use resources from the pool. Each of the three normal tasks had a work resource assigned and the vendor task had the cost resource assigned. I then inserted that sample file into a static master and ran a Resource Cost Summary Visual Report. The Excel pivot table showed the work resource hours and the cost resource cost as expected.

    I near as I can tell, my sample files represent the basis of what you have but the primary difference is that I am using Project Pro 2010 stand alone (i.e. no server environment).

    Again, I think someone with Project Server (or Project Online) expertise needs to jump into this thread and see if they can help. Maybe they can better simulate what you have.

    John


    Tuesday, May 12, 2015 10:22 PM
  • Thanks, John.

    It appears my visual report is working as intended so I will make a new thread for the issue I now have un-linking my subproject and see if that gets some responses.

    Wednesday, May 13, 2015 9:42 PM
  • Jessica,

    You're welcome and thanks for the feedback. I'm still thinking that you have some corruption in your resource pool/sharer file structure so I'd advise un-linking the pool and sharer files and then re-build.

    John

    Wednesday, May 13, 2015 9:56 PM