locked
Authorized and Requested Holidays RRS feed

  • Question

  • Hi Folks,

    Could anyone advise... what is the easiest way to get a list of authorised and requsted holidays for every resource in the Resource Database?

    Thanks for any advice

    Sam


    Tuesday, March 15, 2011 12:16 PM

Answers

  • Hi Sam,

    sorry for falling in. Sachin's query works great for 2007 and 2010. However, there is a nice new view in 2010 "MSP_TimesheetLine_UserViewCF" giving you all you are looking for in an easy way. You can filter for ProjectName = Administrative and will see  the status you are looking for in TimesheetLineStatus.

    Regards
    Barbara

    Sunday, March 20, 2011 11:09 AM

All replies

  • Add all of the resources to the resource sheet of an MPP file, select Reports, and edit the report to include schedule exceptions.

    That's a quick trick to see all of the schedule exceptions.  It's not the cleanest report, but may work.


    Andrew Lavinsky [MVP] Twitter: @alavinsky
    Tuesday, March 15, 2011 1:45 PM
  • Hi Sam Pi

     

    Which Version are you using 2007 / 2010 ?

    Are you using Time sheet Administrative time reporting functionality, for capturing resources to request holiday, if using timesheet then you might want to get data from the OLAP

    Let me know if this helps


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Tuesday, March 15, 2011 2:28 PM
  • Hi Sam Pi,

    Sorry i dont know what you want to refer by "authorised", but as per my understanding you want to fetch all time enter by any resource in holidays, vacation under your "Administrative Task" in timesheet.

    So in this case below query can help you to get that exact data.

    <!-- [if gte mso 10]> <mce:style>

    SELECT     dbo.MSP_TimesheetProject.ProjectName,
                          CASE dbo.MSP_TimesheetClass.ClassName WHEN 'Standard' THEN dbo.MSP_TimesheetTask.TaskName ELSE dbo.MSP_TimesheetClass.ClassName
                           END AS TaskName, dbo.MSP_TimesheetResource.ResourceName, dbo.MSP_TimesheetActual.ActualWorkBillable,
                          dbo.MSP_TimesheetActual.TimeByDay
    FROM         dbo.MSP_TimesheetActual INNER JOIN
                          dbo.MSP_TimesheetLine ON dbo.MSP_TimesheetActual.TimesheetLineUID = dbo.MSP_TimesheetLine.TimesheetLineUID INNER JOIN
                          dbo.MSP_TimesheetProject ON dbo.MSP_TimesheetLine.ProjectNameUID = dbo.MSP_TimesheetProject.ProjectNameUID INNER JOIN
                          dbo.MSP_TimesheetTask ON dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID INNER JOIN
                          dbo.MSP_TimesheetResource ON
                          dbo.MSP_TimesheetActual.LastChangedResourceNameUID = dbo.MSP_TimesheetResource.ResourceNameUID INNER JOIN
                          dbo.MSP_TimesheetClass ON dbo.MSP_TimesheetLine.ClassUID = dbo.MSP_TimesheetClass.ClassUID
    where projectname = 'Administrative'
    ORDER BY dbo.MSP_TimesheetActual.TimeByDay DESC.
    Sachin Vashishth MCTS
    Tuesday, March 15, 2011 2:55 PM
  • Thanks Gentlemen,

    Really appreciate your help on this. The only way I was able to start getting the info was with the SQL report. MSProject reporting and the cube seemed unable to get the info.

    Thanks for sharing this SQL Sachin, it is 90% of the way there - what I am after is all requested and approved holidays, so I'll tinker with what you have kindly provided and see if I can get that - hopefully it is just a status for requested / approved.

     I'll post my effort if successful

    Cheers

     

    Sam 

     

    PS I;m using Proj Server 2010 


    Sa
    Sunday, March 20, 2011 9:45 AM
  • Hi Sam,

    sorry for falling in. Sachin's query works great for 2007 and 2010. However, there is a nice new view in 2010 "MSP_TimesheetLine_UserViewCF" giving you all you are looking for in an easy way. You can filter for ProjectName = Administrative and will see  the status you are looking for in TimesheetLineStatus.

    Regards
    Barbara

    Sunday, March 20, 2011 11:09 AM