Project Details Web Part Error When Using Host Headers

This was an issue that I spent a good amount of time working with a customer on; when you add the Project Server web-parts to other SharePoint sites in other web applications, in some cases the Project Center / Project Details webpart will give the following error always:

"Project Center cannot access the project(s) you are trying to view. It is most likely that you either don’t have permissions to view the project, another user has deleted this project(s) before you were able to view it or that another user is in the process of publishing the project."

clip_image001

Quick note: The webpart can be added to other sites using the steps discussed here by Christophe.

After a long investigation the problem was found to be a repeatable issue caused by the use of Host Headers in the Alternate Access Mapping configuration of the SharePoint farm.

In short; when the PSI URL configured in the .webpart XML web part configuration file and / or the site in which the webpart has been added uses a host header other than the NETBIOS name, then this problem will occur.

All other Project webparts work without issue, Reminders, My Tasks, etc although I have not tried them all, I might guess that the Resource Center webpart would have the same issue.

Eventually we confirmed with Microsoft support that this issue is a bug and no hotfix is currently available.

In terms of workarounds, not many good ones exist. I tested adding a second Alternate Access Mapping pointing to the NetBios name, however this only works if all clients use the non-hostheader address, in other words:

Site: "Project XYZ Workspace" accessed via http://servername/site/projectxyz will work BUT when accessed via http://hostheader/site/projectxyz (which is the same site) it DOES NOT WORK. That obviously has major drawbacks.

In the end in my case our solution was to move the PWA site into the other web application.

I hope this helps someone out there.

Errors in clean install PWA on Windows 2008R2

I came across this one recently, basically after successfully provisioning PWA on a new Windows 2008 R2 server I then found that I received the following errors in Resource Center and when editing items such as Users:

“There was an error loading the page. Please try again by clicking Refresh. If this problem persists, contact your administrator.”

“An error was encountered in loading the page. Refresh the page, or contact your server administrator if this problem persists.”

This led me to increase ULS logging to Verbose and eventually find this little one:

Application error when access /_vti_bin/PSI/resource.asmx, Error=’gzip’ is not a supported encoding name.  Parameter name: name   at System.Globalization.EncodingTable.internalGetCodePageFromName(String name)     at System.Globalization.EncodingTable.GetCodePageFromName(String name)     at System.Text.Encoding.GetEncoding(String name)     at Microsoft.Office.Project.Server.PSIForwarderHandler.ProcessRequest(HttpContext context)     at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()     at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

It seems some settings were different in this installation (this was a clean install by clearly not default), see the IIS Compression settings (Open IIS Manager and select the Compression setting under the server name):

Once I deselected ‘Enable dynamic content compression’ and hit Apply then immediately the above errors disappeared!

UPDATE:

Came across the following KB article which covers this issue for IIS6: http://support.microsoft.com/kb/947899

Reporting workspace lists directly from SharePoint

Update 31/03/2011: See the following blog about how much simpler this is with SQL Server 2008 R2.

This is one of the most common gripes that I hear about Project Server’s reporting database, the fact that only the default fields in your Project workspace lists, ie Project Risks, Project Issues, etc are available to report on.

Unfortunately I have yet to come across a client who uses (for example) a percentage probability of risk, I’m guessing it’s all that PRINCE2 influence. So as a result you are usually left with two options;

  1. Update the default fields to match your requirements, which actually is possible in 90% of the cases with some careful modifications to the workspace template and possibly some custom MDX measures for your cube. The biggest drawback with this one is if you want to report on new lists, such as for example a Change Register it is simply not possible without extending the RDB and cube!
  2. Secondly you could find and purchase a third-party product that will allow you to report directly on all of the list contents of your workpace, some of these link directly into the sites, others use a staging database.

There is of course a third option though, do it yourself, and that is what this blog post is about.

How to access the SharePoint data?

The first question is how, and of course I should be clear as a one-time minion of the capital MS, the question must be how to do it in a supported manner. So that completely rules out direct SQL access to the databases!

Fortunately there is another way: XML.

Let me start by saying that there is a lot of information is out there on this method, so here are some links to some of the most helpful pages; at codeproject, and other blogs here and here. (That last Blog one I probably found most useful!)

Before I get into it it’s worth mentioning that my example involves using Visual Studio 2008 with the SQL Business Intelligence Design Studio (BIDS) installed. Don’t let the VS2008 bit scare you it would be possible to do much of this using Report Builder, at least once you have your data connections created, perhaps I’ll leave that for a future blog?

In summary this is what you need to do:

  1. Create a SRS data source pointing to your PWA Reporting database.
  2. Use the above data source to identify the field named "ProjectWorkspaceInternalHRef" from the "MSP_EpmProject_UserView" table for each project you want to report on.
  3. Then use the URL indentified to dynamically construct your XML data source query by basically appending "/vti_bin/lists.asmx" to the end of the string.
  4. Finally using XML pull down the information that you want.

Too easy!

Okay show me the details!

Okay so first thing you need to do after firing up Visual Studio is start a new blank Report Server Project, then:

Step 1: Create your Shared Data Source

Create a standard SQL data source pointing to your Project Server Reporting database, such as:

Data Source=EPM2007DEMO;Initial Catalog=ProjectServer2007_Litware_Reporting


Step 2: Enumerate Workspace URLs and Select your Project

Firstly create a blank report with a simple SQL query to enumerate a list of all projects and their associated workspace URL:

Example query:

SELECT ProjectName, ProjectWorkspaceInternalHRef

FROM MSP_EpmProject_UserView

Then create a Parameter to prompt the user for a selection:

Name: SelectedProject

Prompt: Select Project

Select Get values from a query for Available Values:

Value field: ProjectWorkspaceInternalHRef

Label field: ProjectName


Step 3: Create your XML Data Source

Now the interesting bit, create a new non-shared datasource (Embedded connection).

Type: XML

Connection String: Expression

Credentials: Do not use credentials

Expression should be as follows:

=Parameters!SelectedProject.Value & "/_vti_bin/lists.asmx"


Step 4: Now create your Dataset using XML

Now you need to create your first Dataset in this XML data source, this is where we reference the actual list item, in this example Project Risks.

Create a new dataset using the following query:

<Query>

<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>

<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>

</Query>


Populate the Parameters with the "listName" (note: this is case sensitive) parameter with the value "Risks":


Step 5: Select your Fields to include in the report

Now the annoying bit, under Fields you must include each field that you want to include in your report, however because we are dynamically generating the data source we cannot automatically populate this.

Note: To automatically populate this list hard code the data source to an actual workspace site then when you create the query for this dataset select Refresh Fields.

Otherwise you can manually populate these, just open the list in question in a workspace and open list settings:


Then use the Column name in the Fields list source by simply prefixing each name with ows_, for example: ows_Title

Resulting in something like this:


Step 6: Finally Create a Test Report to see the Results!

Now add a table to your report and add those columns to preview the data, you should see something like this:


Unfortunately you might notice some fields being blank, there’s actually something we forgot! One of the possible parameters in the dataset above is "viewName", that does exactly what you would think select’s the desired view, so as my example above (using the EPM2007DEMO databases with default column names) Owner, Description and Mitigation Plan are all blank, this is because the Default view configured for that list does not include them.

So you can fix this by changing the default view to include the required fields or by creating your own view and using the viewName parameter. Note though; that parameter requires the GUID of the view not it’s name, so you need to use something like http://meyerweb.com/eric/tools/dencoder/ to decode the GUID from the URL of the view. (See the above linked blog by David Wise for additional details on the parameters available)

One that is done, you should now see something more like this:


Looking good! (Almost!)

Just one last thing there, you can see the special characters in the data, for the Description and Mitigation Plan it is an easy matter of changing the ‘Placeholder Properties’ to Markup Type: HTML. However for the Owner and as you’ll find various other fields they contain internal codes used by SharePoint, these have to be removed with code, again David’s blog above has some code to do just that, or if you’re like me and didn’t quite bother to read down that far you can create your own and add it to the Report Properties – Code section:

Public Function CleanSPSInput(strIn As String) As String

Dim RegEx as System.Text.RegularExpressions.Regex

‘ Replace invalid characters with empty strings

if strIn = "" OR IsDBNull(strIn)

Return ""

Else

Return Regex.Replace(strIn, ".+;#", "")

End If

End Function

Then just replace your Owner field name with the following expression:

=code.CleanSPSInput(Fields!Owner.Value)

Now your report should look something like this:


All Done!(Well for me at least)

What’s the catch?

There is actually a big one which you might have guessed: performance.

The above report is effectively generating the report by browsing to each page and pulling down each list item every time the report is generated. For one single project like in the example above, that’s not such an issue, but if you wanted to create a portfolio summary report for example or batch process a single project report for all active projects then performance would quickly become a problem.

Much of that can be addressed through Reporting Services itself, using caching and pre-generation, etc or better yet using SQL Integration Services (SSIS). With that and some much more interesting scripting a DTS job could be created to setup your very own staging database using the same XML methods as above! Definitely something to blog about another time…

But what about??

Yes there are lots of questions left unanswered;

  1. What if someone renames the list used above?
  2. What if someone changes / deletes the view I selected?
  3. What if …

I think you can see some of the limitations here and possible also how they can be overcome (workspace template standards, etc) but I’ll leave that to you.

    Cube Build Errors? MSOLAP.UPD?

    On a number of occasions I have had problems with this particular file, worse still there doesn’t seem to be much official information on it available. The file is MSOLAP.UPD located in the root folder of the drive on which SQL Analysis Services (SSAS) is installed, the file does not exist usually and is not created during setup, however when a cube is built the file will be temporarily created and then removed. If you run a ProcMon you will see this everytime.

    The problem occurs with Windows 2008 in which new security features restrict access to files in the root folders such as C: or D: which results in errors in the cube build.

    Specifically:

    “Error: Your permissions on the server computer do not allow you to administer this Analysis server.”

    Typically this will occur when the Windows 2008 SSAS server is different to the Project Application Server, as you may imagine a remote process initiated from the App server accessing files in the root folder on the Windows 2008 drive.

    The problem is exacerbated in large deployments when best practice security is used and the Project Server service account is different from both the SSP account and the MOSS Project Web Application Pool identity.

    The solution I have found is a rather simple hack, basically pre-create the MSOLAP.UPD file in the root folder of the server drive where SSAS is installed, and ensure that the following three accounts have full permissions:

    • MOSS PWA Web Site Application Pool identity
    • Project Server Service Account (the account that runs the Queue Windows service)
    • SSP Service account

    AFAIK the first is the one used by the ProjectServerOlapCubeGenerator.exe to touch the MSOLAP.UPD file, so start with that if you need to.

    Otherwise with that error message above the following should be taken into account:

    The accounts used for building Cube include the SSP IIS Application Pool Account and the SSP service account.

    • SSP Service account is used to connect to Analysis Services and trigger the cube build work, requiring access to the Repository, etc
    • App Pool account is used to launch the ProjectServerOlapCubeGenerator.exe process which runs on the app server but also appears to remotely connect to the SSAS server. (need to confirm this)
    • Finally the Project Server Service Account needs to be an SSAS admin.

    Hope this post helps someone out there as I have often had this issue in large deployments and for a long time have done the above steps without even thinking twice about it!

    Cannot edit Budget Cost values?

    I came across this problem while working with a customer and trying to use the very useful Budget Cost Resource feature, basically when using Budget Cost Resources and also restricting the ‘Save Protected Baseline’ permission (as many companies do) the Budget Cost values appear to be restricted with the baseline values! I.e. The values cannot be edited in the Task Usage view.

    It definitely looks like a bug (ahem “code – defect”) to me, however it’s not one I have had a chance (or need) to log with MS.

    Here are some reproduction notes I made while working on this one with a customer, needless to say we opted for the workaround as opposed to attempting to get a resolution.

    These steps are tested with the October 2009 CU package for both Project Server and Project Professional (Build 12.0.6520.5000).


    Issue:

    Budget Cost entry not working when Save Protected Baseline permission restricted.

    Scenario:

    Our organisation process requires that in Project Server Baselines can only be saved by the PMO department and not by project managers as such we have removed the default permission to ‘Save Protected Baseline’ from the ‘Project Managers’ project server group. Additionally for our project status reporting we need to track Budget expenditure compared to baseline, unfortunately we have found that when the ‘save protected baseline’ permission is removed from the PM’s they are no longer able to enter any budget costs into a project unless they were the original creator of the project (ie not someone who the ‘Owner’ was changed to). As our PMO is responsible for creating all projects with set templates and then assigning the PM this issue results in PM’s never being able to update Budget Cost’s.

    Environment:

    MOSS & Project farm deployment:

    – 1 x WFE with MOSS and Project Server 2007 (SERVICE PACK 2)

    – 1 x Project Application Server (SP2)

    – 1 x SQL 2008 database server (SP1)

    All servers running Windows 2008 SP1, all clients running Project Professional 2007 SP2.

    Reproduction Steps:

    1. As administrator in PWA open the ‘Project Managers’ group and remove the ‘Save Protected Baseline’ permission from all categories.
    2. As administrator create a new Project in project professional, create a local cost resource and from the resource properties, select “Budget” resource as the type.
    3. Assign the budget resource to the project summary task; first from Tools – options enable the option to show the project summary task, then assign the resource to that task in the normal way.
    4. From Task Usage view, add the ‘Budget Cost’ to the right hand side of the window, then enter some cost values.
    5. Save and Publish the project.
    6. From PWA – Project Centre, edit project properties and change the owner to a member of the Project Managers group (not an administrator).
    7. Open the project from Project Professional as the PM user then open the Task Usage view and add ‘Budget Cost’ (if required)

    RESULT:

    Unable to add further budget costs to any item, HOWEVER the PM is able to delete exiting costs!

    EXPECTED RESULT:

    PM is able to edit all items in the ‘Budget Cost’ line.

    Workaround:

    If the procedure is changed so that the PM creates the Project in step 2 above then the PM is always able to edit the Budget Costs, however this requires that internal business process be changed and does not account for future changes in the assigned PM needing to make updates.

    (Hope this helps someone out there…)

    PS. Ten points to anyone who recognizes where my issue subjective template comes from!