none
Recreate Assignment Work by Resource Report RRS feed

  • Question

  • Hi --

    I have been tasked with recreating the Assignment Work by Resource report, I understand I need to access capacity, availability, work over time etc. for selected resources.

    I would like to query the project server 2010 reporting database direct, however is this ok or should I use the SDK (I don't have any experience with project server SDK)?

    If I'm ok to query directly then what is the best approach - using SSRS with a chart with a capacity overlay, or is there a better way? As I'm sure you can tell, any help or guidance appreciated.

    Creating a representation of the resource report direct from the database using SSRS feels as though it should be straight forward, I'm keen not to over engineer this one report.

    Thanks!

    Friday, January 3, 2014 10:30 AM

Answers

  • Hi,

    As an starting point and for reference you can download Resource Assignment Work and Capacity report SQL query from here.

    Yes now answering to your question.

    Yes it is perfectly fine to hit Reporting database for your reporting purpose. Also explore Reporting database schema from PS 2010 SDK to better understand the Project Server Reporting.

    Initially Use SSRS Report Server Project Wizard and table/view which you may need are

    1 MSP_EPMAssignment_UserView

    2) MSP_EPMAssignmentByDay_UserView

    3) MSP_EPMResource_UserView

    4) MSP_EPMResourceByDay_UserView

    Then later you can add SSRS chart and etc to create your Resource Management dashboard.


    Sachin Vashishth MCTS

    • Marked as answer by _c Monday, January 6, 2014 9:10 AM
    Friday, January 3, 2014 12:18 PM

All replies

  • Hi,

    As an starting point and for reference you can download Resource Assignment Work and Capacity report SQL query from here.

    Yes now answering to your question.

    Yes it is perfectly fine to hit Reporting database for your reporting purpose. Also explore Reporting database schema from PS 2010 SDK to better understand the Project Server Reporting.

    Initially Use SSRS Report Server Project Wizard and table/view which you may need are

    1 MSP_EPMAssignment_UserView

    2) MSP_EPMAssignmentByDay_UserView

    3) MSP_EPMResource_UserView

    4) MSP_EPMResourceByDay_UserView

    Then later you can add SSRS chart and etc to create your Resource Management dashboard.


    Sachin Vashishth MCTS

    • Marked as answer by _c Monday, January 6, 2014 9:10 AM
    Friday, January 3, 2014 12:18 PM
  • Hi Sachin - Thanks but page not found!
    Friday, January 3, 2014 2:14 PM
  • Sorry my mistake check this "http://gallery.technet.microsoft.com/projectserver/Server-2010-SQL-Get-5842010f"

    Sachin Vashishth MCTS

    Friday, January 3, 2014 2:18 PM
  • Thank you Sachin, fantastic, that really helps!

    Do you happen to know where the Primary Role field value is held?

    Friday, January 3, 2014 2:39 PM
  • Sorry, but can you be more specific about "Primary Role"

    Sachin Vashishth MCTS

    Friday, January 3, 2014 2:53 PM
  • Not to worry, I have the required columns.

    Now that I can create a dataset based on the SQL, how do I create a histogram/bar chart in SSRS which has assignment work (y axis) and a capacity line (x axis) across 12 months (x axis)?

    Thanks!

    Friday, January 3, 2014 3:56 PM
  • Hmmm, below are few SSRS chart basics that you must understand before going into Chart report development.

    The chart data of Reporting Services is organized into three main areas: values, category groups and  series groups.

    • Category groups are distributed along the x-axis or horizontal axis.
    • Values are shown along the y-axis or vertical axis.
    • Y-axis values must be a numeric data type.
    • A chart, like any other data region, is attached to one particular dataset.
    • You can group data within each axis.
    • At minimum, a chart should have one aggregated field for the value and one grouped field for the category.
    • Charts cannot be created using the Report Project Wizard. The Report Project Wizard does not support chart or free-form data regions.

    Then based on your requirement, you need to put Months in category to be shown on X -Axis and Assignment data in Y-Axis.

    For detail please go through this article

    Also to create Capacity Line along X-Axis following blog may help you.

    Let me know in case you still have any doubts.

    Also would suggest you to close this thread as your query regarding how to fetch data from Project Server is resolved and now your queries/doubts are more towards SSRS reports. Then would be better if you start a new post here on SSRS forum "http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlreportingservices".

    You'll get better response there comparing to Project Server Forum.


    Sachin Vashishth MCTS

    Saturday, January 4, 2014 5:08 AM
  • Agreed, thanks again Sachin. I'll create a new thread for the Chart requirement.
    Monday, January 6, 2014 9:10 AM
  • Your welcome, happy to help you!!!

    Sachin Vashishth MCTS

    Monday, January 6, 2014 9:30 AM
  • Sachin

    Do you have a similar SQL statement for Project 2007? We are trying to generate the same report for that version.

    Lisa

    Friday, October 17, 2014 3:04 PM
  • A more relevant question, do you know where I could get a similar SQL statement/report for Project 2013?

    Lisa

    Wednesday, October 22, 2014 2:10 PM