Christophe Fiessinger's Blog
Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings

  • Christophe Fiessinger's Blog

    New SQL Reporting Services Sample Reports for Project Server

    (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:

    Earned Value Trend 196 2008-04-10, 09_56_40 Project List 204 2008-04-10, 10_02_00
    Issues   200 2008-04-10, 09_58_42 Risks 199 2008-04-10, 09_58_23
    Portfolio Status 197 2008-04-10, 09_57_34 Organization Status 201 2008-04-10, 10_00_01
    Project Detail 203 2008-04-10, 10_01_12 Timesheet Compliance 205 2008-04-10, 10_02_52

    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

  • Christophe Fiessinger's Blog

    How to display Microsoft Project Task’s Notes field in a 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)

    T-SQL Query

    FROM MSP_EpmTask_UserView AS T
    INNER JOIN MSP_EpmProject_UserView AS P
    ON P.ProjectUID=T.ProjectUID
    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
    If i <> b.Length - 1 Then
    End If
    mystr = left(s.ToString, len(s.ToString)-1)
    rtfRTB.rtf = mystr
    return rtfRTB.text
    exit function
    return "n/a"
    s.length = 0
    End Function
    • For the TASK_RTF_NOTES field use the following 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:
  • Christophe Fiessinger's Blog

    How to disable logging in Windows Sharepoint Services V3

    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:

    • Go to WSS V3 Central Administration -> Operations -> Logging and Reporting
    • Click on Diagnostic Logging
      1. Customer Experience Improvement Program: select No, I don't wish to participate
      2. Error Reports: select Ignore errors and don't collect information.
      3. Trace log: clear the entire Path.
      4. Click OK
    • Click on Usage Analysis Processing
      1. Uncheck Enable logging
      2. Click OK
    • Click on Information Management Policy Usage Reports
      1. Uncheck Enable recurring policy usage reports

    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!

    • Christophe Fiessinger's Blog

      Project 2010 and Team Foundation Server 2010 Integration

      <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 </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.

      Project and TFS Integration

      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.


    • Christophe Fiessinger's Blog

      Adding an EPM Web Part to another SharePoint site

      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

      ·         MySchedule

      ·         ResourceAssignmentsPart

      ·         OWCViewPart

      ·         ResourceCenterPart

      ·         ProjectCenterPart

      ·         StatusApprovalsPart

      ·         ProjectDrillDownPart

      ·         TaskPart

      ·         ProjectWorkspacesPart

      ·         TeamAssignmentsPart

      ·         QueueStatusPart

      ·         TimesheetPart

      ·         RemindersPart



    • Christophe Fiessinger's Blog

      Microsoft SQL Server Reporting Services 2008 – Sample Project Server Reports

      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 :)

      Gauge Type
      Sample gauge types available with SSRS08
      Notice the two gauge types to the right of the regular IMG (red, yellow or green dot) indicator that came with the SSRS05 report
      On the left is the initial design, the gauge is actually a static image; on the right a dynamic gauge with the true value (pointer on the left is fixed since the image is static :))

      For more information on SQL Server 2008 use the following references: Microsoft SQL Server 2008 Upgrade Technical Reference Guide is Live!

    • Christophe Fiessinger's Blog

      Analyzing Unified Logging Service (ULS) Logs using SharePoint’s Central Admin

      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:

      ULS Viewer

    • Christophe Fiessinger's Blog

      Integrating Project Server 2010 with Line Of Business Applications

      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:

      Application Type Typical Scenario Application Example
      Accounting (ERP)

      Import financial information, and WBS

      SAP R/3, MSFT Dynamics SL and AX, Oracle etc.
      Billing/Invoicing (ERP) Export PS timesheet information to generate invoices, a classic in IT charge back and or service organizations SAP R/3, MSFT Dynamics SL and AX
      Human Resources Import resources information like skills, RBS, billing rate, etc.… SAP or custom systems
      Customer Relationship Management (CRM) Transfer customer service work information to PS MSFT Dynamics CRM, Siebel etc.
      Ticketing, Maintenance, Keep the Light on Import work performed in these other app. into PS via Statusing API or push project plan and assignments to another app. to track the execution System Center Service Manager, Remedy etc.…
      Time tracking, time sheeting Import data into PS to have full view of resources execution SAP CATS, etc.
      Application Lifecycle Management (ALM) Transfer tasks and assignment to a team of developers MSFT Team Foundation Server, etc.
      Project Portfolio Management (PPM) Yes I have seen customer bring PPM data from competitors product in our stack due to the ease of use of our platform and the enhance collaboration and reporting capabilities with SharePoint 2010 IT PPM competitors

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

      Project Server Data Type/Entities Description
      Projects Projects can be tied to portfolios, programs, WBS, etc.
      Tasks Tasks are associated to WBS, work items, work package etc.
      Resources Resources typically are synched from Active Directory and additional information like skills/role come from another system etc.
      Task’s Status A common scenario where task updates are made in another system and needs to be transferred to PS the ChangeXml API
      Timesheets Timesheet data is typically transferred out of PS to another system for billing, etc.
      Lookup Tables Import LT data definition from another system.

      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: 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

      File format required to transfer data 9XML, CSV etc.)? Can validation rules and business rules be easily created and modified?

      What are the additional Custom Fields required in PS to enable the transfer?

      Are there existing partner solutions available or do I need to build a custom interface?
      Reporting requirements for this interface? KPI, dashboards etc.  

      Useful links:

    • Christophe Fiessinger's Blog

      Timesheeting and Statusing Data Flow Diagram

      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.

      Timesheeting & Statusing data flow

    • Christophe Fiessinger's Blog

      Project Server 2010 and SharePoint 2010: one farm or multiple?

      [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:

      1. Together/Coexistence - Single farm with both Project Server and SharePoint Server 2010
      2. Apart/Standalone - Dedicated Project Server Farm running SharePoint Server 2010

      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:

      image image

      Why Together (#1) in no particular order:

      • Single Infrastructure: You can leverage the same infrastructure you have put in place for your farm, for instance lets say you have architected the farm for high availability on all tiers (redundancy/multiple servers at the Web Front End and Application tiers, and a SQL cluster for storage), why buy another set of hardware for a Project Web App (PWA) Farm and duplicate resources? Similarly why not apply software update, cumulative updates at the same time, why duplicate efforts across farms?
      • Content Management: If you have two separate farm all the SharePoint content generated during the usage of Project Server (document artifacts etc…) cannot be stored in your main content management farm, hence you will duplicate your Enterprise Content Management efforts (governance, digital asset management, record management, etc…), similarly you cannot integrate PWA web parts across farms (we will publish an article this month on what PWA web parts can be integrate in different sites/pages of a Farm)…
      • Project Server is a SharePoint App: yes it is and yes it’s not free! but since 2007 we have been build on SharePoint so again why a separate farm. As an anecdote since we are built SharePoint if you deploy Project Server in a standalone farm (scenario 2) you are effectively deploying a second SP Farm in your organization… The 2010 version has gone a long way in terms of scalability, stability/quality so why not deploy it in the same intranet farm according to documented best practices and as usual monitor it so you are always pro-active if issue arises or if the PWA is becoming resource constrained. So why start putting every Service App/Web App in separate Farms, isn't the power the consolidation from a IT and functional point of view?

      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!):

      • Licensing – one could argue that it could cost more to have two farms, if for instance you have redundant servers in both farms you will need to purchase more SharePoint Server licenses for instance. Additionally while its important to focus on acquisition cost, a more important vision is to look at Return On Investment, Total Cost of Ownership and measure the true value of a single/multiple farm over their lifespan. Again you will loose value and incur additional maintenance cost by going with two separate farms as mentioned above.
      • Performance – well yes potentially but if you Farm has been architected properly by SP experts and if it’s monitored according to best practices then performance should not be an issue, typically the bottle neck at the end of the day is the database tier (SQL servers: CPU/RAM and disk I/O)
      • Patching – yes but again there are two sides to this, you can argue that since Project Server Cumulative update are part of the SharePoint Server CU why not patch all at once instead of duplicate efforts?

      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 Smile, 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:

    Page 1 of 69 (689 items) 12345»