I came across a little possible pitfall while generating some reporting for a client, which I thought I should share with the community.
In Microsoft Project, the Start Date in Project Information defaults to the Start Date of the first task in the plan.
Obviously this can be changed in the Project Information so that the Start Date of the Project does not necessarily reflect the Start of the first task in the plan, or the Project Summary task.
So which date does appears in the reporting database? Well, here are the results:
From the MSP_EpmProject_UserView view in the reporting database
As you can see, the date from the MSP_EpmProject_UserView displays whatever is set in the Project Information. This might cause some unexpected information in reports, so we need to expand our query to include the date from the Project Summary task:
So, when writing the specifications for your reports, make sure you’re clear which date the client wants – it’s not unheard of having a plan created a few months in advance of the work being realistically scheduled which might cause this confusion!
Obviously the clear process-driven workaround is to have your Project Managers ensure that the Start Date in Project Information is updated when scheduling the project!
Just a quick note to let you know about an error I came across today.
In Project Server 2010, I had a field called “Objective”. This was associated with a lookup table of the same name, which was single-value select. This field was added to the Project OLAP cube, which built successfully. So far so good!
I then changed the field type to multi-select, and left the cube to build overnight. Came back the next day to a failed OLAP build. The exact error I got in the queue was this:
- CBS message processor failed:
- CBSMetadataProcessingFailure (17005) – InitCustomFieldDimensions cannot process the Project custom field ‘Objective’. Details: id=’17005′ name=’CBSMetadataProcessingFailure’ uid=’c69b0459-5d1f-4d78-a410-f56cd32eca97′ QueueMessageBody=’Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=<SQLServer> ASDBName=OLAPCube ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=10/30/2012 00:00:00 ToDate=10/30/2012 00:00:00 HighPriority=True’ Error=’InitCustomFieldDimensions cannot process the Project custom field ‘Objective”.
Hmmm…OK, must be the change I made to the field. I need this field to be multi-value, but I don’t necessarily need it in the cube for reporting. So, I went to remove the field from the cube but it wasn’t listed. Weird. I thought re-saving the OLAP configuration and re-building it would just flush out the field since it was no longer listed in the cube configuration. I got the same error in the queue again.
The only fix I found for this was to re-create the OLAP cube with all of the same settings as previously, minus the “Objective” field, which I couldn’t add anyway as multi-value fields aren’t available to add to the cube.
Hope this helps you out if you come across this error in the future.
Edit: it seems that this was a known issue back in 2011, which hasn’t yet been fixed. More info on Brian Smith’s blog here: http://blogs.msdn.com/b/brismith/archive/2011/02/04/project-server-2010-take-care-changing-custom-fields-to-allow-multiple-values.aspx
Have you ever wanted to hide tasks from the Gantt Chart? The following technique is useful for tidying up a schedule – for example, if you want to show only pertinent Gantt information for a presentation, or take a screenshot of a plan for a client that contains some internal tasks.
To hide individual bars in the Gantt display, simply insert the “Hide Bar” field and set it to “Yes” for the requisite tasks:
You’ll notice that all incoming dependencies for the hidden bar are also hidden, so take care when looking at your predecessor/successor logic!
A useful little nugget about Microsoft Project that came in handy the other day is showing or hiding items from your Gantt chart. It’s as simple as this:
In Microsoft Project right-click in the Gantt Chart and select “Gridlines”:
To show, for example the Current Date on your Gantt, select “Current Date” and change the line type from blank to any other line style. Change the colour if required, then click “OK”.
My example here is showing the Status Date as a red line, and the Current Date as a blue line:
One query that we receive a lot from our clients is about setting up automatic traffic light (or RAG) indicators for their project schedule. This is a very well documented request, but a recent client wanted a slight variation – the project schedule indicator to have its tolerance based on a percentage, instead of a hard-coded value. The following formula solves this issue:
IIf([Baseline Duration]=0,"No Baseline",
Switch([Baseline Start]=ProjDateValue(‘NA’) Or [Baseline Finish]=ProjDateValue(‘NA’),"No baseline",
[Duration Variance]<=0,"On schedule",
[Duration]/[Minutes per day]>([Tolerance for schedule in percent]/100)+([Baseline Duration]/[Minutes Per Day]),"Outside tolerance",
[Duration]/[Minutes per day]<=([Tolerance for schedule in percent]/100)+([Baseline Duration]/[Minutes Per Day]),"Within tolerance"
This is for the task schedule RAG, and will return values based on a number field called “Tolerance for schedule in Percent” to indicate whether the task duration has increased beyond its allowed tolerance.
You will also need to set up RAG graphical indicators for this field as well, with the following values, as per the screenshot below:
No baseline = Question Mark
On schedule = Green
Within tolerance = Amber
Outside tolerance = Red
Of course, your indicators could be different to those that I have chosen, as well as the text. Just make sure to update the formula if you want to change the returned text.
Hopefully this will shorten your chin-scratching time when attempting to do something similar!
Inactivate task is a new feature for EPM2010; this feature enables the project manager to set certain tasks to inactive rather than deleting the tasks. This functionality is also useful for performing what-if analysis.
The following paragraph from Microsoft explains that intended usage of the inactivate task functionality:
“Microsoft Project Professional 2010 enables you to cancel a task but keep a record of the task in the project plan. This is called inactivating the task. The task remains in the project plan, but does not affect resource availability, the schedule or how other tasks are scheduled. Note: Inactivating a task is a feature available only with Project Professional.
Why would you want to inactivate a task? For one thing, it can help you model the effects of schedule or resource constraints on the project without deleting tasks permanently. Also, inactive tasks remain in the project plan, providing a record of cancelled tasks and enabling you to reactivate them if circumstances change.
Note: Inactivation is nor a good way to archive complete tasks, because it could have unanticipated effects on the remaining schedule. Instead, mark the tasks as completed.”
Please see the following best practise guidelines for using inactive tasks:
- When in the planning phase, if you decide that a task or tasks are not required, at this stage use the inactivate task feature to set tasks to inactive rather than deleting the tasks. this will give you the option to quickly make these tasks active at a later date if there is a requirement for these tasks.
- Inactive tasks do not affect resource availability. Baseline values that have already been taken are retained, but any new baselines taken will not include data for inactive tasks.
- Tasks that have actual work cannot be made inactive.
- Inactive tasks are not published, so inactive task assignments will not appear on a team member’s task list.
- Inactive tasks are available to report on via the Project Server OLAP cube, so care should be taken when reporting that these tasks are/aren’t included depending on the figures required. The “Task is Active” field can be used to include or exclude inactive tasks. If you do not see this field in your OLAP cube, it may be that the EPM administrator has not enabled inactive tasks in the cube.
Due to possible differences in calendars between EPM (Enterprise Calendars) and Microsoft Project (local project settings), resource rate changes using an effective from date may not be applied from the beginning of the working day. At one client in particular, where all resources in the Enterprise Resource Pool received an updated (increased) rate from the first of the financial year, this caused project financial information to be out by a couple of hundred to a few thousand pounds. Given that this particular client uses timesheet and therefore project actual work and cost figures to update their financial system for client billing, this is quite a big problem.
This article describes how to set the effective from time for the resource rate in order to ensure that it is applied from the beginning of the working day.
Setting Project Options
Open Microsoft Project and click on File > Options. Under the General tab, change the Date format to include the time, as below, and then click OK.
For resources that require a rate change, navigate to Resource Center in PWA. Select the resources for which the rates should be updated and click Open:
This will open the selected resources in Microsoft Project. To update the resource rate, double click on the resource and click on the Costs tab. The Effective Date will contain the time as well as the date. Ensure that the time is set to the same as the Enterprise Calendar for the start of the day.
Repeat for all resources as required. When complete, save the changes to enterprise resources (File > Save) and close Microsoft Project.
If required, change the date display setting back to show only the date by repeating the steps above.
Strictly speaking this quick article doesn’t really have anything to with SharePoint, but hopefully it might help-out someone out there in SharePoint land.
One of the projects I’m currently working on is a 2010 Farm build, we are just getting the media set ready to install SharePoint with AutoSPInstaller, and of course need to download the Pre-Requisite Installer Files. For this particular project I have no local access to the servers so everything is being done by RDP on the server. So to download the Pre-Requisite Installer Files I’m was using this script from codeplex, but it was failing with a really wierd error
I tried it on a few different servers, but they all had the same problem.
I could of course simply open the script, grab the URL’s and paste them into a browser, but where is the fun in that.
This KB article talks about foreground / background transfers, and this being a problem when using background transfers and that foreground should be okay.
This article describes the cmdlet references for BITS and the –Priority switch that controls the foreground / background transfer setting
So the fix was to change this line
Start-BitsTransfer -Source $Url -Destination $DestFolder\$DestFileName -DisplayName "Downloading `’$DestFileName`’ to $DestFolder" -Priority High -Description "From $Url…" -ErrorVariable err
to this line
Start-BitsTransfer -Source $Url -Destination $DestFolder\$DestFileName -DisplayName "Downloading `’$DestFileName`’ to $DestFolder" -Priority Foreground -Description "From $Url…" -ErrorVariable err
and Voilá, pre-reqs downloaded .Follow @NeilKing41
I recently had an issue at a client where the Project Center was failing to load and returning “An unknown error has occurred” on the page. We have seen this behaviour before and it’s usually some corrupt Project data that causes it, but I’ve never had the time to get to the bottom of the problem. Fortunately, today I had a bit more free time to investigate.
This particular issue is caused by custom fields that are linked single value (read: not multi-select) lookup tables erroneously getting multiple values assigned to them. How this occurs is as-yet unclear, but it appears to be symptomatic only in the published database – the draft and reporting databases are unaffected.
So, what’s the fix?
There are a couple of things that can be done, depending on your level of access and technical capability. Firstly, we need to identify the plans that are causing the issue. The fastest way of doing this is with a little piece of SQL code (change <Published_DB_name> to your actual Project Server published database name):
USE <Published_DB_name> SELECT (SELECT proj.PROJ_NAME FROM dbo.MSP_PROJECTS proj WHERE pcf.PROJ_UID = proj.PROJ_UID) as ProjectName ,(SELECT pcfv.[MD_PROP_NAME] FROM [dbo].[MSP_CUSTOM_FIELDS] pcfv WHERE pcfv.[MD_PROP_UID] = pcf.MD_PROP_UID) as CustomFieldName ,count(*) FROM [dbo].[MSP_PROJ_CUSTOM_FIELD_VALUES] pcf GROUP BY PROJ_UID, MD_PROP_UID HAVING COUNT(*) > 1 ORBER BY ProjectName, CustomFieldName
(credit to Jan @ Piet Remen’s blog for the above query – http://pietremen.blogspot.co.uk/2012/01/project-centre-unknown-error-has.html)
This will return a list of projects, custom fields and the number of times that the custom field has been populated. If any project appears as an output of this script, you can view view the issue by taking a look at the Project Information through PWA. Affected custom fields will appear comma separated – e.g. Project Priority: Low, Low, Low, Low – where they should only appear as a single value.
The fix is quite a simple one – republish the project. This pushes the values from the draft database back to the published database and overwrites the broken custom field values. Check in PWA > Project Information, or re-run the above query to check that the plan is now fixed in the published database. I have seen a couple of instances where this did not fix the problem, and we had to delete the published version of the plan, and re-publish from the draft version.
It is still unclear how long the project will remain “fixed”, we have had a couple of plans reproduce the issue after being republished.
Microsoft have identified the issue, and released a fix, which can be found here: http://support.microsoft.com/kb/2598251
This KB is also included in the roll-up package (http://support.microsoft.com/kb/2597152) though it’s not documented.
These KB’s will prevent the issue occurring in the future, but will not fix existing problems – be sure to follow the above steps to ensure that the plans are fixed. As always – be sure you have database backups before applying updates!
The last 2 SharePoint 2010 systems I have looked at have displayed the “Action Required” status for one or more servers in the farm in the “Manage Servers in this Farm” page, which is normally caused by incorrect server patching.
When you apply a Service Pack or CU to your SP2010 farm, you are normally looking at performing a quite straight forward 3 stage process.
Stage 1: Obtain patch:
Download the latest Service Pack or CU from here:
http://technet.microsoft.com/en-us/sharepoint/ff800847#LatestUpdates or use the “Use this page to view the latest patch status for products installed on servers in the farm” link on the CA site in Central Administration > Manage Patch Status
Stage 2: Install Patch
Once you have your Service Pack or CU, you will need to run it on each of the servers in your SharePoint Farm that has the SharePoint binaries installed, there is no special order to do this, but personally I like to run the patch on each WFE in turn, then on the application servers.
Once the patch has installed you will normally be prompted to run the Config wizard, if you are working on a single server farm, run the Config Wizard at this point, if you are working with a multi server farm cancel the Config Wizard and run the patch on each server on your farm.
Stage 3: Config Wizard
If you are running a single server system and have followed the instructions in stage 2, you should be finished. If you are running a multi-server farm you now need to run the Config Wizard to finalize the patch install. I like to run the wizard on the 1st server I patched and let it run to completion, then run the wizard on the rest of the servers in the farm, again there is no particular order to this but personally I like to run the wizard in the same order as I patched the servers, Once finished a quick reboot all round and we are done, and your status should be “No Action Required”