none
Billing Category in Timesheets from SQL RRS feed

  • Question

  • I need to know where to get the values from the Administrative Time categories (shown as Billing Category on the timesheet). Examples, are Sick time, Vacation, etc. We have some categories that are marked as Working, such as Meetings, Training, etc. I can get the values but just as a generic "Administrative" as the Project Name. I need the actual hours billed for each unique category. I will be writing a stored procedure in SQL that Excel will connect to.
    Saturday, May 29, 2010 12:07 AM

Answers

  • Thanks to Gurpreet:

    I feel the easier way would be to create a ‘Data Analysis View’ using ‘Timesheet’ cube. It provides the fields necessary to slice and dice the reported time. For example,

    ·         ‘Timesheet Project List’ can be filtered for ‘Administrative’ project.

    ·         ‘Timesheet Class’ would provide the ‘Billing Categories’.

     

    In case, they want to use SQL… they can look into the combination of following ‘Stored Procedures’ in Reporting database…

    ·         MSP_TS_GetReportingAllTimesheetLinesData (Provides individual Timesheet Lines including fields like ClassUID/ClassName which would be Billing Category GUID/Name)

    ·         MSP_TS_GetReportingAllTimesheetActualData (This would provide the Actual billable or non-billable work against each Timesheet Line)


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Tuesday, June 1, 2010 6:11 PM

All replies

  • I think you can get that from the reporting db. Look at the predefined Tables and Views with Timesheet in the name.

    --

    Rod Gill
    Microsoft MVP for Project - http://www.project-systems.co.nz
     
    Author of the only book on Project VBA, see: http://www.projectvbabook.com
     

     
    "Chad Olson" wrote in message news:a6dd3699-1c57-437f-88b6-068557bc6a2d...
    I need to know where to get the values from the Administrative Time categories (shown as Billing Category on the timesheet). Examples, are Sick time, Vacation, etc. We have some categories that are marked as Working, such as Meetings, Training, etc. I can get the values but just as a generic "Administrative" as the Project Name. I need the actual hours billed for each unique category. I will be writing a stored procedure in SQL that Excel will connect to.


    __________ Information from ESET Smart Security, version of virus signature database 5154 (20100528) __________

    The message was checked by ESET Smart Security.

    http://www.eset.com
    Sunday, May 30, 2010 1:18 AM
    Moderator
  • I've looked at the all predefined tables and views. I can't find where the actuals get mapped to those categories.
    Monday, May 31, 2010 7:06 PM
  • Hi Chad,

    these categories are named "Class" in timesheet tables. You haven't mentioned your version?

    2007: There is a view MSP_TimesheetClass_OlapView in RDB and you will find ClassUID in MSP_TimesheetLine_OlapView.
    2010: There is a great new view "MSP_TimesheetLine_UserViewCF" in RDB. Administrative categories are listed as TimesheetLineClass and TaskName.

    Regards
    Barbara

    Tuesday, June 1, 2010 6:13 AM
    Moderator
  • Thanks to Gurpreet:

    I feel the easier way would be to create a ‘Data Analysis View’ using ‘Timesheet’ cube. It provides the fields necessary to slice and dice the reported time. For example,

    ·         ‘Timesheet Project List’ can be filtered for ‘Administrative’ project.

    ·         ‘Timesheet Class’ would provide the ‘Billing Categories’.

     

    In case, they want to use SQL… they can look into the combination of following ‘Stored Procedures’ in Reporting database…

    ·         MSP_TS_GetReportingAllTimesheetLinesData (Provides individual Timesheet Lines including fields like ClassUID/ClassName which would be Billing Category GUID/Name)

    ·         MSP_TS_GetReportingAllTimesheetActualData (This would provide the Actual billable or non-billable work against each Timesheet Line)


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Tuesday, June 1, 2010 6:11 PM