none
Reporting Database PS2007 RRS feed

  • Question

  • I have a query that uses the reporting database to get the projectname, rbs, resourcename, taskIndex, task name, assignemnt remaining cost, assignmentremaining work, assignemnt actualcost, and assignment remaining work from the assignment, task, resource, and project userviews.

    The remaiing cost that is displayed is not accurate.  For ent resources whose standard rates change in the future, the remaining cost for future tasks reflects thier current standard rate.  Has anyone ever had this problem?

    Tuesday, April 5, 2011 7:39 PM

Answers

  • Hi,

    after stripping slightly, I used the following:

    SELECT     TOP (100) PERCENT LEFT(ac.ProjectName, CHARINDEX('_', ac.ProjectName + '_') - 1) AS Expr1, ae.ResourceName, ab.TaskIndex, ab.TaskName,
                          ad.AssignmentRemainingCost AS RemainingCost, ad.AssignmentRemainingWork AS RemainingWork,
                          ad.AssignmentRemainingCost * .20 AS 'Admin Remaining Cost', SUM(aa.AssignmentActualCost) AS ActualCost, SUM(aa.AssignmentActualWork)
                          AS ActualWork
    FROM         dbo.MSP_EpmAssignmentByDay_UserView AS aa INNER JOIN
                          dbo.MSP_EpmTask_UserView AS ab ON aa.ProjectUID = ab.ProjectUID AND aa.TaskUID = ab.TaskUID INNER JOIN
                          dbo.MSP_EpmProject_UserView AS ac ON aa.ProjectUID = ac.ProjectUID INNER JOIN
                          dbo.MSP_EpmAssignment_UserView AS ad ON ad.ProjectUID = aa.ProjectUID AND ad.AssignmentUID = aa.AssignmentUID AND
                          ad.TaskUID = ab.TaskUID INNER JOIN
                          dbo.MSP_EpmResource_UserView AS ae ON ae.ResourceUID = ad.ResourceUID
    GROUP BY ac.ProjectName, ae.RSP, ae.ResourceName, ab.TaskIndex, ab.TaskName, ad.AssignmentRemainingCost, ad.AssignmentRemainingWork
    ORDER BY ae.ResourceName

    ANd all values are correct. So my question: were all projects included in this list published AFTER adding Standard Rate line for the future? RDB is not dynamic, so you need to open your plan afetr changing any rates and publish, to get new values available for reporting. Has this been done?

    Regards
    Barbara

     

    • Marked as answer by Jim Corbin Wednesday, April 6, 2011 11:40 PM
    Wednesday, April 6, 2011 2:03 PM
    Moderator

All replies

  • Hi,

    I can reproduce, all data is correct in my RDB. Could you post perhaps your query?

    Regards
    Barbara

    Wednesday, April 6, 2011 4:22 AM
    Moderator
  • thank you for your reply Barbara, here is the query

    select

     

    --format the project name

     

    left(ac.projectname, charindex('_', ac.projectname + '_')-1) as ProjectName

     

    ,

     

    --categorize each resource as either business or IT

     

    CASE LEFT(ae.RBS,15)

     

    WHEN 'IFBI.Technology' THEN 'IT'

     

    WHEN 'IFBI.Countryway' THEN 'IT'

     

    ELSE

     

    CASE LEFT(ae.RBS,5)

     

    WHEN 'IFBI.' THEN 'BUS'

     

    ELSE 'OTH'

     

    END

     

    END as BusType

     

    , ae.ResourceName

     

    , ae.[first name]

     

    , ab.TaskIndex

     

    , ab.TaskName

     

    , (ad.AssignmentRemainingCost) as RemainingCost

     

    , (ad.AssignmentRemainingWork) as RemainingWork

     

    , ((ad.assignmentRemainingCost) * .20) as 'Admin Remaining Cost'

     

    , sum(aa.AssignmentActualCost) as ActualCost

     

    , sum(aa.AssignmentActualWork) as ActualWork

    from

    MSP_EpmAssignmentByDay_UserView aa

    inner

    join MSP_EpmTask_UserView ab on aa.ProjectUID = ab.ProjectUID and aa.TaskUID = ab.TaskUID

    inner

    join MSP_EpmProject_UserView ac on aa.ProjectUID = ac.ProjectUID

    inner

    join MSP_EpmAssignment_UserView ad on ad.ProjectUID = aa.ProjectUID and ad.AssignmentUID = aa.AssignmentUID and ad.TaskUID = ab.TaskUID

    inner

    join MSP_EpmResource_UserView ae on ae.ResourceUID = ad.ResourceUID

    WHERE

    (aa.AssignmentActualWork > 0 or (aa.AssignmentActualWork = 0 AND ad.AssignmentRemainingWork > 0

    ))

    AND

    (ProjectName not like 'project admin%' and ProjectName not like 'T-%' and projectname not like 'Z%'

    )

    AND

    ae.ResourceName not like

    'unassigned%'

    AND

    ac.[Weekly Reporting (Y/N)]=

    'yes'

    AND

    (ac.[Project Status] = 'Pending' or ac.[Project Status] ='active'

    )

     

    GROUP

    by ProjectName, ae.RBS, ResourceName, ae.[first name],TaskIndex, TaskName, ad.AssignmentRemainingCost, ad.AssignmentRemainingWork

    order

    by ResourceName

    Wednesday, April 6, 2011 1:42 PM
  • Hi,

    after stripping slightly, I used the following:

    SELECT     TOP (100) PERCENT LEFT(ac.ProjectName, CHARINDEX('_', ac.ProjectName + '_') - 1) AS Expr1, ae.ResourceName, ab.TaskIndex, ab.TaskName,
                          ad.AssignmentRemainingCost AS RemainingCost, ad.AssignmentRemainingWork AS RemainingWork,
                          ad.AssignmentRemainingCost * .20 AS 'Admin Remaining Cost', SUM(aa.AssignmentActualCost) AS ActualCost, SUM(aa.AssignmentActualWork)
                          AS ActualWork
    FROM         dbo.MSP_EpmAssignmentByDay_UserView AS aa INNER JOIN
                          dbo.MSP_EpmTask_UserView AS ab ON aa.ProjectUID = ab.ProjectUID AND aa.TaskUID = ab.TaskUID INNER JOIN
                          dbo.MSP_EpmProject_UserView AS ac ON aa.ProjectUID = ac.ProjectUID INNER JOIN
                          dbo.MSP_EpmAssignment_UserView AS ad ON ad.ProjectUID = aa.ProjectUID AND ad.AssignmentUID = aa.AssignmentUID AND
                          ad.TaskUID = ab.TaskUID INNER JOIN
                          dbo.MSP_EpmResource_UserView AS ae ON ae.ResourceUID = ad.ResourceUID
    GROUP BY ac.ProjectName, ae.RSP, ae.ResourceName, ab.TaskIndex, ab.TaskName, ad.AssignmentRemainingCost, ad.AssignmentRemainingWork
    ORDER BY ae.ResourceName

    ANd all values are correct. So my question: were all projects included in this list published AFTER adding Standard Rate line for the future? RDB is not dynamic, so you need to open your plan afetr changing any rates and publish, to get new values available for reporting. Has this been done?

    Regards
    Barbara

     

    • Marked as answer by Jim Corbin Wednesday, April 6, 2011 11:40 PM
    Wednesday, April 6, 2011 2:03 PM
    Moderator
  • The new rates are entered with and effective date of 7-1-2011 and the projects returned in the query have been published quite a few times since they've been entered. the total remaing cost for tasks with work scheduled after 7-1-2011 should be slighly higher.  I ran a similar query in the publshed database to see if it returns different results, but it was the same.

    It seems like Project returns the remaing cost by mulitplying the remaining hours * resource current rate, instead of returning the remaing cost value shown in the remaining cost field of project plan (which is correct)

    Wednesday, April 6, 2011 2:20 PM
  • Hi,

    not in my case. To avoid confusion (for me), I set actual standard rate to 0, and new standard rate starting next monday to 10, so keeping it simple. With a project and a task assigned to this ressource, I have the correct remaining cost. 16 hours work this week * 0 + 40 hours work next weeek * 10 = 400, exactly what I can see in RDB running your script.

    What's your patch level?

    Regards
    Barbara

    Wednesday, April 6, 2011 2:29 PM
    Moderator
  • sp2 june 2010 cu

    Weird. i deleted the future rate and published, then I reentered it and republished it and now it is returning the correct numbers.  These projects were migrated from 2003 in Feb (the rates were entered in 2003), perhaps that is part of the issue.

    Well it works now.  Thank you for working throught this with me! :)

    Wednesday, April 6, 2011 2:47 PM
  • I was not of that much help, only telling that it should work as expected. I am always interested in issues, when I am convinced that it should work - that's the main reason of my initial response. Doing a lot with RDB, I was quite sure that I have never seen what you described.

    Good luck with your migrated system!
    Barbara

    Wednesday, April 6, 2011 6:32 PM
    Moderator