Creating SharePoint Reports with Microsoft Office Excel 2003/07

Microsoft Office Excel 2007 has the ability to sync with EPM Live SharePoint workspaces.  By leveraging Microsoft Office Excel 2007 as a reporting tool you have the ability to create several different dynamic reports.  (Click here to see a short video on how to create reports with Microsoft Office Excel 2007)

1.  Open Microsoft Office Excel 2007

2.  From your SharePoint site navigate to the list that you want to create your report from.  (Images indicate Project Center list.)

3.  Choose the view with the data that you want to report on.

4.  Click the “Actions” button and choose the “Export to Spreadsheet” option.

5.  Click “Open”

6.  Click “Enable” when the security notice window pops up.

7.  Choose the type of chart/table or both that you would like to create and click “OK”.

8.  Chose the data that you want to report on by checking the box next to it.

9.  You can now close the PivotChart Filter Pane and the Pivot Table Field List unless you want to set up a filter.

10. Right click on the chart itself if you want to change the chart type.

11.  Choose your chart type and click “OK”

Note:  You can save your report locally or back to your SharePoint site.

An Update Status Indicator for Microsoft Project

A simple but effective “Update Status Indicator” can be generated using the excellent Custom Fields features available in Microsoft Project 2000 and later versions.

When you need to revise a large project plan, how do you identify those tasks that need updating? By constructing the custom field I describe below, you’ll get red, blue and green indicators that automatically appear against tasks. They indicate, respectively, tasks that should have been completed, that should have been started and that are in progress.

Related Files: To download the Microsoft Project MPP file related to this article, click here.

To generate this custom field:

1. Select Tools/Customize/Fields from the menu.
2. In the Customize Fields dialog box select field Type “Number” and the select a Number field from the list (I used Number20).

3. Click the Rename… button and enter “Update Status” to rename the field.
4. A warning appears that any existing data in the Update Status field will be deleted. Click OK.
5. Click the Formula button and enter the following formula in the Formula for Update Status dialog box:

IIf([Summary]=Yes,0,IIf([% Complete]=100,0,
IIf(([Finish]<=[Status Date] And [% Complete]<100),3,
IIf(([Start]<=[Status Date] And [% Complete]=0),2,
IIf(([Finish]>[Status Date] And [% Complete]>0),1,
IIf([Start]>[Status Date],0,0))))))

6. Click OK.
7. A warning appears that any existing data in the Update Status field will be deleted. Click OK.
8. Under “Calculation for task and group summary rows” click Use Formula.
9. Click the Graphical Indicators button.
10. In the Graphical Indicators for Update Status dialog box enter the following:

Test  “equals” 1.00, Image green button
Test  “equals” 2.00, Image blue button
Test  “equals” 1.00, Image red button

11. Click OK, then click OK in the Customize Fields dialog box.
12. The customized “Update Status” field is now ready and can be included in the table for your view. Enter the Status Date in the Project Information dialog box (Project/Project Information…).

Here’s how the customized field works. The formula first checks to see whether the task is a summary task, a completed task or has a Start date later than the Status Date in the Project Information dialog box. If so, the formula returns a value of 0 in the custom field. If not, it checks to see if the Finish date is earlier than the Status Date but not 100% complete. If it is complete, the formula returns a value of 3. If not, the formula checks to see if the Start date is before the Status Date but is still 0% complete. If so, a value of 2 is returned. If not, the formula checks for tasks started but not due to finish until after the Status Date, in which case a value of 1 is returned. The values 1, 2 and 3 are then displayed as the appropriate green, blue and red indicators.

Educated at the University College of North Wales, Bangor, Mike Theaker graduated with a BSc in electronic engineering. He spent three years in the Engineering Division of the BBC before moving to Pilkington Optronics, first as a project engineer and later as a project manager developing opto-electronic equipment. In 1992 he founded Mike’s Project Service, a consultancy dedicated to providing project management and planning consultancy and training. Contact Mike at mike@mps.u-net.com.

Artcle provide by MPA – http://www.mympa.org

How to configure Project Publisher for use with a Proxy Server

Many of you folks have requested that EPM Live support the use of a Proxy Server when using Project Publisher.  We have developed this functionality into our product but will wait until our next major release to release this to the general community; however, if you have a need for this right now, contact EPM Live support and we will provide you with a hotfix for this issue.

Here is how you configure your Project Publisher to use a Proxy:

1.  From your Project Publisher menu, select Proxy Settings from the Help menu.

 2.  Select “Use Proxy Server” and enter your Proxy Server settings.  If your Proxy Server required authentication, be sure to enter the Username and Password under the “Authentication” section.

 

 Now you should be able to publish and receive updates from SharePoint!

Viewing Task Status in the Current Schedule

Have you ever heard the line, if it weren’t for the last minute, nothing would get done? This refers to the tendency for procrastinators and those managing multiple priorities to put off work until it’s due. For some people, there’s nothing like seeing a task status of “Late” to drive home the need to complete the assigned work — especially if their work is on the critical path for the project.

Scheduling tools such as Microsoft Project typically allow the saving of baseline schedules with a current schedule that’s dynamically updated by applying actual and remaining work. So it’s handy to know if a given task is late compared to the baseline or the current schedule.

I’ve heard plenty of reasons — some more dubious than others — for not saving or adhering to a baseline, including these:

• If I don’t save a baseline, then I won’t be held accountable for not completing my project on time or on budget!
• I can’t control estimates from resources over which I as project manager have little or no control, such as external vendors.
• There are many unknowns regarding the project scope. Projects depending on new technology or those with fickle clients and/or minimal sponsor support are good examples of this.
• No one is asking for a baseline, so why should I provide one?

It’s also possible that a particular project is low priority and is being performed around other more important work and that the organization has agreed that this project will take a “back seat” to the more significant efforts.

In some cases scope trumps schedule. The finish date isn’t as significant as completing the total scope of the work. This rationale is often combined with the low priority project described above.

Finally, sometimes the scheduler doesn’t have the skills or training required to properly save and maintain a project baseline.

Whatever the reason for not involving the schedule baseline, there are situations where comparing the task to the current schedule (vs. the baseline) is desired. This article explores the tools available in Project to review the task status as compared to the current schedule.

The Importance of Task Status

One of the primary concerns of the project manager during execution is keeping the project on schedule. In order to do this, you must identify those tasks that aren’t getting completed on time, especially those that are on the critical path. Given a dynamic schedule and regular task status updates (Project Server timesheets can help automate this), Project can identify these tasks for you in the current schedule.

For the purposes of tracking, a task is one of the following statuses based on the current date (or the Status Date, if it is set in the Project Information function):

• Future. The task is scheduled in the future and no work has begun on the task.
• In Progress. The task has had Actual Work applied to it or the start date has passed. There are two possibilities for this status:
  

  On Schedule — Based on the time-phased scheduling data calculated by Project, the number of work hours scheduled to be completed by the current date (or Status Date) has been met or exceeded by the assigned resources.

  Late — The number of work hours scheduled to be completed by the current date (or Status Date, if set) hasn’t been met by the assigned resources.

• Completed – The task is 100% complete based on the Percent Complete field.

If the Status Date for a project is set, then it will override the current date, which is the default for determining the status of the tasks. To set the Status Date for a project, use the following procedure in Project:

1. Pull down the Project menu and select Project Information. This will open the Project Information dialog box in Figure 1.

2. In the Status Date field, enter the date for which you want the task status to be displayed. To clear the Status Date, enter “NA”.

3. Click the OK button to close the Project Information dialog box.

Viewing Task Status in the Datasheet

Two fields are available in task-level views of Project to identify which of the above statuses apply to individual tasks: Status and Status Indicator. Both fields show the same information in different forms as shown in Table 1. Either of these read-only fields can be added to task-level views (such as the Gantt Chart view) using the following procedure:

1. Right-click on the heading of any column and select Insert Column. This will open the Column Definition dialog box as shown in Figure 2.

2. Pull down the Field name list and select Status or Status Indicator.

3. Click the OK button to insert the field.

The status field has the advantage of allowing the use of the Autofilter function on the column; the Status Indicator column doesn’t allow the use of this feature. To use Autofilter for the status field:

1. Click the icon on the Formatting toolbar. This will display drop-down selection arrows for each column within the datasheet.

2. Click the drop-down arrow on the Status field that you inserted in the previous procedure and select “Late”. This will display all of the incomplete tasks in your current schedule whose start date has passed and the number of work hours scheduled to be completed by the current date (or Status Date, if set) has not been met by the assigned resources.

3. Tasks that are on the critical path are especially sensitive to being late, since they have the potential to delay the project end date or other hard constraints in the schedule. To see the late critical tasks, pull down the Filter selection list (also on the Formatting menu) and select Critical.

4. To remove all filters instantly, press the F3 key.

Viewing Task Status in the Gantt Chart

Like many features in Project, there’s overlapping and/or equivalent functionality in the Gantt Chart since it’s designed to serve as a “sanity check” to information displayed in the datasheet. For task status in the current view, progress lines represent this equivalent purpose.

Progress lines are a graphical representation of whether a task is on, ahead, or behind schedule. It shows as a vertical line in the Gantt Chart from either the current date or the Status Date, if it is set. This vertical line will touch only completed or in-progress tasks; no line will touch future tasks.

Progress lines angled to the left indicate late tasks.

To display progress lines on your Gantt Chart view in Project for the current schedule:

1. Pull down the Tools menu and select Tracking, and Progress Lines. This will display the Progress Lines dialog box as shown in Figure 3.

2. Select the Always display current progress line checkbox.

3. Select whether you want to display the progress line at the project status date or the current date. If the project status date isn’t set in the Project Information function, then the current date will be used.

4. Click the OK button. If the status date was selected in step 3 but wasn’t defined in the Project Information function, the message box in Figure 4 will display. Click the OK button to close the message box under this circumstance.

Figure 5 shows how progress lines appear on the Gantt Chart view.

As you can see, there are many more options on the Progress Lines dialog box that are beyond the scope of this article, including:

• The ability to display progress lines at recurring intervals and on specific dates.
• The ability to display progress lines in relation to the saved project baseline.
• The ability to format the progress lines on the Line Styles tab of the dialog box.

Experiment with these functions to discover the options that best help you communicate schedule progress to your stakeholders.

Kelly Jones, PMP, is the PMO Administrator for the Information Systems department at Sharp HealthCare in San Diego. He is a former Senior Program Manager and has an information systems background that includes systems consulting, programming, database management, project management and resource management. In his current position, Kelly provides training and operational support to IS project managers and resource managers and administers a Project Server system. Contact him at kelly.l.jones@sharp.com.

Article provided MPA  – http://www.mympa.org/

Excel vs. Project: How to Convince Managers that Project Really is Better for Managing Projects

How often have you seen a manager struggle to use Microsoft Office Excel to manage project information such as scheduling project dates or recording task assignments? Often, it’s a matter of sticking with what they know — and every manager in the world seems to know Excel. In this article, I’ll supply you with the arguments you need to help them understand when it’s time to shift to using Microsoft Office Project instead.

When it comes to managing project information such as scope (phases, tasks, milestones and deliverables), timelines (schedules and deadlines) and resources (assignments, effort, availability and costs), Project provides better built-in capabilities than Excel. Once the user is over the hump of learning Project, using Excel to manage project information doesn’t make sense since Project is set up to increase productivity and effectiveness.

Key challenges in using Excel in project management work include:

• Project and summary level data calculation is largely done manually.
• Creating and maintaining phases, tasks and milestones is more difficult.
• Creating and maintaining project timelines and deadlines isn’t dynamic or easy.
• Assigning resources and costs to tasks isn’t automated nor project-specific.
• There are no automatic updates for scope, time or resource assignments.
• There are no specific tools to manage change effectively.
• It’s difficult to see the ripple effect of delays.
• There’s no feature that calculates the impact of resource availability.
• There are no pre-defined project-specific reports for different stakeholders.

Building a Project Schedule

Project enables managers to not only create a schedule but to see a project through the lifecycle of planning, tracking and reporting. Also, if a user has a plan in Excel, importing it to Project is simplified by using the Project step-by-step guides to map Excel columns to project columns. It’s then possible to convert the task list from Excel into phases, tasks and milestones which defines the project scope known as the work breakdown structure (WBS). Project has the tools the user needs to quickly set-up and maintain the WBS. The next step is to establish a timeline by defining task durations and dependencies or links. Completing the schedule involves identifying and allocating resources. Once complete, the task relationships can be represented in the form of graphs or reports. Information can then be easily shared. Project allows the user to:

• Quickly set up project phases, tasks and milestones.
• Define task durations and relationships.
• Clearly identify and allocate resources and coordinate workload.
• Quickly access and share relevant project information.
• Effortlessly create custom reports to keep the project team informed and aligned.

With Project it’s also possible to:

• Import existing data from Excel or Outlook.
• Initiate a plan using the built-in project templates.
• Collapse or expand plans to show the necessary level of detail.
• Click and drag to change task duration.
• Keep notes on important task information.
• Do “what if” analysis with multiple undo’s.
• Focus on tasks that are driving project completion and critical path.
• Determine the factors that are affecting task dates and easily trace the source of issues.

In addition to importing Excel files, Project offers users two options to create or start a new project. First, project schedules can be created from templates. In addition to Project’s built-in templates and those available at Office Online, custom templates can be built. Using templates saves time and ensures consistency and standardization across an organization.

The second approach is to build a plan from scratch using the powerful planning tools Project has to offer. Building dynamic and effective plans in Project involves these five steps:

1. Enter WBS (phases, tasks and milestones) into task name column.
2. Outline (indent/outdent) tasks to reflect the WBS hierarchy (major, minor and task pieces).
3. Estimate duration (or work-for-work based schedules).
4. Set dependencies or task links to establish the flow of activities.
5. Assign resources (and costs if applicable).

Unlike in Excel, any task with zero duration in Project appears as a milestone automatically. A milestone is a reference point marking a major event in a project, used to monitor the project’s progress. By default, milestones appear as diamonds on the Gantt chart, as shown in Figure 2 above, at the end of each phase (summary task). There are many different models and methods for estimating duration, but none is entirely accurate. In Excel, it would be necessary to build formulas to see a project’s total duration or the total duration per phase or major pieces. Using Project, a user can establish a base calendar and estimate each task by entering its duration in the task column. In turn, Project will total the project and phase duration without having to build in formulas.

In Project it’s possible to easily create task dependencies, which are also referred to as activity relationships or task connections. Types of dependencies, as shown in Figure 3, include sequential (finish to start), concurrent and overlapping. Once the task relationships are established in conjunction with duration estimates, Project automatically calculates your schedule. Furthermore it establishes a dynamic schedule that can be recalculated automatically as you modify your plan.

One extraordinary new tool in Project is “Show Change Highlighting,” which will highlight the impact of making a change throughout the entire plan so you can decide whether to keep or undo the change. It will also calculate the critical path, which can be easily viewed through filters, highlights, groups or formatting. In Excel this would involve many separate and complicated calculations to determine the overall schedule of a critical path.

What is a critical path? It’s the set of tasks that affects the overall end date. In any single-project network, one sequence of tasks fixes the duration of the project. Any slippage or failure to begin a task on time that is on the critical path will cause the project to finish later than planned. The critical path is illustrated in Figure 4 below using the red highlights and the red Gantt bars.

Project allows users to manage cost and resources by defining local project resources and the respective related information in a separate view or window. In turn, the Project Resource Assignment Tool is used to add resources to tasks, including multiple resources to a single task or multiple resources to multiple tasks. This is a cumbersome process in Excel involving creating and linking worksheets.

Communicating from the Project Schedule

Project has capabilities and features beyond those available in Excel. Those features can be used to present project data to different stakeholders according to their requirements. It’s easy to keep stakeholders informed using these Project capabilities:

• Using appropriate views to keep different stakeholders informed.
• Controlling the information to present with predefined tables.
• Focusing on the specific information using filters.
• Grouping information for effective communication and decision making.
• Highlighting cells to convey a different meaning.
• Analyzing and reporting project data in professional reports and charts.

Using Excel, it would take numerous steps to generate the views that are a click away in Project. There are many built-in views in Project and it’s possible to create custom views as well. Some of the views include:

Gantt Chart. Shows activity/task start and end dates as well as expected durations. The Gantt chart is the default display and principle report format in Project.

The Network View. Shows the task boxes. Where nested, they indicate the hierarchy of tasks to clarify dependencies between tasks.

The Calendar View. Displays project tasks in a calendar format. A task bar representing a task spans the days or weeks on which the task is scheduled.

Project also includes many built-in resource views. The resource usage view shows the tasks and their timing per resource. The resource histograms show resource utilization data. It would be possible but laborious to create these views in Excel.

The Visual Reports feature leverages charts and diagrams by using Excel and Microsoft Office Visio Professional to produce charts, graphs and diagrams based on Project data. A user can define custom report templates or choose from a list of customizable, ready-to-use report templates. Reports can be shared with other Project users. Tables and filters can be applied to further control the data that is communicated to different stakeholders. Filters provide an easy way to zero in and focus on a set of tasks for more effective communication. Instead of filtering tasks or in conjunction with filtering tasks, grouping tasks makes it easy to reorganize information to enhance your ability to analyze or communicate information. Project has a set of predefined views, tables, filters, groups and reports specifically designed for project managers to become more productive immediately without having to spend extra time formatting or customizing — which is exactly what a user would need to do using Excel.

Tracking and Analyzing the Project Schedule

Project can also be used to track and analyze progress of projects. Within Project, once an optimized schedule has been approved, it’s possible to save a copy of the original plan by setting a baseline. In Excel, this would require manually populating additional baseline columns. With Project, once the baseline is set, the project constraints are managed by comparing the evolving plan with the saved baseline. Managers can then track progress, identify problems and take corrective action using the built-in tools already in Project.

For example, a project manager can easily update a task using the Update Tasks dialog box. The results can then be analyzed in the tracking Gantt view which is already customized for tracking purposes. This view shows both baseline Gantt bars in gray and the current plan bars in red for critical path task and in blue for non-critical. These tools are presented in Project so new users can take advantage of them quickly and easily while providing advanced users the ability to go deeper when necessary. In comparison to Excel, no such tools exist to manage project information in this way, and it would be quite laborious to customize and format it with no guarantee of success.

Gaining a Work Edge

Understanding when to shift to Microsoft Office Project and using the right tool gives those put in charge of managing a project a distinct advantage. Managing project information such as scope (phases, tasks and milestones), time (schedules, deadlines and critical path), resources (assignments, effort, costs) and the need to communicate that information effectively are all indicators of when to use Project. This advantage continues throughout a project life cycle as Project makes it easy not just to plan, but to communicate, track and analyze project data. Project is the best tool to help manage projects because it’s specifically designed for this purpose with tools that offer immediate gains in productivity.

Keith Wilson is an executive and senior consultant with the Project Management Practice Inc. Keith’s background includes 23 years of successful management and consulting experience, with a focus that includes project management, training, marketing, strategic alliances and business planning across many industries. Reach him at kwilson@pmpractice.com.

Article provided MPA  – http://www.mympa.org/

New Project Publisher Features

Project Publisher connects Microsoft Office Project and Windows SharePoint Services 3.0 (WSS) or Microsoft Office SharePoint Server 2007 (MOSS) to make working together on projects easier. By publishing Microsoft Office Project .mpp files to SharePoint, all team members can access and update their own tasks and view key project information.

Below are the latest Project Publisher features:

Capture Project Attribute Information

When custom Project Center fields (columns) are created in SharePoint and a Project is Published to that Project Center, the user publishing the Project will be prompted to fill out the custom Project Center information in order to sort, group and filter projects in the Project Center.

Publish any Microsoft Project Field

Choose which Microsoft Project fields you want to publish to SharePoint. Publishing a field will automatically create the column in the SharePoint Project Center or Task Center list and populate that column with information from Microsoft Project.

Choose Editable Fields

Choose which Task Center and Project Center fields are editable in SharePoint.

Upload Microsoft Project Files to SharePoint

Use the Attach Project Schedule feature to attach an .mpp file to its associated SharePoint Project Center list item. This allows the Project Manager to share schedule information with the entire project team

Publish Summary Tasks

Publish Summary Tasks from your Microsoft Project Schedule to SharePoint allowing the entire schedule to be viewed through SharePoint.

Delete Project Information that was published to SharePoint

Now there is an easier way to remove or delete Projects from SharePoint. Use the Delete Project feature in Project Publisher to delete a Project and its associated Tasks and Resources from a SharePoint site

How to get the latest Project Publisher version

By default Project Publisher is set to look for new verison updates every time you open Microsoft Project.  If this default option is still turned on, you simply have to open Microsoft Project and it will inform you when a new update is available for download. 

You can select or deselect this option by choosing the Help –> About menu option from the Project Publisher menu.

To manually check for new Project Publisher updates, choose Help –> Check for Updates from the Project Publisher menu.

How to deploy the new EPM Live Features in your site

EPM Live has released several “features” that, when deployed, allow you to create new SharePoint Lists or Document Libraries on your site.  For example, when you deploy the Action Items feature, you can then create new Action Items lists in your SharePoint Site Collection.

 Here is how to deploy these features in your Site Collection:

1.  From your Site Collection (top level site), select the “Site Settings” link from the “Site Actions” menu in the upper right corner.

2.  Under the “Site Collection Administration” section, select “Site Collection Features”

3.  Click the “Activate” button for each EPM Live Feature that you would like to be active in your Site Collection.

4.  Select the “Site Settings” link from the “Site Actions” menu in the upper right corner.

5.  Under the “Galleries” section, choose “List Templates”

6.  You should now see a list of the EPM Live Features that you activated in step 3.  Click the edit icon next to the feature.

 

7.  Provide a Title and Description for the feature and click the “OK” button.

 

8.  Now, if you click the “Create” link from the “Site Actions” menu, you will see your Feature in the list of available templates.  Keep in mind that depending on the type of Feature, the Feature will show up under different sections on the create page.

New Project Publisher custom fields feature provides users with much more flexibility

When we first release Project Publisher serveral months ago, we only supported updating or bringing back Percent Complete into Microsoft Office Project from SharePoint.  So, the only task update scenario was for Team Members to update Percent Complete in SharePoint and then the Project Managers would pull the updated Percent Complete back into Microsoft Project.  This scenario worked very well for several of our customers, however, several more customers suggested that we support some additional update methods beyond just Percent Complete.

You asked for it, we delivered!

We now support 4 different update methods.

1.  Percent Complete

2.  Actual Work

3.  Remaining Work

4.  Actual Work and Remaining Work

This means that using the Project Publisher custom fields feature, you can configure your SharePoint Task Center list to support the updating of the above fields.  Due to the various calculation methods within Microsoft Project, you can only choose to use one of the four methods above.  Project Publisher has also been configured to only allow the configuration of only one of the above methods.

How to you set your environment up to support one of these methods?

By default, the edit screen for tasks looks like this:

 So, by default you can see that task update method #1 is supported.  Let’s say though that you want to have your team members update Actual Work instead of Percent Complete.  Here is what you need to do:

1.  Open the Microsoft Project that was published to the workspace.

2.  From the Project Publisher menu, choose “Project Options” and then “Custom Fields”.

 

3.  Select the Task Center tab

4.  Highlight Percent Complete in the SharePoint Fields list on the right and then deselect the “Make Editable Field in SharePoint” checkbox.  By doing this, you are specifying that you do not want Percent Complete to show up on the Edit screen in SharePoint.  So, Percent Complete will not be editable by team members.

 

5.  We now want to make Actual Work editable in SharePoint.  Highlight Actual Work in the SharePoint Fields list and select the “Make Editable Field in SharePoint” checkbox.

 

6.  Click the “Save” button when finished.

7.  Now, go back into SharePoint and edit one of your tasks.  You will see that instead of Percent Complete being editable, now, Actual Work is editable.

 

 You can use the steps above to configure your SharePoint environment to use the other task update methods as well.

Release Notes

The following are the relase notes for Project Publisher Build: 1.0.2950.31004 that was released on 2/11/08 at approximately 3:00pm PST

- Bug fixes to address task update issues

- Message box alerts when you remove a custom field from the Project Publisher SharePoint field list

- Message box alerts when you attempt to configure your editable custom fields in an unsupported manner

- Fix to address the updating of over 1,000 tasks from SharePoint.  Now, the Project Publisher will automatically update tasks from SharePoint in sets of 500 at a time.