Christophe Fiessinger's Blog Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings
(August 20, 2009 update, check out this Second Report Pack for Project Server 2007)
We have released last week an updated version of our EPM 2007 demo VPC. Please find below the SQL Server Reporting Services (SSRS) reports used in that VPC.
All these SSRS reports are using the Project Server 2007 Reporting database as a data source.
Please note the Earned Value Trend reports will require a custom stored procedure (included in the attachment).
This pack includes the following reports:
To deploy the Project List and Project Detail reports check the following post: Fields required to run Project List and Project Detail Reports; for the Timesheet Compliance report check this post:How to deploy the Timesheet Compliance Report
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)
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P
INNER JOIN PWA_Published.dbo.MSP_TASKS AS TRTF
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.
‘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
If i <> b.Length - 1 Then
mystr = left(s.ToString, len(s.ToString)-1)
rtfRTB.rtf = mystr
s.length = 0
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
WSS V3 out of the box has extensive logging activity, Logs can be usually found in this directory and tend to grow significantly over time: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS
If you are running WSS V3 in a developement environement or in a VPC and want to optimize resources, you can disable logging activity by doing the following:
Please remember that once you have disabled logging it will be impossible to troubleshoot any PS nor WSS errors. This is not a recommended setting for any production system!
<Update July 2010> Check out this recently released VM: Microsoft® Team Foundation Server® 2010 and Microsoft Project Server® 2010 Integration CTP Hyper-V Virtual Machine to learn about server to server integration http://blogs.msdn.com/b/chrisfie/archive/2010/07/06/microsoft-project-server-and-team-foundation-server-2010-ctp-virtual-machine.aspx </Update July 2010>
Following the recent launch of Microsoft Project Professional/Standard and Project Server 2010 this week (along with Office, SharePoint and Visio) I wanted to provide a brief overview of Microsoft Project 2010 integration with Visual Studio Team System 2010 Team Foundation Server (TFS). First I recommend that you all read the 143 pages Microsoft Project Server 2010 Product Guide, it will give a great overview of what’s in 2010 (including this topic) and hence why you should consider it as your solution of choice for Project Portfolio Management.
Basically two scenarios are supported with TFS 2010 and Microsoft Project 2010.
First there is a “lightweight” scenario which enables a Project Manager to connect his/her plan to TFS using the desktop ribbon menu entitled “Team” (picture below) - a project manager can simply sync with Project Server 2010 to better connect the worlds of project and portfolio management and application development.
Additional information can be found on MSDN: Scheduling Tasks and Assigning Resources Using Microsoft Project
Secondly the Project engineering team and the Visual Studio TFS team are working on an out of the box server to server integration (no need for a CodePlex solution to integrate both)- providing direct connectivity between Project Server 2010 and Visual Studio Team Foundation Server 2010. Expect more information on this topic in the future.
Steps you will need to follow to display PWA Web Parts in another site within the same SharePoint farm:
1. Chose the Web Part (e.g. TimesheetPart) from the PWA homepage or the web parts gallery (Project Web Access > Site Settings > Galleries > Web Parts) and Export and Save to your preferred desktop location:
2. Edit the Web Part in Notepad. Find the following property PSIURL (or add if it does not exist) and change it accordingly for instance:
<property name="PsiUrl" type="string"> http://server/PWA</property>
3. Save Web Part update
4. From http://server/sites/project - Site Collection EPM > Web Part Gallery > Upload Document, select TimesheetPart modified earlier.
5. Add Web Part to the page you like (Site Action -> Edit Page)
To cross SharePoint server farms, you can use the Page Viewer web part in conjunction with SimpleUI=31
If you encounter the following while adding the Web Part to the page : “Unable to add selected web part(s). My Timesheet: The request failed with HTTP status 401: Unauthorized” check the ULS logs. It’s typically an access right issue so make sure the user has access to the PWA site.
Project Server 2007 Web Parts
Following the release of this sample SQL Server Reporting Services 2005 report pack: New SQL Reporting Services Sample Reports for Project Server, I have just updated two sample reports (Risks and Portfolio Status, attached below) to illustrate the use of the Gauge which comes with SQL Server Reporting Services 2008. I used SQL Server 2008 Reporting Services Report Builder 2.0 to create the reports in with a few clicks; creating custom reports has never been easier!
Time to add Gauges to your reports :)
For more information on SQL Server 2008 use the following references: Microsoft SQL Server 2008 Upgrade Technical Reference Guide is Live!
If you run into issues with your SharePoint farm (including Project Server instances), as a farm administrator you might not have access to the folder where the logs are stored (typically under C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS).
To work around this access right you can get the person with the proper administrative rights to setup this Log Viewer feature to your farm: SharePoint 2007 Features
The Log Viewer will render in a webpage the logs stored in the ULS files as shown below:
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:
Import financial information, and WBS
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):
What’s the specific use case?
Interface scalability and performance – was on a recent call with a partner who realized their interface to another system did not took too long to process updates…
What PS data entities (Project, Task, Resource, Lookup Table) needs to be updated and transferred? How is the custom data mapping defined?
Interface logging and monitoring - would expect any solution whether its of the shelve or custom made to provide visibility of data updates so that it can be easily monitored and maintained
What are the additional Custom Fields required in PS to enable the transfer?
Please find attached a Visio 2007 diagram that shows the standard data flow for time tracking and reporting progress in EPM 2007. Might be useful if you are doing customization using the Tied-Mode solution starter released last week on CodePlex.
[Update 11/30/2010 – another great reason to deploy it in a single farm is the ability to integrate Project Server web parts in other location within a farm, see this documentation on TechNet for more info. Plan for Project Server 2010 Web Parts]
Following a number of questions on this topic at Tech.Ed a few weeks ago and this recent post from Joel Oleson: Project Server 2010 and SharePoint 2010 Coexistence please find below my humble opinion on this question: Should Project Server 2010 be deployed in a standalone Farm or should it be deployed in an existing SharePoint Farm?
First lets start with an overview of the two deployment scenarios:
As a reminder please find the version compatibility below (see recent Tech.Ed presentation below); in a nutshell you cannot mix 2007/2010 version of Project Server and SharePoint:
So while mix scenarios are not supported, it is possible to go from 1 to 2 (Together to Apart) or the reverse from 2 to 1 as shown below
So back to the initial question, which scenario to choose (again there are plenty more pros and cons I have put together in these slides listed below), well it depends as listed below, but I would recommend a single farm for the following reasons:
Why Together (#1) in no particular order:
While with the 2007 version most customer deployment I saw was about a 50/50 split between 1 and 2 (I did talk to a few customers that did not realized what they lost from a functional point of view by separating the farms and were looking for non-supported workaround); in 2010 with the tighter integration with SharePoint 2010 (the Business Intelligence/Reporting capabilities used by PWA or the Demand Management/Workflow capabilities to name a few) and the fact that organization want a consolidated infrastructure for Collaboration (whether its document management, performance management or project management) I would strongly recommend option 1. To follow up on Joel’s arguments (yes this is a constructive argument and Joel and I know one another!):
So again think of the functional, technical, maintenance, licensing implication of deploying Project Server and SharePoint Server together or apart, thanks to Joel for starting the debate , in the end the great news whether you decide to go with 1 or 2 is that you can always reverse the decision as mentioned before. As with all my post feel free to send feedback and share your opinion. Happy Project Server 2010 and SharePoint 2010 deployment!
Resources on this subject: