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.

Share This Post
6 Comments
  1. Is there any advantage in using the Excel add-in over simply adding Calculated Measures to the relevant Cube in OLAP configuration?

    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?

    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.

    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?

    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?

    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

    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>

Please complete this equation so we know you’re not a robot. *

Thanks for submitting your comment!
You must be logged in to comment.