Blogroll

Month: April 2011

Mark Everett, out of our Dallas office, sent me an e-mail the other day asking me why I haven’t posted anything about SharePoint 2010’s new calendar overlay functionality. Honestly, I hadn’t yet posted anything on the topic since it was the first I had heard that it even existed.

…so I did some digging, and here you are…..

The Overview

So it turns out that SharePoint 2010 has a new built in feature to aggregate up to 10 calendars into a single color-coded view. Apparently it’s a well known feature, but I apparently missed the memo.

This is probably a pretty useful feature for PMOs or program managers who are looking for ways to aggregate multiple project calendars.

If you’re not using project calendars, I point out that many of my clients actually use them quite heavily by e-mail enabling the calendar. Once that’s done, the project calendar may be “invited” to project meetings just like a real person. Any meeting invite will automatically get posted to the calendar, which in effect, works much like a project journal.

This functionality puts all of the project meetings into a single calendar – useful when your PMO maintains visibility into all of the key project meetings.

The Set Up

It’s actually surprisingly easy to create a new aggregated calendar. Just create a new calendar list within your SharePoint site. In this example, I will create one in the main PWA site.

Click on the options to View All Site Content under the Site Actions menu in the top left.

Select the calendar option.

Once the calendar has been created. Choose the option in the Calendar tab to add a Calendar Overlay.

Choose the option for a new calendar.

Configure the next screen to point to the target calendar. Choose a color, and you’re off to the races.

Note that you may change the default view to filter on specific meeting types such as status meetings or key phase gate meetings.

This question has come up in multiple engagements, and I suppose will continue to come up…..how can you spell check Project Detail Pages? This has been a perennial issue with SharePoint, and only gets to be more of an issue now that we have multiline text fields available in Project Server 2010.

I am sure that there are complicated technical solutions out there….but for now, I might recommend downloading and installing the ieSpell application. It’s a lightweight add-in that installs into Internet Explorer, and spell checks text in forms on the page – including as I confirmed this week, the Project Data webparts on the PDP page. Free for personal use, but requires license for commercial use. (Note the licensing restrictions.)

In my previous post, I discussed some of the mechanics of using the Bulk Import tool to either import data into Project Server or to perform a batch edit of existing project data. In this post, I wanted to expand on that topic by walking through the procedures that seemed to work for me when performing both of those functions.

I would strongly recommend reading both posts before attempting to manipulate data in a production environment.

Importing Legacy Data Into Project Server

The import process begins with the data in Excel. While your legacy data may reside natively in a SharePoint list, my guess is that the data must be scrubbed to match the new requirements of a Project Server environment, and therefore will be exported to Excel for analysis and modification prior to beginning this process. If your data resides in another data repository, then that only increases the chances that you will have to export to Excel. Hence, we start with Excel.

Review date fields to confirm that they appear to be dates and not text entries.

Confirm the owner field matches the user name as displayed in Project Server exactly.

Confirm that all outline fields and lookup fields match the lookup tables exactly – and that outline fields only include the lowest level entry – not the entire string of the outline hierarchy (see the previous post for more information).

I found it helpful to add a unique identifier column on the left hand side of the data set that included both text and numbers, i.e. PROJ01, PROJ02, etc. When importing, this allowed me to identify exactly which projects were causing issues – and allowed me to skip one step in the SharePoint list modification prior to import.

2) Import the list into SharePoint.

At this point, you may wish to make a strategic decision about the basic process that you intend to follow. As I mentioned in the last post, the import process only seems to work on a specific number of projects. At various times, I was able to import projects in batches of either 200 or 300. I am not sure what drove that, and maybe it was actually 200 all along, but it seemed to me that 200 seemed to be a safe limit. You may either create multiple SharePoint lists of no more than 190-199 projects – or you may opt to create a single list, import, remove the items that successfully imported, and then reimport the remainder. I personally found the latter process to be a little less time consuming – as it naturally relegated all of the exception projects with issues to the last batch. The first approach is equally valid however. Choose one.

You also may only select one Enterprise Project Type per batch import. You may wish to split out your various EPTs into different lists for ease of import.

Within PWA, select View All Site Content, choose the option to create a list, and then click on the option to Import an Excel Spreadsheet. This will import the source data to SharePoint.

3) Review the List Settings

Navigate to the list settings. Review any multiline text fields (such as Description), and convert the field from the default rich text to plain text.

Confirm that the fields imported properly. A date field shouldn’t import as a multiline text field for instance.

Modify the view and confirm that the Project Name field is displayed in a form that is not linked to the actual item. If it is linked, it will not be available for mapping during the import process.

4) Create a Project Server View of the imported data set

You now should go to Project Center within PWA, and if you haven’t already, create a view of the imported data. I usually add “TEMP” as a prefix to this view so if anyone actually sees it, they will know it’s not a real view.

I’ll configure this view to only show the fields being imported, and perhaps to include any relevant filters.

5) Run the import process.

Click on the button to import from SharePoint and review the field mapping. If any fields don’t map automatically, I would go back into the Excel file and change the column header. Chances are that I messed up the column header, for instance including “Costs” instead of “Cost” or “Name” instead of “Project Name.”

Validate that all of the fields from the Excel sheet and the SharePoint list actually show up to be imported. For instance, if the Project Name field was the leftmost column on import, then the field was imported as the item title. The default SharePoint view will include that column as a link field, and it won’t be available to import into Project Server. If this is the case, go back into the SharePoint list view settings and add the unlinked Project Name column back into the view to make it available for the import.

The import tool will display a summary of the project import status after the import has concluded *if* the total number of projects being imported fall within a specific parameter. I am not sure what this parameter is exactly, but the last time I did this, it seemed to be about 200 projects. If you are trying to import more than 200 projects, the green circle will stop spinning, and your queue will stop – signifying that the import process has concluded. Simply navigate to your Project Center view and confirm the projects were imported. If you are importing less than 200 projects, the tool will display a list of project status at the end of the import process. Projects appear to be imported in the order in which they are displayed in the SharePoint list.

Check the Force Check In option to confirm that all projects have been checked in.

6) Validate the Data Import

The easiest way to do this is to navigate to your Project Center view, refresh it, and then export the view to Excel. Once in Excel, you can use formulas such as vlookup, match, or other comparisons to confirm that the data in fact migrated.

If you’re importing less than 200 projects, you may wish to make edits to the SharePoint list, and then run the import process one by one for the exception projects.

If you’re importing more than 200 projects, I would match the project names in the exported Project Center list to my source data, and then remove any items that had imported successfully. The remaining projects get saved in another Excel file (Master Projects 2), and the process is repeated.

7) Clean Up Project Server

To clean up the import process once you’re confident the data looks good, go into the import list and make sure it doesn’t appear on the Quick Launch bar.

Go to Site Actions > View All Site Content and delete any of the temporary lists you had to create to pull the data into Project Server. Note that those lists will go to the site Recycle Bin and get deleted permanently as per your configured policies.

Modifying Existing Data

To modify existing data, we more or less follow the same process as above with a couple of minor tweaks:

1) Prepare Project Center View

As above, create a Project Center View that contains the projects and fields to be edited.

Export this view to Excel.

2) Modify the Data

Make the changes to the data in Excel. Do not change the project names. These names will allow us to map the data changes back into the Project Server data.

When importing the Excel sheet back into the SharePoint list ensure that the project name is the leftmost column. This will import the project name as the item name.

3) Import the Changes

Run the bulk import tool. Map only the fields you’re changing. You do not need to map the Project Name. The changes will be implemented in the Project Server data. If you are editing more than 200 projects, wait until the green circles stop spinning and the queue shows no more activity. If editing less than 200 projects, wait until the report appears at the end to show which projects were successful and which needed some modification before reimporting. Note that there is no need to select an EPT type.

Repeat as necessary.

4) Clean Up Project Server

Review the Force Check In interface to confirm all projects have been checked in.

The Bulk Import Tool is one of the superstars of the solution starters that were released onto Codeplex to augment the native functionality of Project Server 2010. Again and again, I have been forced to use this tool – and to support clients who are trying to use this tool. This post represents the first in a two part series based on my experiences trying to understand how the tool works, and how it can be best used to support an EPM implementation.

This has been a difficult post for me to write. Honestly, I think I’ve written and rewritten this post about three times. This has been one of those times where every time I think I’ve figured out exactly how the tool works, I’ve then been proven wrong. As of this writing, I think that I can at least predict how the tool works with reasonable precision.

In the end, I realized that I probably had too much content for a single post, and thus am splitting this into two. Today’s post will focus on the mechanics of the tool, how to get it installed, and an overview of what works and what doesn’t seem to work. Tomorrow’s post will focus more on recommended procedures and how to use the tool to both import project data and to perform bulk updates of existing project data.

Note that the following observations were made after using the tool in three distinct environments with different configurations. I was using the version in the December 2010 release.

The Introduction

Why would you use this? Well, for a couple of reasons. If you’re just now implementing Project Server 2010, then there’s a good chance that your organization has a list of projects lying around that must be entered into the system. The list doesn’t have to be in SharePoint. Any old spreadsheet or Access database will suffice. As long as you can get the data into a spreadsheet, you’re pretty much good to go.

The other reason you might use this technique is to update the project level metadata on existing projects. You can use the tool to effectively import specific fields into the Project Center views. This kind of conflicts with another solution starter, the Bulk Field Edit tool, which will be a topic for another post. For now, let’s just say that I am still trying to understand the rules upon which the Bulk Edit Tool works, and am far from making it behave predictably.

Installing the Tool

The tool installation is pretty straightforward. Simply download the Zip file from Codeplex. Unzip the directory, and copy the Bulk Import Tool folder to the desktop of your server.

Open the Deploy Powershell file in Notepad. Change the Site URL to the correct URL for your Project Server instance.

Right click on the Deploy Powershell file, and select the option to Run As Administrator.

Confirm that you get the following message.

Navigate to the Project Center to confirm the new button appears on the Ribbon.

Prepare the Data

Careful preparation will ensure that the import process occurs smoothly. Unless your processes require otherwise, my general recommendation would be to export any list to Excel, clean the data in Excel, and then create a custom list based on the spreadsheet. This will make your life a lot easier.

In my real life experience, this part was complicated by the fact that there’s some weird issue with SharePoint 2010 which means some site collections don’t allow you to create a list by importing a spreadsheet. Some site collections do. The first site collection caused the following error: Method ‘Post’ of object ‘IOWSPostData’ Failed. There are fixes for this if you simply do a search on the Web.

That had me sweating for a while, until I realized that I could use a quick and dirty workaround of just creating the list in a datasheet view, then copying and pasting the data from my spreadsheet. The second site collection worked just fine. If you’re importing a large number of projects (in my case, about 1,000), your experience will be much smoother if you find a site collection that actually works, or can simply fix the issue.

The other thing that will make your life easier is to understand exactly what data types will import using this tool, and what data types do not import. I did a rough test with almost each of the SharePoint field types with the following results.

Note that much of the content below applies to SharePoint lists. If you’re using Excel to clean the data and simply importing into SharePoint for the purpose of staging your data for the tool, you may not have to worry about such things as calculated fields, etc. Calculated Excel fields will simply import into SharePoint as text or number fields.

Text, Single Line – imports to text fields, no problem.

Text, Multiple Line – imports to multiline text fields, no problem – provided that the field has been set to plain text within SharePoint. When importing from Excel, the default setting is a rich text field – which will not import into Project Server. I’ll discuss that in more detail tomorrow.

Text, Multiple Line (w/ Append) – imports the most recent entry to multiline text fields. Does not import historical records. See the note above with regards to plain text vs. rich text.

Choice field – imports into text fields, no problem.

Number field – imports into number fields, no problem.

Number field (percentage) – imports into number fields as a decimal.

Currency – imports into an available currency field. Note that the import process will convert the currency symbol to whatever the default Project Server currency is. I.e. a SharePoint field denominated in Chinese Yuan will import into Project Server as US Dollars.

Date – imports into date fields. There’s a weird quirk (which could have been my virtual environment) that dates get appended with a 5:00 AM start time. So 3/21/2011 gets imported as 3/21/2011 5:00 AM (if you’re displaying time, which few people do for project level fields). This may be annoying if you end up importing SharePoint date fields into Project Server text fields.

Date and Time – imports into date fields. This too demonstrated that weird quirk where the import seemed to add 5 hours to the imported time. For the most part, that’s probably not an issue. 3/21/2011 6:30 AM becomes 3/21/2011 11:30 AM. However, in some cases, that will flip the date to the next day, i.e. 3/21/2011 10:00 PM imports as 3/22/2011 3:00 AM. I didn’t spend much time troubleshooting this, but keep it in mind if your dates seem to change randomly.

Lookup Single Item – imports provided the lookup table in Project Server has exactly the same values. I’ve noticed when exporting from SharePoint to Excel however that Lookup fields do get a bit garbled – so you may take that in consideration if you’re using SharePoint columns that reference other lists.

Lookup Multiple Item – imports provided the lookup table in Project Server has exactly the same values. Note also the caveat above.

Yes or No – Doesn’t import. Period. Doesn’t even give you the option of importing this field. (The workaround for this was to add a text field to the list, populate it with “Yes” or “No” values, copy this into a temporary Project Server field, then use the Bulk Edit Tool to filter on the temporary field and populate the actual Project Server flag field.)

Person or Group – Looks like it should import, but doesn’t. Probably because it contains the “\” character.

Hyperlink – Does not appear to import.

Calculated field – Imports only as a text field. Even if you calculate a number value, this will only offer you the option of mapping it to a text field.

Note that Outline fields function a bit differently than you might expect. For instance if you have an outline in Project Server configured as 1.Red, 1.Blue, 2.Purple, and 2.Mauve, you do not want the source data to include each level of the outline. If you try to import “1.Red,” the tool will not import the value and leave the cell blank. If you import “Red,” the value will get imported and mapped to the outline structure appropriately. I am not sure how the system will behave if you have identical multiple lower level values such as 1.Red and 2.Red. My guess is that you would want to change that temporarily to 1.Red and 2.Red2, import the data, then change the lookup table back to 2.Red. As the project values are tied to the lookup table GUID, the change should cascade through existing projects. As usual, you should test that in a test environment before deploying in production.

A couple more notes:

The Owner field is a key part of the Project Server security configuration. This field will import provided that the list entry matches the AD name displayed in Project Server exactly. Do not try to include the actual AD login account name, as this won’t work, and the tool will balk at importing the “/” that is part of the AD. So, import “Scott Smith” and not “AD/SSmith.” If the system cannot recognize the owner name, the field will default to the account of whomever is actually performing the import. For this reason, you may want to confirm that you are logged in as the service account for the import. I’d also recommend considering opening up the Project Manager:My Organization permissions immediately after importing so project managers can change the ownership of mislabeled projects to themselves. Once that’s been sorted out, ratchet security permissions back down.

The Project Name field will throw an error on special characters. As far as I can tell, the only characters that won’t throw an error are the dash or parentheses. You’ll have to remove periods, quotation marks, colons, semi-colons, and commas from project names prior to importing them. Also remove any leading or trailing spaces in the project name. Duplicate project names will throw an error, so you may want to confirm that this will not be an issue.

After assessing which fields will or will not import into Project Server using this tool, confirm that your enterprise custom fields and lookup tables have been created within Project Server. You may wish to turn off any required fields as they may cause the import process to fail.

Next, if importing a large number of fields, modify the column names to match the Project Server field names exactly:

The tool will automatically map the fields provided the names match.

Automatic mapping won’t work for required fields as they show up in the tool as with the “*” prefix, i.e. *Project Departments.

The tool won’t automatically map an imported Project Departments Field to the *Project Departments field in Project Server – but allows you to manually map these fields.

At this point, you should also pay attention to project names, and ensure that project names do not include any “strange” characters such as “&” or “/.” If you’ve used Excel to clean the data, go ahead and create a new custom list using the option to import the spreadsheet.

Now the real fun starts.

Import the Data

Before running the tool, you may wish to log into the server using your service or administrator account. Whomever runs the tool ends up being the owner of all of the imported projects where the owner field could not be resolved, and aesthetically, it probably looks better to have 1,000 projects assigned to a service account than to your own name.

Let’s run the import tool. From the Project Center, click on the button in the Project Center and add the URL of the source list. Click the Validate button.

You’ll see the tool will take a stab at mapping the fields that share a name with the target fields. As mentioned above, Yes or No fields do not get the option of importing into Project Server.

Also note that the calculated SharePoint field attempts to map to a text field even though it has been set within SharePoint to be a calculated number. When reviewing the options to map it to, the list only displays text fields as options.

I assign a default EPT, select the option to save settings, and kick off the import process. I had a couple of experiences here at different times. The first time I tried this, I left the Update Existing Projects field unchecked as I was importing projects into a blank environment. Nothing happened. Then I checked the box and ran the import again, and it actually imported projects. I am not sure if that was a consistent experience, but watch your queue after kicking off the import process, and if nothing happens, that could be the culprit.

If everything works well, I get the following confirmation message. Generally, I only got this message when I was importing projects in batches of approximately 300 or less. Trying to exceed that arbitrary 300 project limit often meant that I had to monitor the queue until it looked like the import process had completed, the green dots stopped swirling, and I could click off the import window. I’ll discuss this phenomenon in more detail for my next post.

Also note that after the import tool has been run, you should check the Force Check In option under Server Settings. A couple of times, one or two projects was stuck in checked out mode.

Now let’s review how well that worked…. To do so, I create a custom Project Center view with all of the custom fields except for the multiline fields (which do not appear in the Project Center.) At first glance, everything looks good.

When I scroll over to the right however, it doesn’t look as good.

The following fields failed to import:

Lookup Single and Multiple Item

Yes or No

Person or Group

Hyperlink Custom

Calculated

Created By

Modified By

On closer examination, I realized that my lookup table listed Task 1-10, but should have read Test 1-10. I reimport, and get the following results:

So the lookup fields do import when the lookup table values match exactly.

What about the multiline fields? I build a quick and dirty Excel report to see if those came over:

Looks like everything worked there – with the possible exception that the multiline field with append only picked up the present value and not the previous values. Still, that’s a bit better than I was expecting in all honesty.

On the other hand, when I actually got into a full blown test migration, I kept running into that arbitrary 300 project limit. I am not entirely sure why that was the case, but I could never get the tool to import more than 300 projects from the same list. I tried multiple views, different sorting schemes, and different filters, different item limits within the list view, but 90% of the time, the tool kept importing the same projects (or updating the same projects) over and over again. The only technique that ended up working for me was essentially the following process:

Import all 1,000 projects into a SharePoint list.

Import all 1,000 projects into Project Server. Only about 300 import.

Identify the remaining projects. Import these 700 projects into a second SharePoint list.

Import those 700 projects into Project Server. Only about 300 import.

Repeat as required.

After importing, note that the entire mapping schema has been saved to a newly created list. You may wish to go into the List Settings for this list to toggle it off of the Quick Launch bar.

To reimport from the same list with the same mapping, simply select that option the next time you run the Bulk Import Tool.

Post-Import Steps

After importing the projects, you may wish to go through the following steps to clean up your data:

Validate that all of your projects indeed imported. To do this, I would use the Project Center Export to Excel option and compare the results to my source data.

Restore any Project Server enterprise custom fields that must be set to required.

Take the Bulk Import Tool configuration list off of the Quick Launch bar lest it confuse your end users.

Review the imported projects for additional data that must be added.

Identify the appropriate ownership for the imported projects, and set them accordingly. The Bulk Edit Tool is useful for this.

Set any flag fields as appropriate and remove any temporary fields added to facilitate this process. Again, the Bulk Edit Tool is useful for this.

…and that’s it for the basics. With my next post, I plan to walk through the import process more from a procedural standpoint and less from a mechanical standpoint. I also plan to address how to use the Bulk Import Tool to perform bulk edits of existing project data.

This is an event I’ve been eagerly anticipating. ‘tis a pity that I won’t be able to make it.

Anyway, if you’re an aficionado of public speaking, you’re familiar with TED….and if you live in the Houston area, you should definitely try to make it out to this all-day celebration of content and content delivery.

Becoming a Project Server admin is tough. You have to simultaneously become an expert in project management methodology, SharePoint, Project Server, Business Intelligence….it’s undoubtedly quite a challenge. It’s a bit easier if you have the resources of corporate IT to rely upon, but there’s no guarantee that outside of the usual realm of SharePoint skills, they’re ready to support you in configuring and managing Project Server.

When I first started managing Project Server implementations, I put together a short document that I could leave with newly minted administrators – basically a cheat sheet to available resources. A couple of years ago, I realized that this sort of document was best off as a blog post, which I could then point my clients to. This represents the latest version of that content, refreshed for the 2010 release.

Please see below for my recommendations for getting up to speed on administering Project Server 2010…

Recommended Reading List

There are a lot of good books available these days and even more material available online. If you have an unlimited budget, I’d recommend buying every book under Project 2010 on Amazon. If you don’t have an unlimited budget, I’d recommend the following at a minimum – and not just for your shelf, but read through them.

Read the Blogs

There’s a lot of good information being produced in the blogosphere these days. If you haven’t gotten into the habit of subscribing via RSS to a selection of blogs, you really need to. I have been using Omea Jetbrains for years, but any number of tools are out there for both PC, cloud and mobile OS.

Many of the bloggers are also active on Twitter. If you monitor Twitter, watch for the following hash tags: #PMOT, #MSProject and #ProjectServer. If you are not active on Twitter, feel free to check out this automatic service that pulls the latest relevant Tweets each week.

Monitor The Newsgroups

The Technet newsgroups are the best resource of free Project-related information on the Web. Questions posted are typically answered in less than 24 hours by volunteers from around the globe – including yourself if you so choose. Start your day with a cup of coffee and a quick perusal of the latest postings.

There’s an ongoing debate about whether or not the forums are downloadable for offline viewing and posting. The latest tool for doing so is the NNTP Community Bridge, but whether or not it is supported seems to change by the week. If it is working, you can download the posts each day to Outlook Express or another tool of your choice (like Jetbrains.)

As Project Server is married so closely to SharePoint, you may also have to keep the SharePoint forums on your favorites list:

Note however that you should ensure that any questions posted to the SharePoint forums are strictly SharePoint related. Posts mentioning Project Server in any context often get transferred back to the Project Server forums.

You may also add the Technet search engine to your IE search box to speed up your search for information.

In addition to the newsgroups, some of the LinkedIn Groups are also gaining in popularity. Take a look at LinkedIn, but some of the active groups I’ve identified include:

Collaboration and Project Management

Forecast Scheduling (Based on the popular Microsoft Project text of the same name)

Join the User Groups/Local Communities

Attend (or at least get on the mailing list) of your local SharePoint User Group – most cities have one. Watch for relevant sessions and networking sessions.

Engage your local chapter of the Project Management Institute. The monthly sessions may not be specifically pertinent to Project Server, but you’re guaranteed to run into other folks attempting to implement the same thing. Chapters often offer subsidized Microsoft Project or Project Server related classes.

In case you missed the announcement, try to attend the 2012 Project Conference which was announced only a couple of weeks ago.

Build a Virtual Environment

Nothing replaces having a full virtual environment to play with, and to break if necessary. Work with your local technical resources to provision a Hyper-V environment on your local laptop, or failing that, on an available development server. Despite the dire warnings, you don’t really need top of the line hardware to run a virtual sandbox. As far as I can tell, pretty much any machine with 8GB of RAM and a 64 Bit OS will suffice. Note that you need Windows Server 2008 to run Hyper V, but if you’re stuck with Windows 7, could probably get by with the Sun Virtual Box using these instructions from Rolly Perreaux.

If you’re comfortable doing so, go ahead and build your own environment with a Technet Professional subscription. The Administration book referenced above will walk you through the process.

Get An Operating Framework

Since you will be the Project Server service desk, or will be interfacing with your corporate service desk, it behooves you to get up to speed on best practices in keeping the lights on and in managing changes. There’re a couple of flavors out there, but the ideal scenario would be to get ITIL Foundations Certified or up to speed on the Microsoft Operations Framework. I can’t speak to MOF, but the ITIL Foundations class is easily accessible and quite beneficial to non-techies.

…and that’s my list. Out of curiosity, I Tweeted a request out to the Twitterverse, and some last minute useful additions to this list include:

I am pleased to announce that a second white paper of mine has been published on Technet. If you’re looking for an overview of Project Server reporting options in a convenient package, then go ahead and download the paper from the following link:

Here’s the table from page 9, a quick overview of the different reporting tools…

Tool

Description

When to Use

Microsoft Project Professional

The desktop client version of Microsoft Project provides a wealth of reporting tools – Timelines, Visual Reports, Reports, Export to Excel, Export to Access, and the ability to copy specific screenshots.

Use these tools when reporting is performed on a single project basis. Typically, these tools are used by the project manager to respond to ad hoc informational requests or to participate in a defined manual status reporting process.

Project Center Views

The default dashboard feature within Project Server, the Project Center allows for the rollup and graphical depiction of key metrics from within projects.

Use Project Center views when the organization has defined specific metrics that may be tracked in projects using enterprise fields. Project Center views should generally only be used for reporting when the organization has defined metrics, and enforces a strict project update process to ensure that the reported data is valid.

Excel & Excel Services

Excel provides an intuitive and familiar report authoring tool to the information consumers of the organization. Excel Services allows the reports to be published in a secure fashion for consumption by other users.

Use this tool when a number of information consumers have spreadsheet authoring skills, and desire the ability to manipulate standard reports to meet specific needs. Excel reports may be used for project or portfolio level reporting. Excel reports may yield minimal returns to organizations that have not begun tracking effort or cost estimates within projects.

Visio & Visio Services

Often underappreciated as a reporting tool, Visio provides an interface for producing unconventional reports with intuitive navigation. Visio Services allow the reports to be published in a secure fashion and be made accessible to users without Visio installed on their local machines.

Use this tool when a simple chart is insufficient for reporting purposes. For example, the report consumers may be looking for a more graphical depiction or navigational structure such as a timeline, geographical or organizational representation of project or portfolio level data.

SQL Server Reporting Services (SSRS)

Traditionally the reporting tool of choice for many organizations, SSRS typically requires skilled individuals to develop custom reports. With the latest offering of SSRS, users may download Report Builder as a tool to develop reports. While this new tool allows users to create reports easily, the interface is still not as intuitive as commonly used desktop reporting software such as Excel or Visio.

Use SSRS reports when there is a need for an automated distribution of reports on a regular basis, for instance a weekly e-mail comprising the status for all projects within a given portfolio. SSRS reports are also useful when attempting to develop reports pulling from both Project Server and SharePoint Server data sources. Generally, SSRS is to be used when the format of the report is static, and users are not expected to make changes other than controlling specific filtering parameters.

PerformancePoint Services

PerformancePoint Services allows users to assemble diverse collections of reporting assets into modular dashboards. The reporting assets may include SSRS reports, Excel reports, and native PerformancePoint reports. Each asset may be used individually, in a single dashboard, or in multiple dashboards.

PerformancePoint Services provides several points of functionality to the Project Server user. Use PerformancePoint Services when various reporting elements are expected to be used and reused as components within various, targeted BI dashboards. Also use PerformancePoint Services to quickly and easily display key metrics from projects in a Web-based interface. Note that these metrics are typically pulled from OLAP cubes, and as such may only be relevant to organizations tracking cost or effort within project schedules.

The REST API

The REST API is a powerful tool for dynamically extracting information from published Excel reports and then embedding that information in web sites, Word, or PowerPoint documents.

Use this tool if the organization requires the routine production of Word or PowerPoint artifacts consuming project or portfolio level data. This tool allows the user to embed dynamic data in Office documents for use in such artifacts as routine status reports or monthly resource reports.

External Content Types

External content types are configurable by using SharePoint Designer, and allow the surfacing of database data directly into SharePoint Server. This potentially makes the data more accessible, and subject to the default SharePoint Server search process.

Use this tool in conjunction with the SharePoint external list feature to surface Project Server data in the form of SharePoint lists. This model may be appropriate to organizations that have grown accustomed to providing key information to stakeholders via SharePoint lists or who need to give users the ability to create and save custom views of project data.

Power Pivot for Excel

PowerPivot for SharePoint

Introduced with the 2010 release, Power Pivot consists of both a SharePoint Server application to generate list data feeds and an Excel add-in. Power Pivot pulls data from SQL Server databases and SharePoint lists and easily aggregates it into a single table. (Power Pivot was deemed out of scope for this document.)

Use this tool when multiple SharePoint lists must be combined with Project Server data to generate a single data set for reporting purposes. For example, use this tool to combine a list containing a project narrative from team members with project data – or if project level metadata has been extended from Project Server into secure SharePoint lists.