none
SQL query for resource capacity RRS feed

  • Question

  • I am looking to create an sql query to extract data about capacity for timesheet period.

    I create one that work find by day but how to find the data by timesheet period.

    Any help is very Welcome.

    (I run Project Server 2010 in Sharepoint Farm with last CU)


    PWAdmin

    Friday, October 5, 2012 5:33 PM

Answers

  • Hi PWAdmin,

    are you looking for something like that? May need some improvement regarding performance.

    SELECT     dbo.MSP_EpmResource_UserView.ResourceName, SUM(dbo.MSP_EpmResourceByDay_UserView.BaseCapacity) AS BaseCapacityByTSPeriod, 
                          SUM(dbo.MSP_EpmResourceByDay_UserView.Capacity) AS CapacityByTSPeriod, TimesheetPeriod.PeriodName
    FROM         dbo.MSP_EpmResource_UserView INNER JOIN
                          dbo.MSP_EpmResourceByDay_UserView ON dbo.MSP_EpmResource_UserView.ResourceUID = dbo.MSP_EpmResourceByDay_UserView.ResourceUID INNER JOIN
                              (SELECT     dbo.MSP_TimesheetPeriod_OlapView.PeriodName, dbo.MSP_TimeByDay_OlapView.TimeByDay
                                FROM          dbo.MSP_TimesheetPeriod_OlapView CROSS JOIN
                                                       dbo.MSP_TimeByDay_OlapView
                                WHERE      (dbo.MSP_TimeByDay_OlapView.TimeByDay BETWEEN dbo.MSP_TimesheetPeriod_OlapView.StartDate AND 
                                                       dbo.MSP_TimesheetPeriod_OlapView.EndDate)) AS TimesheetPeriod ON 
                          dbo.MSP_EpmResourceByDay_UserView.TimeByDay = TimesheetPeriod.TimeByDay
    GROUP BY dbo.MSP_EpmResource_UserView.ResourceName, TimesheetPeriod.PeriodName
    Regards
    Barbara
    Thursday, October 11, 2012 9:29 AM
    Moderator
  • Hi,

    ok, I assumed week numbers with leading zero. Does this work?

    SELECT     dbo.MSP_EpmResource_UserView.ResourceName
    		 , SUM(dbo.MSP_EpmResourceByDay_UserView.BaseCapacity) AS BaseCapacityByTSPeriod
    		 , SUM(dbo.MSP_EpmResourceByDay_UserView.Capacity) AS CapacityByTSPeriod
    		 , SUBSTRING(TimesheetPeriod.PeriodName, 5, 
                          { fn LENGTH(TimesheetPeriod.PeriodName) } - 9) AS WeekNo
    FROM dbo.MSP_EpmResource_UserView INNER JOIN
         dbo.MSP_EpmResourceByDay_UserView ON 
         dbo.MSP_EpmResource_UserView.ResourceUID = dbo.MSP_EpmResourceByDay_UserView.ResourceUID 
         INNER JOIN 
          (SELECT dbo.MSP_TimesheetPeriod_OlapView.PeriodName, dbo.MSP_TimeByDay_OlapView.TimeByDay
                                FROM          dbo.MSP_TimesheetPeriod_OlapView CROSS JOIN
                                                       dbo.MSP_TimeByDay_OlapView
                                WHERE      (dbo.MSP_TimeByDay_OlapView.TimeByDay 
                                BETWEEN dbo.MSP_TimesheetPeriod_OlapView.StartDate 
                                AND dbo.MSP_TimesheetPeriod_OlapView.EndDate)) AS TimesheetPeriod ON 
                          dbo.MSP_EpmResourceByDay_UserView.TimeByDay = TimesheetPeriod.TimeByDay
    GROUP BY dbo.MSP_EpmResource_UserView.ResourceName, SUBSTRING(TimesheetPeriod.PeriodName, 
    			5, { fn LENGTH(TimesheetPeriod.PeriodName) } - 9)
    Regards
    Barbara
    • Marked as answer by PWAdmin Friday, October 19, 2012 11:36 AM
    Friday, October 19, 2012 4:55 AM
    Moderator
  • I would strongly suggest not using the _OLAPView objects for reporting. These are used by the Cube Building Service to generate the OLAP databases.

    The MSP_TimeByDay table pretty much has the same information so you can use that.

    Treb Gatte | @tgatte | http://AboutMSProject.com

    • Proposed as answer by PWMatherMVP, Moderator Tuesday, November 13, 2012 10:15 PM
    • Marked as answer by PWAdmin Thursday, November 15, 2012 8:25 PM
    Tuesday, November 13, 2012 9:24 PM
    Moderator

All replies

  • Hi PWAdmin,

    are you looking for something like that? May need some improvement regarding performance.

    SELECT     dbo.MSP_EpmResource_UserView.ResourceName, SUM(dbo.MSP_EpmResourceByDay_UserView.BaseCapacity) AS BaseCapacityByTSPeriod, 
                          SUM(dbo.MSP_EpmResourceByDay_UserView.Capacity) AS CapacityByTSPeriod, TimesheetPeriod.PeriodName
    FROM         dbo.MSP_EpmResource_UserView INNER JOIN
                          dbo.MSP_EpmResourceByDay_UserView ON dbo.MSP_EpmResource_UserView.ResourceUID = dbo.MSP_EpmResourceByDay_UserView.ResourceUID INNER JOIN
                              (SELECT     dbo.MSP_TimesheetPeriod_OlapView.PeriodName, dbo.MSP_TimeByDay_OlapView.TimeByDay
                                FROM          dbo.MSP_TimesheetPeriod_OlapView CROSS JOIN
                                                       dbo.MSP_TimeByDay_OlapView
                                WHERE      (dbo.MSP_TimeByDay_OlapView.TimeByDay BETWEEN dbo.MSP_TimesheetPeriod_OlapView.StartDate AND 
                                                       dbo.MSP_TimesheetPeriod_OlapView.EndDate)) AS TimesheetPeriod ON 
                          dbo.MSP_EpmResourceByDay_UserView.TimeByDay = TimesheetPeriod.TimeByDay
    GROUP BY dbo.MSP_EpmResource_UserView.ResourceName, TimesheetPeriod.PeriodName
    Regards
    Barbara
    Thursday, October 11, 2012 9:29 AM
    Moderator
  • Yes!

    Thanks Barbara, I will begin with your query!

    I like Technet!!


    PWAdmin

    Thursday, October 11, 2012 2:16 PM
  • Hi Barbara,

    I need a way to show only the week number (SEM-12-2012) - > 12. I had some trouble modifying your query, could you give my a little push?

    ThanKs!


    PWAdmin

    Thursday, October 18, 2012 5:44 PM
  • Hi,

    do you only want to use a substring of Periodname? Then you can use SUBSTRING(TimesheetPeriod.PeriodName, 5, 2) instead of Periodname.

    Or do you want the query to calculate week by date? In this case: where are you located? Europe and using ISO week or week definition of US and other countries?

    Regards
    Barbara

    Thursday, October 18, 2012 6:16 PM
    Moderator
  • It works, but I had this result...

    SEM-8-2012    8-

    SEM-47-2011    47

    PWAdmin

    Thursday, October 18, 2012 6:48 PM
  • Hi,

    ok, I assumed week numbers with leading zero. Does this work?

    SELECT     dbo.MSP_EpmResource_UserView.ResourceName
    		 , SUM(dbo.MSP_EpmResourceByDay_UserView.BaseCapacity) AS BaseCapacityByTSPeriod
    		 , SUM(dbo.MSP_EpmResourceByDay_UserView.Capacity) AS CapacityByTSPeriod
    		 , SUBSTRING(TimesheetPeriod.PeriodName, 5, 
                          { fn LENGTH(TimesheetPeriod.PeriodName) } - 9) AS WeekNo
    FROM dbo.MSP_EpmResource_UserView INNER JOIN
         dbo.MSP_EpmResourceByDay_UserView ON 
         dbo.MSP_EpmResource_UserView.ResourceUID = dbo.MSP_EpmResourceByDay_UserView.ResourceUID 
         INNER JOIN 
          (SELECT dbo.MSP_TimesheetPeriod_OlapView.PeriodName, dbo.MSP_TimeByDay_OlapView.TimeByDay
                                FROM          dbo.MSP_TimesheetPeriod_OlapView CROSS JOIN
                                                       dbo.MSP_TimeByDay_OlapView
                                WHERE      (dbo.MSP_TimeByDay_OlapView.TimeByDay 
                                BETWEEN dbo.MSP_TimesheetPeriod_OlapView.StartDate 
                                AND dbo.MSP_TimesheetPeriod_OlapView.EndDate)) AS TimesheetPeriod ON 
                          dbo.MSP_EpmResourceByDay_UserView.TimeByDay = TimesheetPeriod.TimeByDay
    GROUP BY dbo.MSP_EpmResource_UserView.ResourceName, SUBSTRING(TimesheetPeriod.PeriodName, 
    			5, { fn LENGTH(TimesheetPeriod.PeriodName) } - 9)
    Regards
    Barbara
    • Marked as answer by PWAdmin Friday, October 19, 2012 11:36 AM
    Friday, October 19, 2012 4:55 AM
    Moderator
  • It works!

    Thanks!


    PWAdmin

    Friday, October 19, 2012 11:36 AM
  • Sice We install the September 2012 CU the query dosen't work...

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'dbo.MSP_TimeByDay_OlapView'

    Weird...


    PWAdmin

    Tuesday, November 13, 2012 7:04 PM
  • I would strongly suggest not using the _OLAPView objects for reporting. These are used by the Cube Building Service to generate the OLAP databases.

    The MSP_TimeByDay table pretty much has the same information so you can use that.

    Treb Gatte | @tgatte | http://AboutMSProject.com

    • Proposed as answer by PWMatherMVP, Moderator Tuesday, November 13, 2012 10:15 PM
    • Marked as answer by PWAdmin Thursday, November 15, 2012 8:25 PM
    Tuesday, November 13, 2012 9:24 PM
    Moderator
  • Thanks It works!


    PWAdmin

    Thursday, November 15, 2012 8:25 PM