Microsoft Project Server Portfolio Analyzer provides users access to views on their project and portfolio data that were difficult if not impossible to produce in earlier versions of Microsoft Project. One of these views is an S-Curve, which can be used to represent data trends over time.
I’ll explain how to create an S-Curve report in a Portfolio Analyzer view that will show your users the time-phased cumulative amount of work or cost in a line graph, overlaid on a bar graph showing the work for that time period.
This kind of graph (Figure A) is useful for visualizing the distribution of work or cost across the life of a project or group of projects. To get this graph out of Microsoft Project 2000 or Project 98, you had to export time-scaled data to Excel and then do some manual manipulation within Excel to get the graph. You had to do this for each project manually each time you wanted the updated data. With the Portfolio Analyzer (which uses OLAP cubes that are updated on a daily or weekly basis), all you have to do is build the view once and the data is up to date as of the last cube update.
|A Cost S-Curve report|
To build the view, log into Project Server Web Access as a user with the rights to create views (as the Administrator, for example), and click the Admin link on the top navigation bar. Then, click the Manage Views link and click the Add View button, as shown in Figure B.
On the page that comes up, select the Portfolio Analyzer radio button, as shown in Figure C.
From the field list, select Work and drag it into the yellow area marked Drop Totals Or Detail Fields Here. You’ll see a field with a total for the work from every project in your portfolio. Scroll to the bottom of the field list and find the Time dimension. Drop the Year dimension and the Month dimension onto the green Drop Row Fields Here area. When you’ve finished, your view should look like the one in Figure D.
The next step is to add a new Total to the list that will be the cumulative work across time. To do this, click the Calculated Totals And Fields button on the toolbar and select the Create Calculated Total item from the drop-down list. This will bring up the Commands And Options dialog box shown in Figure E.
Figure E shows the Name field and the formula field already filled in. In this example, we’ll call this new calculated total WTD, for Work To Date. Paste the following formula into your dialog box:
Then click the Change button. You’ll see the WTD field showing the cumulative work calculation. Close the dialog box by clicking the X. Right-click on the chart area and select the Commands And Options item on the menu. On the General tab, you’ll find a Select field. In this field, make sure that the Chart Workspace item is selected. Then select the Series Groups tab (see Figure F).
Select the entry for WTD in the Series Groups section of the dialog box at the top, and then click the OK button. The top part of your dialog box should look like the one in Figure G.
In the lower part of the dialog box, select the number 2, select Right in the Axis Position field, and then click the Add button. Figure H shows what the dialog box will look like when you click Add.
This will add a second axis on the right side of the graph, which is necessary because the WTD field will have values much higher than the Work field.
On the General tab, select WTD from the Select field, click the Type tab, and select either Line or SmoothLine, as shown in Figure I.
Your graph will look like the one shown in Figure J.
You’ll have an S-Curve that shows the work and cumulative work for every project in your database. Drop the Project dimension into the Drop Filter Fields Here section so that individual users can view the curves for particular projects. For example, Figure K shows this same graph filtered for a Software Development project with a detail of the year 2003.
While a work-based curve can be useful, some organizations prefer to see S-Curves created using cost data. You can easily customize this process to show cumulative cost instead of work. In the formula you paste into the Calculation tab, just use Cost instead of Work, like this:
Doing this and then dropping the Cost field into the view instead of the Work field will give you a cost curve instead of the work curve.