Business Intelligence

Adding custom calculations to Project Server's Portfolio Analyzer

Portfolio Analyzer in Microsoft Project Server 2002 allows you to view project data, such as work, broken down by project, resources, time, and other metrics. Learn how to customize this powerful reporting tool.

Portfolio Analyzer, the Online Analytical Processing (OLAP) reporting functionality in Microsoft Project Server 2002, allows users to view project data such as work, cost availability by project, resources, time, and other custom outline codes defined by the administrator. It provides a powerful reporting tool for analyzing data about projects.

Portfolio Analyzer delivers this reporting capability via the Office XP Web Components, a set of ActiveX controls that put the Excel 2002 Pivot Table and Pivot Chart functions into Web-based components. One feature of these controls is the ability to add a new calculated total to the Pivot Table. Totals are the data elements—Work, Actual Work, Baseline Cost—that can be reported on in a Pivot Table. Figure A shows an example of a Pivot Table showing both Baseline Work and Work broken down by the organization that is performing the project.

Figure A


In looking at these numbers, you can see an obvious problem: For every organization, the Baseline Work is exceeded, in some cases dramatically. It would be helpful to have the variance between Baseline Work and Work visible in the report. Work Variance is not among the default fields included in this reporting tool, but you can add it with a few clicks.

First, click on the Calculated Totals And Fields button (the calculator icon). From the drop-down menu, select Create Calculated Total (see Figure B).

Figure B


You’ll bring up the Commands And Options dialog box that shows the properties of the new calculated number. In the Name field, enter Work Var. In the text box for the calculation, enter the formula [Work]-[Baseline Work], as shown in Figure C.

Figure C


When creating new totals, if you’re unsure of the way to represent a particular field in the formula window, you can use the drop-down menu at the bottom of the dialog box to insert references to fields.

When you select an item in this drop-down menu, click the Insert Reference To button and a reference to that field will be inserted, at the cursor, in the formula field of the dialog box. This way, you can avoid errors caused by badly formed field names.

Figure D


Click the Change button, shown in Figure C. You can then see this new field in the table behind the dialog box in Figure D.

Figure D also shows the Format tab of the dialog box, which allows you to edit the way the total is displayed in the table. For example, if you change the Number field to Standard, it will display the value with commas and will also round the decimal values to the nearest hundredth.

Now you can see the difference between the Work and Baseline Work, but what about going further? A raw number can tell some of the story but, for example, the Assembly group (ASSY), as shown in Figure E, has a variance number of 22,112 and this is only the fourth-highest number. A closer look shows that the Baseline Work field is only 1,200 hours.

Figure E


Someone scanning down the variance column might not think that 22,112 was so bad compared to some of the other groups. You could make this analysis easier by adding another calculated total for the percentage of the original baseline work value that the variance represents.

Once again, click on the calculator icon and then select Create Calculated Total. Figure F shows the Commands And Options dialog box for this field.

Figure F


The formula is [Work]/[Baseline Work] and the total is named Work Var Perc. On the Format tab, the format is changed to Percent. When you click the Change button, you’ll see that the new total has been made visible in the table (see Figure G).

Figure G


Anyone can now easily see that the Assembly group (ASSY) is the big offender in going over baseline on hours. From this information, an executive or portfolio manager could draw a number of possible conclusions:
  1. The Assembly group is exceedingly poor at making estimates.
  2. The projects that this group does are subject to large changes in scope.
  3. The Assembly group project managers aren’t saving baselines for all of their projects.

No matter what the cause, further attention will likely be paid to this group.

Note that the addition of calculated totals using the method described here will add them to the current view only. For example, the totals added here will not be available to be added to other views in the Portfolio Analyzer. If you needed them for those views, you’d need to add them using this same procedure.
0 comments