Author: Marc Soester

Marc Soester is the Managing Director of i-PMO, an organisation dedicated to successfully implement Microsoft's Enterprise Project Management (EPM) solution throughout Australia. Marc and his team have extensive expertise in the implementation of Microsoft Project Server and Portfolio Server as well as conducting training in Project. He has more then six years of experience implementing EPM solutions and is Australia's first and currently only Project MVP (Most Valuable Professional). You can contact him at

Ask the Experts: How Resource Sharing Works in a Master Project

Shelley asks: I am working with a resource pool that has about 30 sharer files. How can I work with resources from the pool along with some resources that are only working on one project? I have set the workgroups for the “local” resources as “none” (as you would if you were using Microsoft Project Server), but the pool still picks them up. I don’t want this to happen because some of customized fields in the resource pool are restricted, and the pool sends a message that it can’t copy a value to a restricted field, even a blank. So I need to figure out how I can stop the pool from picking up these new resources. Answer: In order to create a shared resource pool, create a Project called “Master Project” and within the Resource Sheet create all your Project Resources that you like to manage. Figure 1: A sample Master Project. Now create the Project files for each project you wish to manage using the Resource Pool from Master Project. Create the new project (in my example, it’s Project 1), then select Tools | Resource Sharing | Share Resources. Figure 2: Specifying the Resource Pool from which to pull resources. Ensure that you use the resources in your Master Project. Select OK and all resources held in the Master Project will also be displayed in your schedule. Figure 3: Resources held in Master Project will display in your project. You can now use these resources for your own project, and the Master project will be able to see the allocation of the resources on your project. Shelley responds: The description Marc supplied is exactly how I set up my pool. Because I have about 30 sharer files and I also needed to create a master plan that contained all 30 sharers, I called my file “Resource Pool” and linked each sharer to that before inserting it into my master file. So, knowing there isn’t a way to set it up with local and global resources is a great help, and I’ll go ahead and remove the restrictions I placed on some of the customized fields I put in the pool so it easily picks these new resources up. A note to other readers: Don’t set up any restrictions on customized fields to prevent users being told over and over that certain fields can’t be copied to the pool. That becomes very frustrating for the user. Plus when those little annoying things happen, it’s harder to get buy-in for using a pool.

Ask the Experts: When % Complete Won’t Calculate

Nancy asks, “I have been a project controller for about seven years. I have a problem that I cant figure out. My % Complete column has stopped calculating. Also, the Actual Duration and Remaining Duration are not being updated either. Actual Duration is always zero even though its well into the task. So Remaining Duration equals Duration. What would cause that?” Answer: This one is really puzzling. After reviewing the MPP file sent, I wasn’t able to see why the % completed doesn’t calculate. I have checked the settings which, are all fine. Then I exported the project into an XML file and re-imported it ( just in case the schedule is corrupt); but this didn’t change the behavior (which is a bummer). I’ve also noticed that some % completed updates actually work. Task 31, for example, works fine, but Task 3 doesn’t work at all in regards to the % complete. It seems that a combination of settings and corruption could create this effect. So here’s how I suggest you make it work. (I assume this isn’t a Project Server environment, but only an MPP file.) 1. Save the project as an XML file. 2. Open the XML file in Microsoft Project and create a new project. 3. Go to tools | option. Within the Calculate Tab select “Updating Task Status updates Resource Status.” 4. Save the project. Nancy replies: “Well, thank goodness! It’s in a Project Server environment, so I skipped steps 1 and 2. It still worked. Thank you so much!”    

Ask the Expert: Optimize Microsoft Project Performance

Joseph McDowell writes, “Microsoft claims Microsoft Project 2007 has the capacity of up to 400,000 tasks. However, we have a number of projects that when they get to about 1,000 tasks, things start to slow down, and when you get to 4,000 to 6,000 tasks, things slow down drastically. We’re running Project 2007 Professional on dual processor PCs with 2GB of memory. Are you aware of the practical limit for the number of tasks? One of the sales reps for Microsoft is now telling us, after we have purchased Microsoft Project 2007 in quantity that best practices is to have no more than 1,000 tasks. I have never seen that published by Microsoft, PMI, MPUG, or anyone else. Are you aware any published practical limits or have you any experience or thoughts in this area?” Answer: In my experience 1,000 tasks are no problem. Technically I would think it is possible to have up to 400,000 tasks, but the practicality of managing so many tasks is doubtful. The problem with more than 1,000 tasks, I find, is the management of the actual schedule. I wouldn’t recommend having more than 1,000 tasks, and, yes, the more tasks you have, the slower an MPP file will get. It’s the same with Excel. If, however, you have more than 1,000 tasks, trying switching Auto Calculate off and only recalculate when you really need it. You will find that this will improve the performance. In order to avoid more than 1,000 tasks, you may also consider a master project with two or more sub projects included. Not only will you be able to manage fewer tasks per sub-project, but it will also give you an overall view of all project tasks.

Ask the Expert: Importing Data from Excel into Project

Richard J. writes, I’m using Microsoft Project 2000 to import data from Microsoft Excel but some of the data isn’t appearing in Project, seemingly at random. This may be because there are 305 rows of data to import. Is there a limit to the amount of data you can import into Project from Excel or can you think of another reason why seemingly random data won’t be imported Answer: I’m sure there’s a limitation regarding the number of rows that can be imported from Excel into Project, but it’s in the thousands, not the hundreds. (No, I don’t know the exact number; if somebody else does, let us know!) Therefore, your current issue is probably unrelated to product limitations. When importing data from Excel into Project, check two things: 1. Spaces that appear in front of the first word of each line (” John Smith”) can lead to export failure. 2. Special characters such as & or % may be causing the problem. Look to see if your rows have something like that in common. I hope this helps. Addendum added Mar 18, 2009 from reader Jill Baird: “The maximum number of tasks that can be imported from Excel seems to be 65,536 (if your Excel file has no headers), depending on PC memory. Excel 2007 contains 1,048,576 rows in a sheet, but my Project 2007 only imports .xls files (not Office 2007 .xlsx files), and xls files can only contain 65,536 Excel rows. Importing additional columns should take more memory, and I’m guessing importing data that goes to different tables, such as text, numbers, flags, etc.adds to the memory consumption. BTW: Importing Excel data into a new file, rather than into an existing open file also works a lot faster.”  

Ask the Expert: Custom Reports in Microsoft Project

Reggie Brown asks, what are some methods to produce custom reports with Microsoft Project 2003 and 2007 data? Answer: Both versions of Microsoft Project allow various ways of creating custom reports, whereby Project 2007 offers a brand new feature called “Visual Reports.” I won’t cover all reporting capabilities available within Project by utilizing the Group and Filter functionality. I will cover the ways of creating an actual report in a different format to Project. Here are some ways of producing a custom report using either Project 2003 or 2007. The Reports Feature The reports feature allows users to create pre-defined and custom reports. You’ll be able to find the report link in Project 2003 under View | Reports and within Project 2007 under Report | Reports. By choosing the reports link you’ll be prompted with the reports dialog window, as shown in Figure 1. Figure 1: The reports dialog window. Check out the predefined reports, which are already very good. Additionally, you’re able to create your own reports using this function. The Analysis Feature The Analysis feature allows you to copy a picture (such as a Gantt chart) into a Microsoft Word, PowerPoint or Visio file. Additionally Project 2003 gives you the ability to analyse time-scaled data in Excel (a feature replaced with the visual reports in Project 2007). These functions allow you to extract data or pictures easily for usage with other Office files for presentation or analysis purposes. You can find this feature by right clicking on the Tools bar and choosing “Analysis.” Figure 2: The Analysis feature is tucked under Tools in the menu. Once you’ve chosen “Analysis,” you’ll see the menu bars shown in Figures 3 and 4 in your toolbar. Figure 3: The menu for Project 2003. Figure 4: The menu for Project 2007. The Save As Function Both versions of Project 2003 and 2007 allow you to save your Project file in many different formats. These formats can allow you to create reports based on your Project file. Go to File | Save As and under the “Save as type” choose your preferred file type (for example, Microsoft Excel Pivot Table). Project will guide you through an export wizard allowing you to specify if you want to analyse Tasks, Resources, or Assignments. Based on your selection, you have the ability to create great Excel reports and more. Figures 5a and 5b: The Save As function.   Visual Reports Visual Reports, new to Project 2007, allows you to export Project-related information into either an Excel or Visio Format. This feature has proven invaluable for project managers since its introduction. You can find this feature under Reports | Visual Reports. You’ll be prompted with the Visual Reports dialog window. Figure 6: The Visual Reports dialog window in Project 2007. Project 2007 has pre-defined templates, but also allows users to create their own report templates. This feature, together with Excel 2007 and Visio 2007, has become an efficient way to analyse and report against project progress. My example below represents a “Cash Flow” report, a report most important for many organisations. By selecting the template” Cash flow report” and selecting the “View” button, the visual report will automatically be created based on the great Excel pivot table feature. Excel will open and display the cash flow report based on your Project file. Figures 7a and 7b: A cash flow report and its Excel equivalent.      By using the power of Project and Excel you’ll have the ability to report any data you maintain in your schedule. A Final Note about Project Server 2007 I know that Reggie’s question is more about Project, but it’s important to remember that Project Server 2007 allows organisations to easily report project and program data by using the 14 OLAP cubes (pivot tables) that come out of the box and a fully dedicated Reporting Database. This dynamic duo allows organisations to create any reports you require or just plain desire.