Loading...
Quick Links

Building a Resource Usage Report in Project Server 2010

In a recent MPUG Presentation, “Reports, Dashboards, and all that Jazz,” I covered building a Resource Usage report using the OLAP Cubes.

As a follow-up to that presentation, this article provides a step-by-step approach to building a very similar report.

In building this report, we will be using an Excel add-on called OLAP Pivot Table Extensions, which will allow us to easily perform calculations in an OLAP Pivot Table. It was downloaded from CodePlex. Personally, I have found this add-on very useful for almost all Pivot-Table-related reports.

Also, if you are not familiar with using Pivot Tables, I suggest that you get yourself primed up on that topic.

Assumptions:

  • I will assume in this report that your Analysis Services Cubes have been built, are operating successfully, and that you have the necessary permissions to author and publish reports to your Business Intelligence Center
  • The steps detailed below are possible only with versions of Excel 2007 or higher.

To get started use the following navigation:
PWA >> Business Intelligence Center >> Templates >> Your OLAP Cubes
From the list of the cubes, select the OLAPPortfolioAnalyzer cube.

This will open a template in Excel.

Now drag and drop the fields in the Pivot Table Area as shown in the picture below.

This will yield a Pivot Table like the one shown below.

Now let’s try to show % Usage instead of hours. To achieve this, we will need to calculate this value. This is where the OLAP Pivot Table Extensions will come into the picture.

Let us define % Usage as (Work/Capacity).

Right-click on the Pivot Table and select OLAP Pivot Table Extensions.

This will open up a dialog box like the one shown below.

Now write the formula we agreed upon above (shown in the picture below) and click Add to Pivot Table.

This will add the % Usage field to the Pivot Table.

Now let’s adjust the number format and add some color to the table. We might also remove the Capacity and Work columns, if we want to look at only the % Usage. You can remove any column from the Pivot table by dragging the column out of the Field List.

Now all that is left is to use conditional formatting to highlight the outliers. If we agree on the following colors:

50-100% = Green

>100% = Red

0-50% = Yellow

NA = Gray

then we can use Conditional Formatting as shown below:
From the Home tab, click on the Conditional Formatting button, and select Manage Rules

This will open the Conditional Formatting Rule Manager dialog box as shown below.
Click on New Rule to start building a new conditional formatting rule.

Select the Options, and set the format for Rule 1 (mentioned previously in the article).

Repeat the same steps for each other color. You will end up with rules as shown below:

and the results on the Pivot Table will appear as shown below:

There you have your Heat Map!!

Now, if you want to publish this to SharePoint, all you have to do is select the Save to SharePoint option while saving, as shown below.

This will make the report available in Excel Services.

Note: If you are using Excel 2007, the option to publish this to SharePoint would be Office Button >> Publish > Excel Services.

Written by Prasanna Adavi

Prasanna Adavi (Project MVP, PMP, MCTS, MCITP, MCT) is a Senior Project Portfolio Management (PPM) Consultant. specializing in helping organizations meet their business needs in the areas of Project Management and Portfolio Management. He is a regular presenter at various Project Server, EPM and SharePoint events across the country, and regularly blogs. He also runs a podcast dedicated to MS Project and Project Server. He has been awarded the MVP (Most Valuable Professional) award by Microsoft since 2014, for his continued contributions to Microsoft Project Community. Connect with him on Twitter or LinkedIn.

Share This Post

Customer Reviews

5
0%
4
0%
3
0%
2
0%
1
0%
0
0%
    Showing 6 reviews
  1. Is there any advantage in using the Excel add-in over simply adding Calculated Measures to the relevant Cube in OLAP configuration?
    0

    0

    You have already voted!

    Reply
  2. The instruction is really helpful. But I got the problem trying to determine the number of man-month resource allocated aggregated up. How can we get the man-month of allocation rather than the %Usage, If we roll up from individual resource into project, we want to see the sum of man-month instead of a % usage per month. How can we do it?
    0

    0

    You have already voted!

    Reply
  3. Thank you very much for your very valuable post. It is very useful and important. I have one question. Is there any possibility of grouping this resource usage report by the resource department? I am working with MS Project 2013. Thank you.
    0

    0

    You have already voted!

    Reply
  4. I agree with the above comments that this is a great post. I have one question.

    I use Project Server 2013. The BI seems to have been set up correctly and I can generate reports from both ‘Reports’ as well as ‘Analytics’. The challenge is that when I click on OLAPPortfolioAnalyzer cube, it doesn’t open an Excel template as you show in above. Instead it opens Excel with a limited number of fields (6) in the pivot list.

    How can I open the Excel template?

    0

    0

    You have already voted!

    Reply
  5. I agree with the above comments that this is a great post.

    I use Project Server 2013.

    I would like to generate a Resource Usage report using the OLAPPortfolioAnalyzer which is located in the “analytics” section of reports. I am able to see the report in Excel but it doesn’t open a template in Excel. That means I cannot drag and drop the fields in the Pivot Table Area. Instead I am restricted to six fields.

    It seems as if I am missing a step as this is the case with the other reports. What do I do so that I can select fields from the full list for my pivot table, in order to generate a comprehensive report?

    0

    0

    You have already voted!

    Reply
  6. Hi Prasana

    I tried the same which you mentioned the above, very nice it is possible bringing the same report rbs wise resource capacity vs project allocation.

    Note: resource capacity based on the org. calendar wise (holiday and org common holidays)

    Thanks
    Josh

    0

    0

    You have already voted!

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Thanks for submitting your comment!