Project Server Blogs

Archive for December, 2008

— Next Page »

A New Look for 2009

I would have wished you all a very merry holiday season of your choice – if I had not been without Internet Access for fair portion of the last week.  Snow decided to come and stay a while in the Pacific Northwest.  Fortunately we only lost power over one night – but my neighbors over the valley where our broadband wireless originates were not so lucky – and were out for a few days.  We also lost satellite TV signal in the heaver snow (no great loss I have to admit) and also lost the ability to drive up the hill to our house, which was more of a challenge!  With my injuries from last summer still lingering it was frustrating having to watch my wife shovel snow and not let me help. 

We had to skip Christmas lunch on the patio too.  This was Christmas Eve, and we had more snow for the following 3 days before the thaw started…

With the New Year I decided to go for a new look, and will also start the audio/video blogging shortly.  Expect the first ones to just be testing the process and making sure everything works rather than adding greatly to the sum of human knowledge. 

The picture I currently have as the header on the blog is a sunrise over Eastern ridge of the Snoqualmie Valley, taken back in November.  I’ll change it occasionally as the mood takes me.

Popularity: unranked [?]

Agile Planning Tools In Visual Studio Team System 2010

Check out the following article Agile Planning Tools In Visual Studio Team System 2010 from Ajoy Krishnamoorthy in the latest version of MSDN Magazine; another interesting article in the December edition: Streamline Team Projects With Process Templates.

Related link:

• Visual Studio Team System 2010 and Project Server

Popularity: unranked [?]

Project Server Database Timeout

Hey,

Since it is the end of the year, I am slowly working my way through my Outlook tasks to be able to start fresh in the new year. I seemed to have missed posting a change we made to the PSI in the SP1 Rollup. In the SP1 Rollup, we added two new methods to the Admin PSI. One allows you to read the current database timeout and the second allows you to set it:

• SetDatabaseTimeout(DatabaseTimeoutType.Core, timeout);
• GetDatabaseTimeout(DatabaseTimeoutType.Core);
                    

We added these methods because, in some deployments, the default timeout was not long enough. If your Project Server deployment is experiencing database timeouts, you may want to try changing the default timeout. Attached is sample code on how to use these methods.

Chris Boyd

Popularity: unranked [?]

Getting Started with Project Server 2007 - TechNet Magazine January 2009

Check out the following article from TechNet Magazine (January 2009 edition) from Alan Maddison from Strategic Business Systems:  Getting Started with Project Server 2007 This article is targeted at IT Professionals:

At a Glance:

• Four general areas of enhancement
• Multitier architecture aids scalability and flexibility
• Prerequisites for installation
• Installing and configuring

(…)

As IT administrators, most of us will have used Microsoft Office Project (Standard or Professional) to help streamline and organize at least some of our many projects. However, not many of us have implemented Project Server, due to a lack of familiarity, expertise, or simply the time to tackle one more complex installation and configuration. With Office Project Server 2007, Microsoft has made significant enhancements not only to the features and functionality available for users but also to those that interest administrators.

(…)

Last Words

Project Server 2007 is clearly the most significant release of this product in a long time. Microsoft has invested a lot of time and energy into making Project Server 2007 a marked improvement over earlier versions and if you have not taken a look at it recently, then it is time you did.

Related links

• Inside SharePoint: Enterprise Project Management with SharePoint

Popularity: 7% [?]

How to report Project Risks at a Program Level?

A request I heard a few times from customers is how can you report Risks (or Issues) at a Program Level and since I’m snowed in it’s a perfect opportunity for a new EPM blog post! Since Risks and Issues are stored as lists within SharePoint another way of looking at this problem, is how can one create a list of lists from a SharePoint content database?

For instance lets assume I have the following project hierarchy in Project Web Access:

where Level A is a parent of Level AX and Level AX a parent of Level AXX. The levels could also represent Program/Project/Workpackage. At each level I have projects and for each projects I have a workspace associated with it (following the SharePoint site hierarchy shown above for ABA (add a ‘B’ and start singing!)):

Let’s assume I track Risks at each level and for each project shown above. How can you report all the risks at any level is the question? Remember that Issues and Risks are all tracked and stored in the SharePoint content database associated with the site collection. I hence wanted to build a report by querying the data directly in the SharePoint content database to ensure I have the latest and greatest data (remember that the SharePoint data (Issues/Risks/Deliverables) only flows in the reporting database once a project plan is opened and published). I could have leveraged SharePoint Designer to build a List of Lists by accessing the list webservice  (sample here), but instead of using ASMX I figured it would a lot more efficient to query the database directly.

There were two challenges in writing that query: figuring out the Sharepoint “Risk” T-SQL query and building a recursive function to find all the children “Risks” of each parent. Please find below how I solved these two challenges:

SharePoint “Risk” T-SQL query

I used the following post from Rob Fisch to get started: Reporting on Sharepoint lists from Microsoft SQL Reporting Services. I fired up the query editor in SQL 2008 to leverage Intellisense (I love it!) and started with this first query in the SharePoint content

SELECT TOP 1 tp_Fields FROM    Lists WHERE   Lists.tp_Title = ‘Risks’

<FieldRef Name=”ContentTypeId”/><FieldRef Name=”Title” ColName=”nvarchar1″/>
<FieldRef Name=”_ModerationComments” ColName=”ntext1″/><FieldRef Name=”File_x0020_Type” ColName=”nvarchar2″/>
<FieldRef Name=”Owner” ColName=”int1″ StaticName=”Owner” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”AssignedTo” ColName=”int2″ StaticName=”AssignedTo” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Status” ColName=”nvarchar3″ StaticName=”Status” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Category” ColName=”nvarchar4″ StaticName=”Category” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”DueDate” ColName=”datetime1″ StaticName=”DueDate” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Probability” ColName=”float1″ StaticName=”Probability” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Impact” ColName=”float2″ StaticName=”Impact” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Exposure” ColName=”sql_variant1″ StaticName=”Exposure” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Cost” ColName=”float3″ StaticName=”Cost” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Cost_x0020_Exposure” ColName=”sql_variant2″ StaticName=”Cost_x0020_Exposure” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Description” ColName=”ntext2″ StaticName=”Description” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Mitigation_x0020_plan” ColName=”ntext3″ StaticName=”Mitigation_x0020_plan” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Contingency_x0020_plan” ColName=”ntext4″ StaticName=”Contingency_x0020_plan” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Trigger_x0020_Description” ColName=”ntext5″ StaticName=”Trigger_x0020_Description” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Trigger” ColName=”nvarchar5″ StaticName=”Trigger” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>
<FieldRef Name=”Links” ColName=”ntext6″ StaticName=”Links” SourceID=”{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}”/>

SELECT
    ProjectStructure.Program,
    ProjectStructure.Project,
    UserData.nvarchar1       AS Title,
    UserInfo.tp_Title        AS ‘Owner’,
    UI.tp_Title              AS ‘Assigned To’,
    UserData.nvarchar3       AS ‘Status’,
    UserData.nvarchar4       AS Category,
    UserData.datetime1       As ‘Due Date’,
    UserData.float1          AS ‘Probability’,
    UserData.float2          AS ‘Impact’,
    UserData.sql_variant1    AS ‘Exposure’,
    UserData.float3          AS ‘Cost’,
    UserData.sql_variant2    AS ‘Cost Exposure’,
    UserData.ntext2          AS ‘Description’,
    UserData.ntext3          AS ‘Mitigation Plan’,
    UserData.ntext4          AS ‘Contingency Plan’,
    UserData.ntext5          AS ‘Trigger Description’,
    UserData.nvarchar5       AS ‘Trigger’,
    UserData.tp_Modified     AS ‘Modified’,
    ProjectStructure.FullUrl AS ‘URL’,
    UserData.tp_DirName + ‘/DispForm.aspx?ID=’ + CONVERT(varchar(5),UserData.tp_ID) AS [Risk URL]

FROM UserData
INNER JOIN LISTS
    ON UserData.tp_ListId = Lists.tp_ID
LEFT OUTER JOIN UserInfo
    ON UserData.int1 = UserInfo.tp_ID
LEFT OUTER JOIN UserInfo AS UI
    ON UserData.int2 = UI.tp_ID
INNER JOIN ProjectStructure(@ProgramName)
    ON ProjectStructure.Id = LISTS.tp_WebId
WHERE tp_ContentType=‘Project Workspace Risk’
AND UserData.nvarchar3=‘(1) Active’
ORDER BY UserData.sql_variant1 DESC

Recursive Function to Find all Children that belong to a level

I used this blog post as a starting point: Creating Recursive SQL Calls for Tables with Parent-Child Relationships and created a function in the SharePoint content database. Notice how ProjectStructure does an inner join on itself hence the recursion:

ALTER FUNCTION [dbo].[ProjectStructure](@ProgramTitle nvarchar(255))
RETURNS TABLE AS RETURN (

WITH ProjectStructure (Id, [Program], [Project], FullUrl, [Level]) AS
    (
        SELECT Id, Title AS [Program], Title AS [Project], FullUrl, 0
        FROM Webs
        WHERE Title = @ProgramTitle
        UNION ALL
        SELECT Project.Id, ProjectStructure.[Program], Project.Title, Project.FullUrl, 1
        FROM Webs AS Project
        INNER JOIN ProjectStructure
        ON ProjectStructure.Id = Project.ParentWebId
    )

SELECT * FROM ProjectStructure
)

I then created a report in SQL Reporting Services 2008 (check these cool SSRS08 reports I did recently!) and voila!

Level A
Level AA
Level AB

You can also run the query at the root (PWA level) and you’ll get all the Risks currently active in your Project Server instance. You could write a similar report for Issues. Last but not least use this reporting sample as a starting point for your reporting needs and don’t forget to test, test, test prior to any production deployment!

Happy reporting!

Related links
  SharePoint Database Access
  Reporting on Sharepoint lists from Microsoft SQL Reporting Services
  Creating Recursive SQL Calls for Tables with Parent-Child Relationships

Popularity: 7% [?]

Help update for Project 2007

If the computer you use to work in the Microsoft Office Project 2007 client is connected to the Internet, you have already see the latest Help files. However, if you work in the Project 2007 client offline, you can download an update to the offline Help files, so that you have more current content.

You can read more about the Help update here, or download the Help update here.

Popularity: 6% [?]

Announcing the CodePlex release of ShUIE (SharePoint User Interface Extender)

David Kitchen, Louise Brigthon and Calvin So from PCubed have recently released the following CodePlex project: ShUIE (SharePoint User Interface Extender):

ShUIE is an addition to Microsoft SharePoint that allows a developer to inject JavaScript and CSS fragments depending on the context of the page being displayed. JavaScript and CSS injected can be optionally minified, and jQuery is included to increase functionality.

An easy way to ‘pimp’ (to decorate) your Project Web Access User Interface, for instance (after spending 5 minutes to set it up and storing ShUIE configuration in my favorite the Reporting Database of my favorite PWA instance) I got this using jqDock sample:

jqDock Check this live demo from Jan Tielens. I get the navigation on the root PWA site and all project workspaces.

URLS were broken but the concept sounds interesting.

Another fun project worth investigating for the end-of-year holidays (consider the security and scalability for instance of ShUIE)!

Popularity: 6% [?]

Warm up your Project Server and SharePoint VPC in seconds!

Yet another cool tool from CodePlex: SPWakeUp - Wake up your Sharepoint and WSS Sites written by Andrew Kennel.

A great tool to warm up and optimize your demo performance as well as your favorite SharePoint farm.

A simple application that touches each site and site collection on a Sharepoint or WSS server to rebuild the IIS cache.
This project was inspired by the Warm Up script I found at: http://blogs.msdn.com/joelo/archive/2006/08/13/697044.aspx
For information about how the script was developed, please see my blog: http://akennel.blogspot.com

The SharePoint CodePlex community has been very active this past few months, click here (567 projects as of December 18 2008) for a lot more cool SharePoint projects that you could potentially leverage  in your Project Server deployments.

Popularity: 6% [?]

Server-Side Calculation of Custom Field Formulas

So I have a PSI application that pulls data from Oracle Financials and correlates it to specific projects in Project Server. using a key field it brings over 6 fields and writes it to 6 Project level Enterprise Number fields. It updates the fields and then calls QueueUpdateProject which should trigger a recalculation of custom field formulas. I also have several custom fields that use the original 6 fields in their formulas.

My problem is that the fields with formulas were not getting updated. If I opened the project into Project Professional then they calculated just fine.

It turns out that there is a problem with the calculation ‘engine’ in Project Server that causes it to stop recalculating these formulas if any one of them returns an error. In my case the error was caused by this formula: [Capital Actuals]/[Capital Plan] because for some of the projects the Capital Plan field was 0.

The solution is to make sure your formulas cannot return such an error. In my case it was changing the formula to this:
IIF([Capital Plan]>0, [Capital Actuals]/[Capital Plan], 0)

Similar issues can possibly be caused by a formula like [Field A]+[Field B] if either A or B have been deleted.

So if any of you are having issues where your field formulas are not refreshing properly go and check all your formulas and see if any of them are returning errors.

Merry Christmas\Happy Holidays\Have a Happy Next Two Weeks

Popularity: 7% [?]

Detecting Project Client Version

We have heard from a lot of customers that they want to block the Project Client from connecting to the server if it does not have the latest updates to install. To solve this, you can add one of the two VBA scripts to the enterprise global to check the client version. Both solutions are not perfect, but they may help solve the problem.

The first solution looks at the file version. The only slight issue with this solution is determining the location of the Program Files directory.

Sub projVersion()
    Dim MainFolderName As String
    Dim LastMod As String
    Dim Created As String
    Dim Size As String
    Dim projVersion As String

    Set objShell = CreateObject(“Shell.Application”)

    MainFolderName = “C:Program FilesMicrosoft OfficeOffice12″

    Set FSO = CreateObject(“scripting.FileSystemObject”)
    Set oFolder = FSO.GetFolder(MainFolderName)
     ‘error handling to stop the obscure error that occurs at time when retrieving DateLastAccessed
    On Error Resume Next
    For Each fil In oFolder.Files

        Set objFolder = objShell.Namespace(oFolder.Path)
        Set objFolderItem = objFolder.ParseName(fil.Name)

        Select Case UCase(fil.Name)
            Case Is = “WINPROJ.EXE”
                LastMod = fil.DateLastModified
                Created = fil.DateCreated
                Size = fil.Size
                MsgBox (“File: “ + fil.Name + ” Last Modified: “ + LastMod + ” Created: “ + Created + ” Size: “ + Size)
                ‘do all of the normal tests here to determine which version you need and which you have. See above.
        End Select

    Next

End Sub

The second solution looks at a registry key for the Project Client version and it was written with the help of http://www.arcatapet.net/vbregget.cfm:

Const MIN_PROJ_VERSION = 6330

Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003

Const ERROR_SUCCESS = 0&
Const REG_SZ = 1&                          ‘ Unicode nul terminated string
Const REG_DWORD = 4&                       ‘ 32-bit number

Const KEY_QUERY_VALUE = &H1&
Const KEY_SET_VALUE = &H2&
Const KEY_CREATE_SUB_KEY = &H4&
Const KEY_ENUMERATE_SUB_KEYS = &H8&
Const KEY_NOTIFY = &H10&
Const KEY_CREATE_LINK = &H20&
Const READ_CONTROL = &H20000
Const WRITE_DAC = &H40000
Const WRITE_OWNER = &H80000
Const SYNCHRONIZE = &H100000
Const STANDARD_RIGHTS_REQUIRED = &HF0000 Const STANDARD_RIGHTS_READ = READ_CONTROL Const STANDARD_RIGHTS_WRITE = READ_CONTROL Const STANDARD_RIGHTS_EXECUTE = READ_CONTROL Const KEY_READ = STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY Const KEY_WRITE = STANDARD_RIGHTS_WRITE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Const KEY_EXECUTE = KEY_READ

Sub ProjectVer()

    Dim projVersion As String
    Dim version As String

    projVersion = RegGetValue$(HKEY_LOCAL_MACHINE, “SoftwareMicrosoftWindowsCurrentVersionInstallerUserDataS-1-5-18Products0002109B30000000000000000F01FECInstallProperties”, “DisplayVersion”)
    version = Mid(projVersion, 6, 4)
    If (CInt(version) < MIN_PROJ_VERSION) Then

        MsgBox “Your version of Winproj.exe is not valid and may cause problems.” & Chr(13) & Chr(13) & _
        “The installation of new version of Microsoft Project is required!” & Chr(13) & Chr(13) & _
        “Required Version: “ & Left(projVersion, 5) & MIN_PROJ_VERSION & “.XXXX” & (Chr(13)) & _
        “Found Version:     “ & projVersion & Chr(13) & Chr(13) & _
        “Microsoft Project will now Exit.” _
        , vbExclamation, “Program Version Check”

        Application.FileExit pjDoNotSave

    End If

End Sub

Function RegGetValue$(MainKey&, SubKey$, value$)
   ‘ MainKey must be one of the Publicly declared HKEY constants.
   Dim sKeyType&       ‘to return the key type.  This function expects REG_SZ or REG_DWORD
   Dim ret&            ‘returned by registry functions, should be 0&
   Dim lpHKey&         ‘return handle to opened key
   Dim lpcbData&       ‘length of data in returned string
   Dim ReturnedString$ ‘returned string value
   Dim ReturnedLong&   ‘returned long value
   If MainKey >= &H80000000 And MainKey <= &H80000006 Then
      ‘ Open key
      ret = RegOpenKeyExA(MainKey, SubKey, 0&, KEY_READ, lpHKey)
      If ret <> ERROR_SUCCESS Then
         RegGetValue = “”
         Exit Function     ‘No key open, so leave
      End If

      ‘ Set up buffer for data to be returned in.
      ‘ Adjust next value for larger buffers.
      lpcbData = 255
      ReturnedString = Space$(lpcbData)

      ‘ Read key
      ret& = RegQueryValueExA(lpHKey, value, ByVal 0&, sKeyType, ReturnedString, lpcbData)
      If ret <> ERROR_SUCCESS Then
         RegGetValue = “”   ‘Value probably doesn’t exist
      Else
        If sKeyType = REG_DWORD Then
            ret = RegQueryValueEx(lpHKey, value, ByVal 0&, sKeyType, ReturnedLong, 4)
            If ret = ERROR_SUCCESS Then RegGetValue = CStr(ReturnedLong)
        Else
            RegGetValue = Left$(ReturnedString, lpcbData - 1)
        End If
    End If
      ‘ Always close opened keys.
      ret = RegCloseKey(lpHKey)
   End If
End Function

Popularity: 5% [?]

— Next Page »