Adding Conditional Formatting to a Portfolio Milestone Report

After writing that last post, I was still vaguely dissatisfied with how the report turned out.  My goal in developing the report was to add conditional formatting to the PivotTable to increase the richness of the data available in the view.  I felt that I had failed to do that.

Now, my wife is fond of throwing Mongolian platitudes at me on occasion, and in this case, the one that kept coming to mind was “Davs heevol ustil; ajil heevol duustil,” which is one of those folksy sayings that translates roughly into “If you’re going to put salt in the water, make sure it dissolves – and if you’re going to do a job, make sure you complete it.” (And yes, it sounds much better in the original form.)

So, with thoughts of salty water swirling in my head, I went and played with the report for a couple more minutes, and was able to work out the conditional formatting mechanism in Excel 2010.  Note that this may/may not work in Excel 2007 or Excel Web Services, but I’d be curious for feedback if anyone tests this out.


…so when last we visited this topic, we had an Excel based report showing all of our portfolio milestones along with a couple of key icons based on the Finish Variance field.


Following the same instructions as in that article, I created a second PivotTable on a second worksheet.  This PivotTable includes TaskPercentComplete and a custom Finish Variance field (=TaskFinishVariance/8).


Don’t worry too much about formatting the table as nobody is going to see it.

Now we go back to our first PivotTable, click on a Finish Date and add Conditional Formatting.  Set the field to display green when the TaskPercentComplete cell equals 100%.


See how the Sheet2!B4 references the appropriate TaskPercentComplete cell for that Stage Gate?  Note that you’ll have to remove the “$” which are added by default to the equation to fix the reference, i.e. the default will be “=Sheet2!$B$4=100,” but you’ll change that to “=Sheet2!B4=100.”

Go ahead and add more rules.  Go into the Conditional Formatting dialog box to modify the order that the rules are applied in.


After working through all of that, in my example I have the following rules:

  1. All stage gates that have been completed appear as green.
  2. All stage gates with a finish variance > 10 are red.
  3. All stage gates with a finish variance between 0 and 10 are yellow.
  4. All stage gates with a finish variance < 0 are green.

Which appears as follows….


….much easier to read than what I had before, I think.

Adding Conditional Formatting to a Portfolio Milestone Report

Reporting on Portfolio Milestones with Excel

Well, I seem to be on a bit of a BI tear.  Not sure why, but based on past experience, once I start playing with reporting ideas, that generally leads to other ideas, and then I end up with a half dozen random reporting posts all in a row.  Oddly enough, that seems to happen annually right around the Discovery Channel Shark Week promotion.  Maybe there’s something about sharks that prompts me to ruminate on project data representation.

I haven’t yet done much with Excel based reporting on this blog, so I figured it was about time to set my sights on it and to see if I could generate a portfolio milestone report.  This report is a common request that I’ve seen from customers and on the InterWebs, i.e. how to create a simple milestone report for a project program or portfolio. 

The goal of the endeavor is to display the dates for predefined key milestones or stage gates, and then to provide some indication as to whether or not that stage gate is on schedule.  Essentially, the report will give a quick view at a glance of key milestones within a specific portfolio of projects.  Building off of this post I did a couple of weeks ago on developing a portfolio timeline, here’s a quick and easy report to display key milestones in Excel.


The assumption is that the organization has several main stage gates.  In this case, I am basing the report off of four stage gates, but it wouldn’t be difficult to accommodate more.

Enterprise Custom Fields

After playing with a number of different options, I realized that the easiest and most elegant solution simply requires a single enterprise custom field attached to a lookup table. 

First, I create the lookup table with the stage gate names.


Then I create a custom task level text field married to the lookup table.  Set the default value to blank.


That’s pretty much it for the Project Server side of things.  To create some sample data, I created a couple of projects and flagged the stage gates in each schedule.  The idea is that you would do this with your own stage gate milestones.


(And props to Victor for pointing out that my project illustration is all wrong and that Stage Gate 1 really should come before Phase 1, with Stage Gate 2 authorizing Phase 2 and so on and so forth.  I’m still too lazy to actually fix the illustration, but please note the procedural point.)

Generate the SQL Query

Rather than add a whole slew of custom fields to roll these values up to the Project Center, we can just use one of the Task tables in the Reporting database to pull all of the tasks identified as stage gates.

Thus, our next step is to generate the SQL query that we will use to pull data into Excel.  Since I am not much of a SQL hand, I create a new custom view in the SQL Management Studio to generate the query, which I then add to an ODC file.  Here’s a previous post on creating custom SQL views.  Note that you don’t have to save the view, but just use it to create the query.

In this case, I’ll be combining two tables: the EPMTask_UserView and the EPMProject_UserView.  I add a join on the ProjectUID field, and then make sure to include the new custom Stage Gate field as well as the TaskFinishVariance and the TaskFinishDate.  From the Project table, I include the ProjectName field.


At the bottom of the screen, you’ll see the SQL query generated, which in my example looks something like this…

SELECT     dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName, dbo.MSP_EpmTask_UserView.TaskFinishDate,
                      dbo.MSP_EpmTask_UserView.TaskPercentCompleted, dbo.MSP_EpmTask_UserView.TaskFinishVariance, dbo.MSP_EpmTask_UserView.[Stage Gate]
FROM         dbo.MSP_EpmProject_UserView RIGHT OUTER JOIN
                      dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmProject_UserView.ProjectUID = dbo.MSP_EpmTask_UserView.ProjectUID
WHERE     (dbo.MSP_EpmTask_UserView.[Stage Gate] <> N'””‘)

…don’t forget to filter on the Stage Gate field to exclude all tasks where Stage Gate is null or blank.

Create the ODC File

In order to get this data into Excel, we’ll need to generate an Office Data Connection file.  We could create one from scratch in Excel, but I kept running into error messages when doing so.  Rather than bothering to troubleshoot these issues, I just grabbed one from the BI Center and downloaded it to my desktop.


Double click on the ODC file to launch Excel.  From the Data tab, select the Connections option.  Then select the Properties button to edit the connection.


Give the connection a more descriptive name.  Then click on the Definition tab.


In the Definition tab, delete the Command Text, and swap it out with your SQL query.


Lastly, click the option to Export the Connection File at the bottom to export the new ODC to your desktop.  Either leave it on your local machine or upload it to the BI Center for use in future reports.

Create the Excel Report

Open a new Excel sheet.  Insert a PivotTable and choose your new ODC as the data source.


Configure the PivotTable as in the illustration below…


Now let’s add the Finish Date field to the mix.  Drag that field to the Values section.  You’ll see that it defaults to a count of the finish dates.  Click on the drop down next to the field and select the option to edit the Value Field Settings.


Change the field to display the maximum value and give it a more user friendly name.


Format the cells to display a date, and you should have something that looks like this…


Now let’s add the Finish Variance data to the mix.  The field is stored in SQL in terms of hours, so we’ll need to divide by 8 to get the appropriate number of days.  To do this, click on the PivotTable, and from the Options tab, select the option to create a new calculated field.


Configure the field as below and click on the Add button.


That results in a table that looks like this…


Format the Variance cells as integers.  (Tip: Hold down Ctrl and select all of the cells with your mouse).  Remove the Grand Total and Subtotals while you’re at it.


Highlight the Variance cells again and add Conditional Formatting.  In this case, I’ll choose an icon set.  Edit the rule so it looks as follows:


Go back to your Excel table and it should look like this:


…still not quite beautiful enough to put in front of an executive, but at this point, it’s just a matter of finagling the report in Excel to get it to look the way you would like it to.

Add a new project?  Simply right click on the PivotTable and select the Refresh option to update it with the latest and greatest information.

Reporting on Portfolio Milestones with Excel

Creating a Secure Enterprise Project Type

Here’s an interesting question that popped up recently…how does one secure a specific Enterprise Project Type within Project Server 2010 so that only specific people may be able to create it?

Process Overview

Let’s start off by reviewing the specific business scenario.  We have an organization that has small, medium, and large projects.  Each of those projects is mapped to various initiation processes and has been assigned a specific enterprise project type (EPT).


All large projects must go through an executive approval process, which for now is a manual application.  The application process happens through the use of a form, which then gets signed by the relevant executives.  Once the appropriate signatures have been received, the PMO administrative assistant creates the project record based on the form.

The other path is that users may create a Medium project, then apply to get that project promoted to a Large project.  The application process is the same as defined above, and once the approvals are in place, the PMO administrative assistant will promote the Medium project to a Large project using the Change Workflow button on the Project Center ribbon.

The challenge was that the users were getting confused and would go ahead and create a Large project – even when the approval hadn’t yet been completed.  We initially attempted to train this behavior away, but found a need for more effective enforcement mechanisms.

Honestly, my first thought was to implement some sort of workflow on the backend to ensure that only authorized users would create the restricted project type.  Of course, that would imply that I would have to actually get up and talk to a developer….something I try to avoid at all costs.  (Besides, that’s what the TFS Integration Pack is for, so I never again have to actually talk to a developer.)  Smile  Then, I realized there’s a pretty simple no code solution.

Modifying the Resource Department Field

Enter the workaround.  First off, we need to look at the Resource Department field.  By default, this is a single-select field, i.e. only one value may be entered.  If you’re not really using this field, great.  Leave it as it is and continue following the instructions below.


If you are using this field, then you may wish to consider changing it to a multivalue field.  Before you do that though, a couple caveats….

  1. Changing a field from single value to multivalue is irreversible.  You may not change it back.
  2. Changing a field to multivalue makes it much harder to bring into the Office Data Connection files you may be using to support your BI reporting.  Review the SDK for guidance on incorporating multivalue fields in the ODC queries.
  3. Changing a field to multivalue may potentially break your OLAP cubes.  See this post from Brian Smith for a nice synopsis.  That being said, in my preliminary testing, new cubes built after the field change seemed to work just fine.
  4. Even if you don’t break your cube, adding resources to multiple departments may cause odd numbers to appear in your enterprise OLAP cubes, i.e. a resource in “HR” will show in a different department than a resource flagged to “HR, Admin.”  Of course in this case, we’ll simply change the values for our administrative assistants or PMO members – who may not be under the same scrutiny for remaining capacity as a more technical resource.

That being said, if you have already changed the field to a multivalue, then ignore all of those caveats, and feel free to continue.

The next step is to add a new department to the Department lookup table referenced by the Resource Departments field.  I will call this new department “PMO Administration.”


Mapping the EPT and Resources

Map the Large project EPT to the PMO Administration department.


One more step remaining.  Pick the individuals who will be allowed to create the Large projects and add them to the PMO Administration department.  In this case, I’ll use Niraj Shah as an example.  I modify his record to include the right department.


Reviewing the Results

Now let’s test it out.  I navigate to the Project Center and select the option to create a new project.  I don’t see the option to create a Large project. (Good)


I try to change the project type by selecting a project, then clicking on the Change button on the ribbon.  Again, there is no option to promote a project to a Large project. (Good)


Now, I log in as Niraj’s delegate to see what his experience would be like.  In Project Center, I now have the option to create a new Large project. (Good)


…and in the Change Workflow dialog box, I have the option of promoting a Medium project to a Large project. (Good)


There are probably still ways to get around this obstacle, but overall, it’s an easy and elegant method to make it harder for users to accidentally stumble upon process exceptions.

Creating a Secure Enterprise Project Type

Selecting Time Tracking Options for TFS Integration

Well, it’s been an interesting couple of weeks professionally and on this blog.  During that time, I’ve set my sights on Microsoft Project Professional, SharePoint 2010, Visio Professional, and taken a brief excursion into the world of Ideation.  Spinning the Wheel of Required EPM Consultant Skills this week, I land on the popular TFS-Project Server integration package.

If you’re not familiar with the integration package, it’s an add in that comes as part of MSDN Ultimate that may be installed on your Project Server farm.  Once installed, the integration package essentially marries Project Server to your TFS 2010 SP1 installation – thus allowing bidirectional transfer of tasks and task updates from one to another.

I took a brief look look at the package here and here.  Since then however, I’ve had a number of discussions with clients both during demonstrations and as part of production installations.  This specific post is written to touch on one of those discussions that seems to invariably happen as part of the education process….a review the options available for time tracking.  To date, that discussion usually has revolved around whether the organization would like to track time within TFS or within the Project Server My Tasks/Timesheet interface.

Let’s take a look at the available options…after doing that, let’s take a look at the relevant decision factors.

Default Package Behavior

First off, let’s examine the default behavior of the package.  I am using the demo TFS image downloaded from Microsoft, but have created a new Team Collection and PWA instance to review what happens out of the box and to ensure that everything is set to the default options.

To start out, I create two new tasks, Task 1 and Task 2, then assign those task to the Contoso Administrator.  Each task is 5 days long, which translates into 40 hours (5d X 8h = 40h).


I navigate to the Resource Usage View within Microsoft Project Professional to see how those hours are distributed.  See how 8 hours are planned per day, starting on Thursday.  This is important to note because when the time is updated from TFS, the hours simply get booked from the first day on which Planned Work exceeds Actual Work, and then fill in the gaps until the task has been marked completed.  Here, those hours will begin being booked on Thursday and then proceed through the week.


We look at Visual Studio to confirm those tasks have successfully transferred from Project Server.


Clicking on Task 1, we navigate to the Project Server  tab to see the hours.  In this case, the hours have been pulled correctly from Microsoft Project and populated both in the Project Plan (Project Server) field and in the Work Item field (TFS).


I change the hours within TFS to indicate that I have worked 16 hours and have 24 remaining.  Note that I cannot edit the Project Server fields as they are greyed out.  These fields serve as a reference for what is actually in the project schedule.


Going back into Project Server, I look at the Approval Center page and the hours have been booked to the earliest dates available for the task.


After approving the changes, I look at the schedule in Microsoft Project Professional.  Everything looks good.  The task has been updated correctly.


Again, I review the task in the Resource Usage View to confirm that everything has updated correctly.  See how the hours are allocated against the first couple days of the task?

Picture the task as a series of glasses being filled with water, each glass representing one of the days of the task.  Once the first glass is filled, I move on to the second, then the third, and so on.


The moral of the story is that hours may be updated against the item in TFS, and those hours then get booked against the default hours for the task.  If the task was scheduled to start on Monday, then those hours are booked on Monday….and then any succeeding day on which the task is scheduled.

Note that this could create a potential issue where a task scheduled in the future is completed ahead of schedule.  If the data was updated within TFS, the task will be marked as complete in the future – generally a bad practice in the scheduling world.

Using Project Server My Tasks

So what happens if instead of simply booking time against a task, I desire to track when the work actually happened?  Instead of simply assuming that 8 hours were booked each day, I want the developer to book 4 hours on Monday, 2 hours on Tuesday, and 10 hours on Wednesday. 

Let’s look at the mechanics of this process first, then let’s back up and look at the implications.

Project Server allows the administrator to either set a standard project update mechanism or for each project manager to define the mechanism for their own projects.  In the following screenshot, I am setting the project to allow users to submit Actual Work per Day and then publishing the project.  If that option is greyed out, you will want to discuss with your server administrator.


I publish the project and navigate to the My Tasks screen using my browser.  Within this interface, I can update the actual hours worked per day (or per week).


I can also update the Remaining Work on the task.


Accepting those updates in Microsoft Project yields the following view.  Note that instead of simply booking a default 8 hours to the first two days of the task, I have booked 4 hours to each of the first two days.


Publishing the schedule pushes those task updates into TFS.  Here things start to look a bit strange.  I see that the Project Plan hours have updated correctly, but the TFS fields have not been updated, and still read Actual Work = 0 and Remaining Work = 40.


This gets a bit confusing admittedly.  What appears to be happening is that changes to the hour estimates in Project Server are communicated into TFS, but not transferred into the Work Item fields.  Fair enough.  I can see where that would make sense, i.e. I want to communicate to the developer what the schedule says, but I also want the developer to be able to make his own estimates – in the Work Item fields.

So I go ahead and make those changes in the Work Item fields.  I update TFS to read Actual Work = 10 and Remaining Work = 10.  I save the item.


The change in the TFS record then forces the change in Project Server.  Below, that change is visible in the PWA Approval Center.


…which then updates the project schedule.  My task in Project has now been overwritten by the TFS data.


If I want my users to update their hours in Project Server, this could be troublesome behavior – i.e. that the user can always update the hours within TFS and override the PWA timesheet interface.

Modifying the Default Behavior

But what if I want to change the default behavior?  What happens if I decide to make a commitment to put all of my TFS tasks into Project Server for tracking, and more importantly for my developers to track their time in a full featured timephased timesheet?

Well, after playing with the settings it would seem that this may be resolved with some minor changes to how the mapped fields are configured.

Take a look at the default field mapping.  When downloaded and opened as an Excel file, it appears as follows.  Note the DisplayTFSField and DisplayTFSMirror properties for the Completed Work and Remaining Work fields.


I set those two properties to “False” in the XML source file and re-upload the mapping.


Navigate to the Project Server tab for a TFS item.  See how the TFS values are not even displayed.  Two sets of books are no longer displayed.  More importantly, developers are forced to navigate to the PWA timesheet interface to update their tasks.


Note that since this is a hypothetical scenario, I admit that I haven’t worked through all of the implications of hiding the TFS fields for the Project Server fields, but I’d be curious if anyone has any feedback.

Decision Factors

For the most part, my conclusion is that the default behavior is structured to support an organization where tasks are updated primarily through TFS and then passed back into Microsoft Project.  Changes to TFS override Project Server.  Changes to Project Server get passed into TFS for reference.

Based on my discussions with clients to date, I would agree with this bias in the configuration of the default options.  Most of the groups I’ve discussed this functionality with have definitely leaned in that direction – especially as using TFS to update tasks means that they could leverage TFS to manage much of the detailed complexity of individual project schedules.

That being said, the discussion usually touches on a number of factors:

  1. Does the organization desire to keep a daily/weekly view of actual work performed?
  2. Is TFS being used to keep extraneous details about tasks out of project schedules, thereby allowing the project manager to focus on the high level details?
  3. Are developers expected to use any tool other than TFS to track their own hours?

Pending the resolution of any of those questions, the administrator may wish to look at the specific configuration of the integration package.

Selecting Time Tracking Options for TFS Integration

10 Things Project Server Admins Should Know About SharePoint

A couple of days ago, I posted on 10 Things SharePoint Admins should know about Project Server. That prompted SharePoint consultant Shane O’Hanlon (@GetOffMyPatch) to challenge me to list 10 Things Project Server Admins Should Know About SharePoint. Now, I would hardly claim to be much of a SharePoint expert…despite playing one at various SharePoint Saturdays, attending the local SharePoint User Groups, and having worked with some of the finest SharePoint consultants out there…but I figured it was worth a shot.

So here goes…..10 Things Project Server Admins Should Know About SharePoint (and for a wider post on Things Project Server Admins Should Just Know Anyway, please see here).

First, a bit of a caveat. These items may only be relevant to Project Server Admins who fit a specific profile. Will the Project Server Admin be the person installing the SharePoint farm on which it sits? (I certainly hope not.) Will the Project Server Admin be the person who is in charge of the disaster recovery solution? (Maybe/maybe not). So take these specific recommendations in the general sense in which they are proffered and filter them to your own specific roles and responsibilities.

1) Know How to Build Your Own Environment. Even if your organization gives you a shiny new hosted DEV environment to play with, nothing beats having your own Hyper-V image under your own control that you can break to your heart’s content and use to test out various data recovery and configuration scenarios. It’s far easier to restore to a previous snapshot than to submit an application to the server admins to get your DEV server refreshed every week – and it will save your doughnut and/or bagel budget when you have to start bribing them to do what you ask. Building your own farm is also a fantastic learning experience.

2) Understand the SharePoint Architectural Hierarchy. If you don’t know the difference between a web application and a site collection, go learn. Figure out how PWA and the BI Center fit into that schema. If you have the chance to catch the Shane Young/Todd Klindt roadshow, take a snapshot of “The Best SharePoint Slide Ever Made” and that should simplify everything. (And if anyone knows where that would be available online, please mention so in the comments list.)

3) It’s All About The Governance. Remember that projects are temporary. That means they eventually end. Lots of stuff is produced in the making of that project, not all of it valuable. Figure out what you’re going to do with that content. Project Server by default likes to create a site for every project, often resulting in thousands of abandoned project sites scattered through the farm. Implement a monitoring approach to flag unused or orphan project sites. Last but not least, make sure you have change and release management procedures in place – and just as important, make sure the folks in charge of patching the servers are in tune with your organizational reporting schedule. Don’t go deploying new patches and changes on the evening before your quarterly PMO report is due.

4) Size Does Matter. Often overlooked in the initial planning stages, make some rough assessments as to how much data will be stored on each project site. That will impact the fundamental architecture of your Project Server farm.

5) Plan Your Project Sites. Perhaps repeating the point about governance, but don’t simply start provisioning your farm with all sorts of project sites. Any changes to the content types or available metadata will have to get pushed out to each of those sites individually. Consider all of the changes you will need to make and build your template accordingly. Leverage content types to cascade changes out to already provisioned sites. Don’t know what content types are? See point #9.

6) Embrace the Complexity. Just because you are now a Project Server administrator, don’t try to implement a full SharePoint farm on your own. People study years to get up to speed on SharePoint (and then get recruited by consulting firms due to the market shortage of qualified consultants). Money spent architecting your farm and deploying it properly is money very well spent. That being said, don’t make a junior admin mistake and assume that all SharePoint consultants are created equal. There’re folks who are good at development, infrastructure and/or architecture. Some focus just on search. Don’t just assume that because they have SharePoint on their resume, they actually know how to design a system with Project Server.

7) Test Your Disaster Recovery Process. The Project Server disaster recovery process is separate (but related) to the SharePoint content recovery. Trust your SQL and SharePoint admins to implement disaster recovery, but verify that you personally, with your skill set, can restore all of that data to a new target environment (see Point #1).

8) Don’t Do The Express Installation. I hesitate to get this far down into the weeds on a technical issue, but this is probably the one absolute “No No” in the Project Server world that pops up on the forums every now and then. Don’t do the Express Installation. Project Server, and more importantly, the BI features, do not work well with SQL Express. Honestly, I am not even sure this option is still available in 2010 because I just gloss over it so quickly, that I don’t even think about it. Deploy a full farm with SQL, even if you’re just doing that on a single server.

9) Get Trained. Seriously. Go find a SharePoint class or two and take it. Try to find something offered by a reputable instructor. You don’t have to be an expert, but if you can get the basics up to the level of say, Site Collection Owner, you’re in good shape.

10) Learn to Love Your ULS Logs. ULS logs in 2010 are fantastic. Learn how to read them. Get into the habit of checking them on a daily basis or at the first sign of trouble. Even better, download the ULS log viewer utility from Codeplex and watch them in real time.

10 Things Project Server Admins Should Know About SharePoint

Great Project Server Content from the Product Team

Probably worthwhile to point you over to Robert Hoover’s post yesterday on some of the great content released this summer….

Big thanks to the folks responsible.  Make sure to check out the Pivot Viewer if you haven’t already.

Great Project Server Content from the Product Team

Creating a Portfolio Timeline Report in Visio (Part 3)

This post represents the third post in a series on creating a multiple timeline portfolio report within Visio. 


For the previous two posts, please see Part 1 and Part 2.

Now that we’ve got this beautiful report, what do we do with it?  Well, the obvious answer would be to publish it to SharePoint using Visio Web Services.  This not only allows us to surface the data, but it provides an interactive interface so that users may click on projects to review more information in the Shape Data Window.

To publish, simply hit File > Save & Send, and select the option to publish to SharePoint as a Web Drawing.


Note that one of the limitations (I suspect) of the Visio Web Service is that the data will get refreshed, and the shape data and data graphics will be updated on data refresh, but the x,y coordinates of an object will not get automatically recalculated until the Visio report is actually opened in Visio.  Once that’s done, the report will recalculate the locations of the projects, and may then be republished to SharePoint.


The report may now be used in a Visio WebPart on a PMO dashboard.  The best part, in my opinion, is that users can click on the individual projects and pull up a list of relevant fields directly from Project Server.

Creating a Portfolio Timeline Report in Visio (Part 3)

Creating a Portfolio Timeline Report in Visio (Part 2)

This is the second post in a short series on how to create a simple timeline view of multiple projects in Visio.  The goal is to produce a report that appears as below, and that is dynamically updated from Project Server data.


In the first post, we reviewed the Visio Timeline behavior and identified the modifications we’ll need to make to support this effort.  In this post, we’ll take those modifications and turn them into a shape stencil.

Create a New Custom Shape

Now that we have identified the default timeline behavior as well as the modifications that we will need to make, let’s go ahead and create our report.  As discussed in the previous post, go ahead and create a new timeline report.  Add the timeline to the top of the page, and configure the date range appropriately.

(In fact, to make life easier for you, I’ll just offer the same template that I developed when writing this post.  Go ahead and download it here.  If you decide to use that, go ahead and skip ahead to the section on Adding External Data.)

If you want to make your own template, then go ahead and make the modifications to the shape as identified in the previous post – with one crucial difference.  Don’t lock down the X or Y axes using protection.  If you’re using the same file from the previous exercise, go back to the Developer tab, select Protection, and uncheck those options.


Next click on the option to create a New Stencil, then drag the modified interval onto the Shapes window.


Right click on the new stencil to change the name to something more descriptive.


Add External Data

The next step is pretty simple.  Click on the Data tab and select the option to Link Data to Shapes.  That kicks off the wizard to link the report to SQL.  Select the PWA Reporting database and the appropriate table.  In this case, I will be using the EPMProject_UserView table for my data source.


Continue through the default options for the wizard until you get to the point where you have to select the columns and rows to be imported.  I will select the Project ID, Name, Description, Start, Finish, and Project Departments fields.  You may also select other fields that will be displayed whenever the item is selected within Visio.


Add a filter on the rows if that is required.


Keep going until you get to the screen where the primary key is defined.  Make sure the Project UID option is selected.  This will allow the items to be dynamically updated from SQL data.


The data now appears in the External Data window within Visio.


Now we have to map the fields from SQL to the fields identified in the Shape Data.  Right click on the External Data Window and choose the Column Settings  option.


Rename the fields so that they correspond to the Shape Data field names.  In the case of Project Description, I simply renamed it to something a bit more user friendly.


Back in the Visio drawing, select the stencil on the left.  Select one of the projects in the External Data window, and drag it onto the main screen.  The project record will default to the stencil that you have selected.  (Note that you just need to drag the project to the main screen and not onto the timeline.  If you drag the project record onto the timeline, it will revert to the timeline shape and not the customized interval shape.)

Confirm that the Shape Data translated correctly and that the correct color is displayed on the interval.  Change the value in the Row field to move the shape up or down.


Look good?  Start grabbing projects and dragging them onto the screen.  At this point I ran into either a feature or bug in Visio that caused me a little concern.  Whenever I grabbed multiple projects and dragged them to the screen, the Shape Sheet height formula would default back to the original formula.  When I drag them onto the screen one by one, the correct formula would be applied, and I could modify the location on the sheet with the Row field.

Moral of the story.  Drag each project onto the screen one by one.  It’s a bit onerous, but you only have to do this once, as afterwards the data will get automatically refreshed anyways.  The result will probably look something like this.


To preclude inadvertently breaking the sync with the timeline, select everything on the drawing, and add X and Y axis protection from the Developer tab.


From there, it’s a simple matter of displaying the Shape Data window, and manually modifying the Row field to get the report formatted appropriately.


Feel free to edit the dates on the projects to improve readability or perhaps to do some what-if analysis on the portfolio.  Once you’ve updated any of the data fields, you only have to click Refresh on the Data tab to pull the latest data from Project Server.  That gives you the option of configuring the refresh process. 

The first time you do so, you may wish to check the option to overwrite your changes with the Project Server data.


Next up….publishing the report into SharePoint and a brief discussion of issues with the timeline template and the Visio Web Service.

Creating a Portfolio Timeline Report in Visio (Part 2)

Creating a Portfolio Timeline Report in Visio (Part 1)

The other day, one of the end users I support came to me with a sample portfolio report that had been generated manually within Visio.  The question he asked me was whether or not it was feasible to generate something like that out of Project Server data.  Upon review, it certainly looked doable, and it looked conceptually similar to a this post on generating single project timelines in Visio.

Seeing as I hadn’t played around with Visio in a while, I figured it was worth a shot.  Here’s what the final result looked like…


First off, my standard caveat.  I am not a Visio expert, nor do I purport to pretend that I know much about the tool.  I can figure out how to do the basic functions that I need it to do, and this case is no different.  I would certainly welcome any and all feedback from a “real” Visio power user on the instructions below.  Even if these instructions end up being wrong, I suppose they make a good starting point for an interesting discussion on the topic.

Another caveat, and a question to the SSRS developers out there….would this be any easier to knock together in SSRS?

…now back to our regularly scheduled program.

Create the Basic Timeline Report

We’ll start by creating a simple timeline in Visio.


Add the ruler timeline to the top of the page (or the bottom, I suppose – although you may have to modify these instructions accordingly).  Set the ruler to display the appropriate timeline for your project portfolio.


Now add a block interval, which is usually used to represent a single activity or phase. Let’s use this block interval to review the default timesheet behavior.


You’ll see that you can change the start and end date of the interval to automatically change the width of the shape as well as the location along the X axis…..provided that you don’t actually grab the shape and drag it off of the timeline.  Once you do that, you’ve broken the connection to the timeline, and changing dates will not control the X axis.

Go ahead, try pulling it off of the timeline and change the dates.  See how the connection has been broken?


Now delete the block interval we’ve just created, and let’s add a new one back onto the timeline.  Let’s take a look at the Shape Sheet for this interval.  To get the Shape Sheet to appear in Visio 2010, you’ll need to right click on the Ribbon, customize it, and then add the Developer tab.


Once you have the Developer tab displayed, look at the Shape Sheet for the new block interval.  It should look as follows:


Note the LocPinY field.  By default, it shows as “Height*0.5,” which represents a Y coordinate relative to the location of the timeline. 

Modify that formula to see how the interval moves up and down on the sheet.  In the following example I’ve changed the formula to “Height*3.”  Positive numbers move the interval down.  Negative values move the interval up over the timeline.


So now we’ve identified that we can control the position and width of the interval by modifying the values of three fields: Start, Finish, and LocPinY.

We’ve also identified, and feel free to check this on your own, that the minute you select the interval and move it around on the sheet, you’ve broken the synchronization with the timeline.  To prevent yourself from inadvertently breaking that synchronization, you can select the interval, and then the Protection option from the Developer tab.  Lock down the X and Y axes.


The only way that the interval may be changed now is by changing those three fields identified above.  This also means that the interval will always remain in sync with the timeline at the top of the page.

One more thing to play with while we’re reviewing the behavior of this sheet.  Right click on the interval and display the Shape Data.


Right click on the resulting Shape Data window and choose the option to Define Shape Data.


Create a new whole number field.  I am calling this “Row,” but feel free to use whatever term you would like.


Go back into the Shape Sheet and scroll down until you see Data section.  You should be able to find the newly created field in that section – although whether or not the name is automatically updated appears to depend on the version of Visio that you’re using.


In this case, you’ll see that Visio refers to the field as “Prop.Row.”  Go back up the Shape Sheet to the LocPinY field and modify the formula so that it now displays “Height*Prop.Row.”


Now, whenever you edit the shape data field, the interval will shift along the Y axis.  As this is all being done through manipulation of field data, the interval will remain safely in sync with the timeline at the top of the page.


To make things easier later on, I’d recommend leaving a value of 3 in the custom Row field.  That will help us format the report when we add all of our projects.

Oh yeah….lest I forget, I do have one more thing.  Going back to the original request from my end user, the desired report would color code projects based on a specific parameter such as phase, or status, or in this case, department.

Based on the instructions above, add a second field to the Shape Data called “Department.”


Click OK.  Now select the interval, and under the Data tab, select the option to define a new Data Graphic.


Configure the data graphic to change the color of the shape based on the value entered in the Department field.


Starting to feel good to stretch those long disused Visio muscles.  Next up, saving the modified shape as a stencil…

Creating a Portfolio Timeline Report in Visio (Part 1)

10 Things SharePoint Admins Should Know About Project Server

It’s a rite of passage within every Project Server implementation.  At some point, I sit down with the SharePoint administrator and brief them on what it is exactly that they’ve gotten into it – what exactly they’ll be supporting going forward.  Sometimes it’s part of the demo process.  Sometimes it comes much later.  Here’s the discussion in written form.

Top ten things a SharePoint Administrator should know about Project Server 2010 (in no special order):

1) Project Server schedule data resides in an additional 4 databases for each instance of PWA provisioned.  These databases need to be included in your disaster recovery plan, and are accessible from the Central Administration Farm Backup page.  Collaboration content is stored in your SharePoint content database.  A full backup/restore plan will need to accommodate all 5 databases – more if you plan on deploying multiple Project Server instances.  Note also that search will natively index any site based content, but will require the configuration of External Content Types to index the Project Server databases.

2) Despite its name, the Project Web Application is a site collection.  PWA is the top level site in the site collection.  However, PWA should never be the top level site collection within a Web App.  There should always be a top level site collection within the Web App, even if it’s a simple team site with security blocking anyone from accessing it.

3) The security structure within PWA is separate from the SharePoint security structure. Project Server maintains its own pool of users, which may be drawn from AD or other authentication mechanisms.  That pool is managed in its own interface.  This pool controls access to the main PWA site, and may be used to drive security on the individual project collaboration sites (see the next point).

4) Each project creation event may/may not provision a collaboration site (depending on the Project Server configuration).  The project collaboration site is essentially a plain old team site with the Project Server feature activated.  That site may be hosted within the PWA site collection or, with some configuration, outside of the PWA site collection.  Security on these sites may be automatically managed from within Project Server or that linkage may be turned off and replaced with a traditional SharePoint security model in cases where the organization has policies requiring AD-based security on SharePoint sites.  The same governance discussion that took place around your original farm needs to take place around these sites, i.e. disaster recovery and document retention policies, particularly as these sites are often intended to be transitory sites that get decommissioned at the end of the project.

5) Project collaboration sites may be edited within specific limits.  Most organizations decide to customize the default project site template – which indeed can be mapped to specific project types for the automatic provision of differently branded sites.  Content types do not come provisioned by default but may be easily implemented within the project site.  Four lists/libraries are provisioned as part of the Project Server feature: Deliverables, Issues, Risks and Project Documents.  Those entities may be edited, but do so with caution.  Project Server expects to see specific fields attached to each item within those lists and libraries.  Those fields may be hidden, but not deleted – and additional fields may be added as needed.  The Project Server feature also provisions a number of Web Parts to surface Project Server data.

6) Project Server requires SharePoint Enterprise features (and the concomitant licensing requirements). The upside of this is that Project Server relies on all of the SharePoint reporting tools to report against the Project Server databases.  Excel Services, PerformancePoint and SSRS may all be used, with the Project Server database providing your data source.  Project Server also provides the feature to optionally build OLAP cubes of project and resource data, which may in turn be used to provide additional data to reporting tools.

7) Patches are released on the same frequency as SharePoint patches.  In fact, patches are bundled with the bimonthly Office Server cumulative updates and service packs.  That being said, patching Project Server is a bit more sensitive as you should generally try to ensure that all client installations of Microsoft Project Professional are patched at the same time as Project Server.

8) Users need a client access license (CAL) to access Project Server.  More importantly perhaps, users need a CAL to access Project Server data.  As always, when in doubt about licensing issues, always refer to your Microsoft licensing specialist, but the rule of thumb that I’ve always followed is that if they are accessing real time data from the Project Server database, they need a CAL.  This means that even if you’re surfacing Project Server data via External Content Types into an External List on a non-Project Server farm, you still need a CAL to access that list.  The corollary to that rule that I learned recently is that for the most part, if you need to ask whether or not you need a CAL for a specific scenario….you need a CAL.

9) Project Server may be deployed on the main corporate intranet or within its own farm.  Architecting a Project Server deployment is a discussion that needs to happen early on in the process.  Consider whether the benefits of making Project Server available to the sites on your intranet outweigh the benefits of deploying Project Server to its own isolated farm.  Check online for a number of discussions around this topic.

10) Do not try to implement Project Server on your own.  Always consult with your local Enterprise Project Management implementation partner even if it’s just to put together a couple of roadmapping sessions.  Simply standing up Project Server with a “build it and they will come” approach almost never works.

That’s my list.  What’s on yours?

10 Things SharePoint Admins Should Know About Project Server