none
Reporting Timesheet data RRS feed

  • Question

  • Hi,

    I have created a custom Timesheet view in PWA 2013. This custom view contains the following columns:

    Project Name, Task Name, Work  (assignment), % Task Complete, Process Status, Actual Work, % Work Complete.

    I would like to create an excel report which contains the above columns. Try as I may, I cannot find any report in the standard reports which contains these columns. Essentially we want a report which displays all the data for the above columns per resource. Where can I define (in data connections), which Timesheet View to use for the pivot table in order to select the custom columns?

    Your assistance is much appreciated in this regard.

    Kind regards,

    Jaco


    Wednesday, April 23, 2014 2:31 PM

All replies

  • Hi Jaco,

    You can build a cube in Project Server and then check the OLAPTimesheet template. It should meet the requirements.


    Cheers! Happy troubleshooting !!! Dinesh S. Rai - MSFT Enterprise Project Management Please click Mark As Answer; if a post solves your problem or Vote As Helpful if a post has been useful to you. This can be beneficial to other community members reading the thread.

    Wednesday, April 23, 2014 5:36 PM
  • Hi Dinesh,

    Unfortunately I do not see those custom columns we created in the new Timesheet View when I pull the OLAP Timesheet report.

    I just want to make sure I am checking the right report... I click on Reports > Data Connections > "ServerName"PWAOLAPDatabase > OLAPTimesheet

    Is the above correct?

    Thank you,

    Jaco

    Thursday, April 24, 2014 9:23 AM
  • Assuming this is on premise and not online PS 2013 version

    You will need to add the custom columns to your cube configuration, navigate to CA >> PWA Settings >> select your cube >> configuration >> add assignment custom columns to the desired cube and rebuild the cube that should make your custom columns appear in cube and then can be used for reporting

    Alternatively you can use reporting tables and views within DB for reporting purposes, you will need to make use of MSP_EPMAssignment_Userview along with MSP_EPMProject_Userview, MSP_EPMTask_Userview, MSP_EPMTimesheet_* views/tables to get the desired data


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com


    Thursday, April 24, 2014 2:04 PM
    Moderator
  • Hi Sunil,

    Regarding your last paragraph:

    "Alternatively you can use reporting tables and views within DB for reporting purposes, you will need to make use of MSP_EPMAssignment_Userview along with MSP_EPMProject_Userview, MSP_EPMTask_Userview, MSP_EPMTimesheet_* views/tables to get the desired data"

    How do I do this? How do I use reporting tables and views within DB for reporting purposes?

    Thank,

    Jaco

    Tuesday, April 29, 2014 1:01 PM
  • So with project server installation set of tables and views are created out of box specifically for reporting purposes, just like one you require,

    hence i mentioned check out the reporting tables and views available within the database to get the info you need, for more information on what to get from where, you should check this http://blogs.msdn.com/b/project_programmability/archive/2013/01/28/project-server-2013-reporting-database-rdb-schema-reference.aspx

    Or you can download the 2013 SDK and it has all the reporting tables & views referenced along with details on what contains what


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com


    Tuesday, April 29, 2014 1:11 PM
    Moderator
  • Hi Sunil,

    Thank you for your assistance. We are still struggling to obtain the correct information for our desired report.

    The custom fields we created are below in the Timesheet View we created. We want to pull a report containing the columns as per the Displayed fields: Project Name, Task Name/Description, Work [Assignment], %Task Complete [Task], Process Status, Actual Work [Assignment] and % Work Complete[Assignment]

    Please could someone assist me with compiling the desired report as explained above?

    Many thanks,

    Jaco


    Wednesday, April 30, 2014 10:56 AM
  • Hi Jaco,

    Have you considered using the OLAP DB?

    You first have to add your custom fields going to the OLAP cubes configuration (custom fields are not automatically inserted in the cubes, you have to do it manually) then use the out-of-the-box sample reports or templates to build your timesheet report. 

    See reference below: 

    http://technet.microsoft.com/en-us/library/ff465339(v=office.15).aspx

    http://blogs.msdn.com/b/brismith/archive/2012/11/12/project-server-2013-requirements-to-build-an-olap-cube.aspx

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Wednesday, April 30, 2014 12:35 PM
    Moderator
  • going through the thread As it has been posted earlier the easiest would be to include the fields in OLAP cube through configurations(explained earlier) and then using timesheet cube to pull the data in excel

    on other hand you can refer to this EPM report pack II, which was released for 2007 but pretty much with little modification would work for other version, specifically in there, there are 2 reports related to task and time sheet the datasets behind those reports will get you relevant queries to get the details from reporting DB though you may still need to modify it to include your custom fields

     

    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    Wednesday, April 30, 2014 12:39 PM
    Moderator
  • Hi Guillaume/Sunil,

    If I created the custom fields in the Timesheet view as explained above, how do I add these to the cube? When in cube configuration, Do I select Cube: Project or Task, or Resource, or Assignment? and then do I also select Project under Measures and add it there? I am quite confused here.

    Thanks,

    Jaco

    Wednesday, April 30, 2014 1:22 PM
  • Hi Jaco,

    When you go to the cube configuration, add the custom fields at the level you need. If you need to use the custom field to see data at project level, add the custom fields selecting  the project item in the drop down list.

    See reference below:

    http://technet.microsoft.com/en-us/library/ff465342(v=office.15).aspx

    Then I'd advice to run some test: add the custom fields to dimensions and measures, build the cube then play with the timesheet sample report.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Wednesday, April 30, 2014 1:32 PM
    Moderator
  • Thank you Guillaume,

    Why do I get the following when trying to save the cube cofig:

    Wednesday, April 30, 2014 1:47 PM
  • It might be that you have added too many custom fields or that you have special caracters in your custom fields.

    In case you don't have more than 100 custom fields added, try adding one by one the custom fields and saving after each one, so you'll find the culprit.


    Guillaume Rouyre - MBA, MCP, MCTS

    Wednesday, April 30, 2014 1:50 PM
    Moderator
  • Jaco,

    Check out ElliJ's posts on the topic here for some queries: https://projectserverpants.wordpress.com/2014/03/05/time-reporting-queries-its-about-time/

    One of the challenges you'll face is that the timesheet is Assignment level data whereas you seem to be asking for task level data. What's the question you are attempting to address?

    The Process Status is also problematic as it isn't a discrete column in the reporting database. TimesheetLineStatus is the closest to it. See this rather lengthy discussion that ElliJ and myself had on the topic. http://social.technet.microsoft.com/Forums/projectserver/en-US/a94a5764-8ae9-4a9c-b7fe-19b3cdd275af/timesheet-line-status-is-it-all-a-lie?forum=projectserver2010general

    Treb Gatte, Project MVP | Twitter | http://AboutMSProject.com

    Friday, May 2, 2014 5:32 AM
    Moderator