Other Projects and Commitments with Wackadoodle Math

Since way back in the olden times before Project Server 2010, we’ve been experiencing this “Other Projects and Commitments” issue off and on.

In a Resource Usage view in Project Pro, the “Other projects and commitments” total does not match the work in the projects listed.

Below is an example, but the total can either add numbers which don’t appear, or fail to add numbers which do appear.  Look for any mismatch between the total project hours and the hours supplied by the projects in the list.

badmath

We can usually fix a single instance of this by finding the offending project, replacing the resource with a local generic resource, removing the resource from the project, publishing, then replacing the local resource with the enterprise resource.

That’s the only fix I know, sadly, but I have finally found what I think might actually be the cause, and that’s somewhat exciting.

To me.  Yes, I know I need a life.

I found a thread on the MS forum which points to it – http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/63d77cc7-4542-4a48-b821-034c516b0a81/

The poster indicates that the issue might be caused by adding a local resource with the same name as an enterprise resource, and then publishing and clicking “yes” to replace that local resource with the enterprise one.

I went back through all my notes and I can’t confirm a single user which we’ve had to fix didn’t get added that way.  I know the last two instances of this were certainly set up like that from the start (it was me, I admit it!) but who knew, right?

Then I replicated it on our test server.

[EDIT: SP2 has rendered the replication steps invalid, but we still get these from time to time.]

The steps are easy:

Create a new project.  Add a local resource with the same name as the enterprise resource like this:

Obama; Barack

Now assign them to a task. Give them some work.  Not too much, they are very tired already.  Publish, and when it asks if you want to replace the resource with the enterprise one, say yes. 

Now go back and change the start date of the task you assigned Mr. Obama to. (ok, I doubt he’s really in your resource pool) Publish.

Go scroll around and compare the “other projects and commitments” total to the projects on the list there.

See any bad math?  Here’s the weird part – I can replicate this on my test server, but not on the prod server.  There, it seems intermittent.

For now, we’ll instruct our PMs to not do that anymore, but ultimately, I intend to get this feedback to MS so that maybe they can actually figure this out.  It’s got to be a lot easier to troubleshoot when you can replicate it, right?

Advertisements

14 thoughts on “Other Projects and Commitments with Wackadoodle Math

  1. The calculation is (Total Hours from all Projects) / (Number of Projects). If you have 16 hrs of work in total from all projects but some of them have 0 hrs, The total from all committed projects is going to be less than 16 hrs.

    • Are you saying that the totals in “Other Projects and Commitments” are not a total of the hours in other projects, but an average amount per project?

      If that is the math, it’s most certainly not doing that correctly, either. Are we talking about the same thing here?

  2. Were you ever able to give this feedback to Microsoft? I’ve experienced this same issue on two different environments (unfortunately, I can’t be sure that the original users that had the issue were local turned enterprise) and wasn’t sure if someone’s already alerted the masses or not. I couldn’t find any hotfixes specific to it, but my hotfix searching abilities are not something I brag about 🙂

    • I do have a case open with them and just gave then copies of our DB. I don’t really understand why this issue is so elusive, though.

      And on the hotfix thing- their website is practically impossible to navigate through on those! Why do they make it so hard?

      I will update if I ever get a resolution for this, thanks for reading!

      • Hello,

        I have the same issues, on 2 productions environments…
        It will take me days to change all the resources by Geenric one…

        Did you had new from Micrsoft on that please?

        regards.

      • Unfortunately, I do not know any fix for this issue that doesn’t require manually fixing it. However, there appears to be more than one cause as well as more than one fix.

        The first thing to determine is whether or not opening all the schedules a resource is assigned to makes the math work correctly. If you open them all and look in a resource usage view, do you still see unaccounted for hours, or does it all add up at that point?

        Also, which rev are you using in 2010? I’ll be happy to help you however I can – I have dealt with this a lot, and while it’s not an exact science there are some tricks that make it easier to deal with.

      • I ended up opening a case with Microsoft on this, and they did identify this as a bug. I think my Microsoft contact mentioned that it had been resolved in a previous version (2007) and apparently had reappeared somewhere down the line in 2010. First, he had us update Project Server and Professional to the latest service pack. Then, he had us do the following in development (and later production, duh!):
        1. Backup the PWA Published database
        2. Run the following query on the PWA Published database: delete from [dbo].[MSP_ASSN_ENTERPRISE]

        This query deletes all the entries in this table. We then had to re-publish all of the projects using the ProjTool (http://blogs.msdn.com/b/project_programmability/archive/2010/11/03/projtool-for-project-server-2010.aspx) in order to repopulate the table with the correct assignment data.

        Microsoft said this fix may cause Project Pro cache issues. If a user sees two entries for the same project in the “Other Projects and Commitments” section, they will need to close and check in the file, and then completely remove their cache folder from their machine. Removing cached files individually via File > Project Options > Save will not resolve the duplicate project issue. Microsoft said that we shouldn’t expect all users to encounter this problem, but to be on the safe side, we could preemptively have all users remove their cache folders.

        In our case, we had project managers preemptively remove their cache folders, to avoid drama and didn’t have any duplicate issues.

        Unfortunately, Microsoft didn’t give me any insight into why this was happening or if this fix is permanent. In our case, I have noticed some slight discrepancies in the woe totals here and there since we did these steps, but usually they resolve themselves after opening the file in question. We haven’t had to run the query since doing so with Microsoft back in early November 2013.

      • Jennifer, this is excellent information, thank you! When I was working with MS on this, I don’t know why they didn’t offer this solution – perhaps because on reviewing our DB, the cause you are describing wasn’t found in the Published database?

        I have found a few items which I think may be contributing to this since then – summary tasks of assignment being set to “manually scheduled” seems to be linked somehow, but I don’t usually have time to play around with them, I just fix them as quickly as possible.

        I’ve been working to try to help Quentin, another commenter here, hopefully this will be the solution he’s looking for!

        Thanks for sharing!

      • Hey,

        Thank you very much for your help!
        I will contact Microsoft Next week, with your post, and see what happen.

        I have tried the manual way to solve the issue, but it take me hours for only one resource !

        I will let you know for sure !

        Thanks !

  3. Hello,

    Thank you for helping me.
    In reallity I’m not sure that it’s exactly the same issue, but I wanted to invest on this side.
    For me the hours is well calculated in other project and committement. But when I open all my projects (more than 140…) in a master plan, then I go in resource usage view, and I expand the Other project and committement.
    At this stage, I have no line under “Other project and committement”, but on the summary of this tasks, I have on few resources (5 resource under 70) a work at arround 0.0012 spread all over 40 years…
    Why “arround”, because when I open with another apps (other desk) this number can change a little bit, like 0.0015, or 0.02, etc.

    My architect have look at the cube during few days, and this work Manday doesn’t appear anywhere in the datawarehouse. It’s only link to the application itself.

    My version is 14.0.7015.1000, I know it’s not the lastest version, but I’m in a big company, and it’s the only version that have been approved…

    I will be glad if you had some suggestion 🙂
    Thank you for your help !

    Regards.
    Quentin

    • Quentin, you should take a look at Jennifer’s solution from MS above. It’s a much more “grand sweeping” solution than the manual ones I know about. You definitely want to push your organization to a newer version if at all possible – but applying a fix doesn’t correct the data that’s already affected. If you think her solution will work, you may still want to open a case with MS as well, to have them walk you through these steps as they involve altering your Published DB directly. Jennifer and I have both had them accept this issue as a bug, so hopefully your support agreement with them will cover it. Please let me know how it goes, I hope this helps.

  4. Ok, I took the SQL that Jennifer supplied and did a bit of poking around in the Published database on our test server and came up with some refinements.

    If you pull the records for a specific resource in that table, you will see an entry for each project they are assigned to, and then one other record which doesn’t have a project name and has a proj_uid of 11111111-1111-1111-1111-111111111111 (see below for all the queries) That sucker right there is the culprit, as far as I can tell. I was able to specify the resource by GUID and then the 1111-1….etc record as criteria. Once you delete that line and clear your cache, then you can open and publish any of the projects that they are assigned to and it will correctly rewrite that record.

    For Quentin, you could delete all 1111….etc lines, or you could do it by person, depending on how many you have.

    This all makes sense to me – that record is where Project is pulling the “other projects and commitments” summary. This explains why the lines underneath look correct and why the mismatch doesn’t appear in the Resource Center under Resource Assignments.

    It seems like it would be a lot less impacting to delete one specific row in the DB instead of all the records, and not having to republish all your projects again makes it easier too.

    I would still recommend working with MS on anything like this where you are directly querying and editing your Published DB, and you should always test everything in a test environment. Personally, I’m waiting for MS to get back to me because I’m not running this against my prod environment until I have their go-ahead. But I hope this helps! What I’d really like to do is find a consistent way to force it to rewrite that row without altering the DB directly.

    Query to find the records:

    SELECT
    ASSN_NAME,
    PROJ_UID

    FROM
    MSP_ASSN_ENTERPRISE

    WHERE
    RES_UID = GUID_OF_Resource

    Query to delete by Res_UID and proj_UID:

    DELETE FROM MSP_ASSN_ENTERPRISE

    WHERE
    RES_UID = GUID_OF_Resource
    PROJ_UID = 11111111-1111-1111-1111-111111111111

    • We moved to 2013 in the summer of 2015 and I haven’t seen the issue rear its ugly head since then. You may want to check subsequent patches for 2010 to see if it’s been addressed since then. Hope you find a solution – it’s a nasty bug to deal with!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s