How to tackle this monthly reporting requirement RRS feed

  • Question

  • I am creating a proof of concept in Project Server 2013 for a customer.  They are already using Sharepoint 2013 for document sharing, collaboration rooms and meeting management in projects. Now they would like to see what PS2013 can bring to the table. They are very interested in the reporting cabapilities as they are wasting time on a lot of manual updating of Excel spreadsheets, Powerpoint presentations etc.  I am still a bit new to the product so there are some things I am struggling to solve.

    One of the requirements is to have a monthly statusreport. In this the report you have your time and costs (which will be derived from them updating necessary fields in Project Pro). They also have some other data which is:

    - Capacity [red, yellow, green] (do they have enough capcacity to deliver)?

    - Stakeholder engagement [red, yellow, green] (is the project well enough anchored with stakeholders?)

    - Risk level [red, yellow, green]

    - The Quality level in the project [red, yellow, green]

    - Risks (list of the 3-4 most critical risks)

    All of this is put in manually in a ppt today  and there are no calculation behind the status they put in. It is an overall evaluation by the PM from report to report.

    So how to implement this in project server? The built in status report is fine, but I don’t think that will help me here since it only supports sections of multiple text fields. I would also want the statuses to be visible in project center and in reports. Should a PDP be created where they choose the status in custom fields with lookup tables? Can it be done from the project site?

    How do you recommend reporting on these statuses on a monthly basis?

    Thanks for your help!

    Tuesday, August 4, 2015 7:39 AM


  • Hi,

    There is much to say about this concern.

    First you have to precisely define the business needs and segregated what should/could be automatically calculated, what should be retrieved from the project site, what should be a custom field manually set in a PDP:

    • Risk level: if you are managing the risk in the default risk list from the project sites, then all risks data are stored in the content database and you can easily report on it. For example, you can say if you average risk exposure among the project is more than X, then the risk level is red. Or if you have more than 2 critical risks, then the risk level is red. Similarly, you can easily retrieve a risk list in your status report, filtering on the Top 5.
    • For the capactiy, it depends on how to you manage it. Depending if you are using proposed versus committed booking type, managing overallocation, generic resources, you'll report of different data with different calculations. For example, a rule might be that you should not have generic resources assigned in projects in a 2 months horizon. Another criteria would be to check for overallocations in the projects or more globally in the resource pool.
    • For the quality level, also what is quality should be defined. This could be based on the EVM, baseline versus actual, variance, cost, work... 
    • Then some other information could be set in a PDP with a custom field, such as the stakeholder engagement which cannot be easily calculated.

    Once defined, then you should choose a tool to creating the dashboard. Depending on the tool, you'll expose it accordingly (for example in a PDP, in a document librairy, in the project iste):

    • Excel Service: you need to create your SQL query and it requires doing some formatting for the report,
    • SSRS: requires more SQL skills but gives you more querying capabilities for crossing information and calculating data, such as risk leve,
    • PowerBI: more integrated and easier formatting
    • .NET: requires development skills but no limitations
    • 3rd party report packs

    Finally see some references for reporting:

    • http://www.fluentpro.com/project-server/business-intelligence-products/project-status-reports/
    • https://technet.microsoft.com/en-us/library/ff603998.aspx
    • http://www.senseiprojectsolutions.com/solutions/report-packs/
    • https://pwmather.wordpress.com/category/reporting/

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, August 4, 2015 8:09 AM