I’m thrilled to announce the release of the following two white papers which provide step by step guidance on surfacing key Project Portfolio Management data into insightful reports and dashboards:

• Microsoft Project Server 2010 Reporting with Excel Services written by Jean-Francois LeSaux and Steven Haden from Microsoft Consulting Services.
• Creating Dashboards for Microsoft Project Server 2010 written Blaise Novakovic, Jean-Francois LeSaux, Steven Haden, Microsoft Consulting Services

For additional information on Microsoft Project Server 2010 business intelligence & reporting please check out Business Intelligence in Project Server 2010.

Microsoft Project Server 2010 Reporting with Excel Services, 71 pages

Creating Dashboards for Microsoft Project Server 2010, 115 pages

In MS Project you have the ability to add notes to each tasks in a project plan. This data is stored as an Image Data Type in the SQL database called TASK_RTF_NOTES (note that the field TASK_NOTES is a nvarchar of size 255, thus you’ll only get the first 255 characters displayed). So how can you render the entire note field using SQL Server Reporting Services? Before giving you the answer a special thank you to Sam Brooks from Microsoft for passing along this solution:

(SQL Reporting Services code sample attached at the bottom of this post)

T-SQL Query

SELECT ProjectName,TaskName,TRTF.TASK_RTF_NOTES
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P
ON P.ProjectUID=T.ProjectUID
INNER JOIN PWA_Published.dbo.MSP_TASKS AS TRTF
ON TRTF.TASK_UID=T.TaskUID
WHERE TaskIsProjectSummary=0
AND P.ProjectName='Task RTF' --- For debugging purposes REMOVE!!!!
ORDER BY P.ProjectName, T.TaskName

Note that the TASK_RTF_NOTES is not in the Project Server Reporting database, thus we need to get it from the Published database.

Report Code

• Add a reference to System.Windows.Forms (Report –> Report Properties –> References)

• Add the following function in the report’s code (Report –> Report Properties –> Code):

‘Instantiate a rich text box control in memory
Public rtfRTB As new System.Windows.Forms.RichTextBox

‘Instantiate a stringbuilder object
Public s As New System.Text.StringBuilder()
Public Function byteArrayToString(ByVal b() As Byte) As String
Dim i As Integer
dim mystr as string

on error goto errortrap

s.length = 0
For i = 0 To b.Length - 1
Console.WriteLine(chr(b(i)))
If i <> b.Length - 1 Then
s.Append(chr(b(i)))
End If
Next
mystr = left(s.ToString, len(s.ToString)-1)

rtfRTB.rtf = mystr
return rtfRTB.text
rtfRTB.clear

exit function
errortrap:
return "n/a"
rtfRTB.clear
s.length = 0
End Function

• For the TASK_RTF_NOTES field use the following Value:

=code.byteArrayToString(Fields!TASK_RTF_NOTES.Value)

And voila! 

Another way to do the same thing is to use custom assemblies, check this blog post from Bryant Likes for more information: http://blogs.sqlxml.org/bryantlikes/pages/824.aspx

I just came accross this great tool VhdResizer that will achieve the following:

VhdResize will resize Microsoft's VHD files and will also convert between Fixed and Dynamic file types. This is a sector by sector copy operation from one size/type to the other and the source file remains unaltered.

FYI I have just completed the install of Project Server 2007 + Service Pack 1 (and Office Server 2007 + SP1 as well) using Windows Server 2008 Release Candidate 1 (RC1) from MSDN.

I used this two great posts from Bill Baer to complete the install:

• Installing Windows SharePoint Services 3.0 on Windows Server 2008
• Slipstream, not a phenomenon in Physics

Two screen captures to prove it (check bottom right corner :)

Expect more posts in the future about the advantages of running PS 2007 on W2K8 (IIS7, hypervisor-based virtualization ...).

In the meantime for more information about Windows Server 2008, please check the official Microsoft site here: http://www.microsoft.com/windowsserver2008/default.mspx

Last week we released Microsoft Dynamics AX 2009, and with it comes an out of the box integration with Project Server 2007.

Both products address a common scenario: Line Of Business Integration (LOBI) between an ERP system and Project Server; in this case no need for custom code (check out the LOBI session code sample Brian Smith and I did at TechEd Developer last week in Orlando)!

• Press Release: http://www.microsoft.com/Presspass/press/2008/jun08/06-02DynamicsAX2009GAPR.mspx
• Dynamics Website: http://www.microsoft.com/dynamics/ax/default.mspx
• Editions (http://www.microsoft.com/dynamics/AX/product/editionsandfeatures.mspx)

Following this post (New SQL Reporting Services Sample Reports for Project Server), please find below the list of Excel Services (samples attached) reports included in the newly released demo VPC.

If you want to learn more about Excel Services (requires SharePoint Server) two new great books have been published on the subject:

• Wrox Press: Beginning Excel Services
• Wrox Press: Professional Excel Services

Business Alignment Analysis		Governance Phase Analysis
Investment Analysis		Resource Capacity
Resource Capacity By Role		Project Work Heat Map By Role

Prepping for my TechEd presentations in less than two weeks (Microsoft Project Presence at Tech.Ed North America 2010), I worked on a presentation to demo how easy it is to upgrade from Microsoft Office Project Server 2007 to Microsoft Project Server 2010 (come to Rolly and I’s session entitled Deploying and Upgrading to Microsoft Project Server 2010. I used the five sample databases (yes five: one SharePoint content, and four PWA instance databases) that we built for our core EPM 2007 demo virtual machine (VM) (about 550MB of bak files). I backed up all five dbs from my 2007 farm/demo VM and created an ISO with the five databases and two magic scripts (one to restore the dbs in T-SQL and one PowerShell script to upgrade the databases (basically two commands: Mount-SPContentDatabase and New-SPProjectWebInstance), ready to mount to my new 2010 demo VM.

And guess what… on my demo laptop (yes with 8GB RAM, 4 cores, and dual SSD), I had the Project Server 2010 and SharePoint Server 2010 demo virtual machine up and running; in 10min my PWA 2007 instance got upgraded to 2010!!! Yes you heard me, barely enough time for a coffee. Want a live demo come to New Orleans and see it yourself!

More seriously used the RESTORE DATABASE T-SQL command which took 30s; then ran the following two PowerShell commands (content db upgrade took about 2min, and rest to upgrade Project Server 2007 four dbs and provision PWA):

Mount-SPContentDatabase -Name Litware_Content -WebApplication Project

New-SPProjectWebInstance -Url http://project.contoso.com/litware -AdminAccount "CONTOSO\Administrator" -PrimaryDbserver "DEMO2010A" -PublishedDbname "Litware_Published" -ArchiveDbname "Litware_Archive" -DraftDbname "Litware_Draft" -ReportingDbserver "DEMO2010A" -ReportingDbname "Litware_Reporting"

As usual a lot more detailed information on TechNet to conduct an upgrade or migration: Upgrade and Migration for Project Server 2010 (this article in particular: Database-attach full upgrade to Project Server 2010). Yes there is a lot more process/training/customization etc… to upgrade to 2010 but at least on the data front its super easy!

See the upgrade site below (notice the dashboard quick links from the EPM 2007 PPM demo VM). Happy upgrade!

Following this SharePoint 2010 announcement today: Announcing SharePoint Server 2010 Preliminary System Requirements please note the following requirements for Project Server 2010

• Project Server 2010 will be 64-bit only
• Project Server 2010 will require 64-bit Windows Server 2008 or 64-bit Windows Server 2008 R2
• Project Server 2010 will require 64-bit SQL 2005 or 2008
• Project Server 2010 will only support Internet Explorer 7 or 8 (IE 6 will not be supported nor other browsers)

Please find below two recently published article on TechNet to help you migrate your existing Project Server 2007 farm to 64-bit:

• Advantages of 64-bit hardware and software (Project Server 2007)   This articles describes the advantages of running your Microsoft Office Project Server 2007 farm on a 64-bit Windows server environment.
• Migrate an existing server farm to a 64-bit environment (Project Server 2007)   This article provides instructions and recommendations for a clean, phased migration of a Office Project Server 2007 farm to servers in a 64-bit environment.

Finally please encourage your customers and partners to attend our upcoming Microsoft Project Conference 2009 in September to learn about Project 2010.

Thanks to Sharry please find below some useful steps when troubleshooting EPM 2007 queue issues:

1.     Use Manage Queue page (Sever Settings -> Queue -> Manage Queue) to look at correlations (use the CorrelationUID column for help here) to see why a certain correlation is blocked.  Usually if your queue is still working for other jobs/entities, then restarting the queue is not necessary.  If you cannot see any problems and your queue is still working, then your filters on the Manage Queue page are not right – check them.  Restarting the Queue is only necessary when nothing is processing.  Using the “By Project” filter works nicely for looking at the queue job history of projects.  For other correlations, use CorrelationUID.

2.     Look first for Failed and Blocking states – those are the jobs that are “blocking” others on the same correlation (again, use the correlation UID here to see what jobs are affected).  You can either retry these jobs if the error looks like something having to do with something recoverable (like loss of network or database connection), or you can cancel.  Canceling with the default settings will cancel the entire correlation, so make sure you know what data you could be losing by doing so.

3.     Are jobs stuck in the “Getting Enqueued” state?  If so, WinProj needs to be opened again on that user’s machine who submitted the job to see if WinProj will continue sending the project.  If that doesn’t work, then you will need to cancel the jobs in this “getting enqueued” state.  Note that this effectively means that the save from WinProj never happened, and that data will need to be resaved again.  This is the same thing that happens when you just blindly restart the queue.  But at least doing it this way means that you know what is being lost.

4.     Look at the error (click the link in the Error column) to get an idea about why the failure occurred.  Sometimes you can correct the problem and re-save/re-submit your job.

5.     Start comparing Event Logs to what you’ve found on the Manage Queue page.  Look for errors around the same time as failed jobs in the queue.

6.     SharePoint Logs (usually located here: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS ) .  Same technique as #5 – look for errors around the same time as failed jobs in the queue.

With the recent release of Microsoft Project Server 2010 (PS) last May and its rapid adoption by customers, a recurring question I have seen and been asked is how can I integrate Project Server 2010 with application X? Where application X can be a combination of the following:

The following Microsoft Project Server data type/entities are typically synched:

And there are more I have seen or heard but basically they revolve around the above application types. So how do I interface PS with these other mission critical applications (also referred to as Line Of Business Integration or LOBI) within my organization? This is obviously a very broad topic and this blog post is by no means exhaustive but let me try to get you started in answering this question.

Well as you can guess IT DEPENDS! The biggest challenge and the most complex part of such integration is clearly defining the scenario, documenting what data will be transferred between the two systems, what’s the “master” and what’s the “slave”, exception handling (if an update is rejected what happens for instance? If a task is deleted what happens? etc.)

The second phase is implementing the bridge and for that as mentioned in this recent post: Microsoft Project Server 2010 Integration with SAP you have two options: either use an existing partner solution that is publically available (search here for instance: http://www.microsoft.com/project/en/us/partners.aspx or BING it) or you can build your own connector and in that case look at the Microsoft Project 2010 Software Development Kit.

Points to consider when integrating PS with LOB (by no mean exhaustive):

Useful links:

• Project 2010 SDK
• Project Developer Forum
• Microsoft Project Partners
• Import foProject (a very powerful solutions to integrate PS with multiple external systems directly within PWA)