Carl's Project Server Blog

Quick and dirty "Portfolio Gantt" report

2012-01-24T17:20:00.001Z

We all like Project Centre, with its lists of projects and useful fields, but sometimes you just want it to do a little more. For example:

- what if you could display some key milestones on the gantt

- what if you could easily display colour-coded gantt bar by phases.

The second point above (or variant of) has been requested so many times and as of yet I have not seen anyone deliver such a report or view, so I decided to give it a go.

Now, before we go ahead, the following is done with no coding and no complex Reporting Services reports. I am sure (and have seen) some amazing report views in SRS similar to this requirement, but this is a Proof of Concept solution. Go work out your own way :P

So, a Portfolio Report:

Ingredients:

- Excel

- SP2010 Farm with Excel Services configured

- an ODC

- Some formulas

- Some conditional formatting

Step 1: Define

This PoC is done working on the following:

- There are 5 Phases for each project

- The Gantt should include coloured bars by each of the 6 phases for the project

- This should update automagically based on current planned dates

- The date periods should be weeks across the columns

Note: In this scenario I haven't added any other Project Categories into the Table view, or report filters. I may get onto this later when defining the ODC connection

Step 2: mockup in excel

- Create a new file

- Create a table to include the following columns. This will be the basis for the data connection data later:

Project Name (COL B)
Phase 1 Start (COL C)
Phase 1 finish
Phase 2 Start
Phase 2 Finish
......
Phase 6 Finish (COL N)

Now in adjoining colums to the above table, create the same number of columns (two for each phase), using data formatting of Number, and reference the first set of colums, so:

IF([COL C] = "","",[COL C]
IF([COL D] = "","",[COL D]
etc
IF([COL N] = "","",[COL N]

So now we have

Now we want to set up our timephased column headers. Initially we will do this manually so:

- on your first available column, enter the date you wish to start from:

- then on the next column, same row, create a formula to add 7d to the previous date
- copy this formula field to the right as far as you wish the report to go.

- once done, create a formula in the cell row above the dates entered (ive done this in Row 2), and convert each date to a number field (as we did before)

Now then. How do we colour scheme our table by phase? we are going to write a nested IF Statement that compares the Date Value fields for each phase (created in the second image above) to the column date value created in the step previous to identify whether the formula will result in:

"1" = Phase 1

etc

"5" = Phase 5

NO PHASE

Note: In this example, one phase can only happen in one week.

The formula:

=IF($P7=AB$2,1,IF($P7=AB$2,1,IF($R7=AB$2,2,IF($R7=AB$2,2,IF($T7=AB$2,3,IF($T7=AB$2,3,IF($V7=AB$2,4,IF($V7=AB$2,4,IF($X7=AB$2,5,IF($X7=AB$2,5,IF($Z7=AB$2,6,IF($Z7=AB$2,6))))))))))))))))))

where:

$P7 = Project Phase 1 Start

$Q7 = Project Phase 1 End

etc

$AA7 = Phase 6 End

and

AB$2 = the timephased period in column AB2 (the first date period)

Now copy this formula down the column to the bottom of the table, and across to the end of the timephased period defined

Now we will do the conditional formatting on the timephased periods:

Now once this report is published to a SP2010 report library it looks like this:

Next Steps:

- Add a Current Date marker

- Make this a live report by connecting the Excel file to a SP2010 Data Connection and Project Server 2010 Reporting Database

- Improve on formatting

Someone killed your farm #projectserver

2012-01-09T15:29:00.000Z

** WARNING ** following this post may kill your already sickly farm....

Imagine the scenario:
- during some troubleshooting, someone deletes some EPM databases that they believe no longer point to a PWA Site (but actually do)
or
- when deleting a PWA Site, some environmental issue interrupts the process before it completes
or
- an issue occurred during PWA Site provisioning
or
- who knows what, sometimes bad things happen......

what you will see:

Sharepoint will continually report events such as :

An exception occurred while running the job scheduler. Reason: Cannot open database "ProjectServer_Published" requested by the login. The login failed. Login failed for user xxxxxxxxxxxxxxxxx. Technical Details: System.Data.SqlClient.SqlException: Cannot open database "ProjectServer_Published" requested by the login. The login failed. Login failed for user xxxxxxxxxxxxxx at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception

and

Project Server	Queue	7626	Critical	Cannot start queue. SSP: 150e20ee-631b-420d-9de8-7f47b43fc67e SiteUID: 5afc4aab-f94e-4e69-b7ba-7127a75535c4 Url: Queue: ProjectQ
Project Server	Queue	7626	Critical	Cannot start queue. SSP: 150e20ee-631b-420d-9de8-7f47b43fc67e SiteUID: 5afc4aab-f94e-4e69-b7ba-7127a75535c4 Url: Queue: TimesheetQ

- your event log will clog up with the above

- any other EPM site queues will begin to slow down

- users get very angry

What's happening under the hood?

basically deleting stuff from a SP farm WITHOUT doing so via STSADM, Powershell or the CA UI will cause all kinds of horrible problems that will mean you will either have to recreate Service Apps OR the whole farm (Depending on the scenario) to resolve.

What can you do:

well, beside hitting the person that did it in the first place, there is an STSADM command that could save you. However I will repeat the warning at the top of this page. running this command may kill your farm completely so ensure you have a DR strategy in place and ready to implement.

The command is: stsadm -o deleteconfigurationobject -id {GUID}

Now the GUID bit is the ID of the object you wish to delete from the Configuration Database "Objects" Table.

Which object should I delete, there are 1000's

- Objects are linked together via the Dependencies table

- your aim should be to attempt to identify the PWA site object that links to these now orphased DB objects. therefore start with any GUIDs linked to errors in the event log

- passing the PWA site GUID through the STSADM command will delete the site (this is your only option) but SHOULD also delete all the dependent orphaned objects, thus stopping the errors occurring as all trace of the site will be removed.

Note: In some scenarios, dependencies may be broken, which may cause some residual records to remain and problems to persist. It is worth identifying all the dependant objects prior to beginning this process.

An Example of a query that will help identify the relevant PWA Site Objects from the Farm is as follows:

SELECT *

FROM [SharePoint_Config].[dbo].[Objects]

where Properties like '

an example query to identify orphaned records is here (mileage may vary)

SELECT OD.ID,OD.Name, O.ID,O.Name

FROM Objects

INNER JOIN Dependencies ON O.ID = D.ObjectID

INNER JOIN Objects AS OD ON D.DependantID = OD.ID

WHERE O.Name like '[INSERT-DB-NAME-IN-EVENT-LOG-ENTRIES HERE]'

above query courtesy of this post http://viksrini.blogspot.com/2010/04/delete-orphaned-project-server-database.html

have fun!

Very important note around timephased actual reporting via MS Project Server 2010

2011-12-15T14:49:00.002Z

Source: http://technet.microsoft.com/en-us/library/hh694531.aspx

There has been an interesting post on Technet recently regarding reporting on timephased actual work with Project Server 2010. The link is above, but the key phrase is below:

When you protect user updates, timesheets always maintain exactly what the user entered for timephased work, and the scalar values between timesheets and the project plan are always consistent. However, the timephased distribution of the scalar value may be slightly different in the project plan, as it is geared toward future work planning and not maintenance of actual work historical values. The scheduling engine may adjust some timephased actual work to keep the plan consistent.

So, in English, this basically means that if you want EXACT timephased actual work reporting when using Timesheets and Single Entry Mode, you MUST Report from the Timesheet Subsystem, NOT MS Project assignment-level actual work. The plan should only be used for the scaler-view of Actual Work and the REMAINING work (although of course the timephased remaining work may also not be 100% accurate if the actual work timephased has moved and the plan has not been properly rescheduled.

Interesting.

Timesheet solutions in Project Server 2010 - #2 - the rest

2011-11-09T16:02:00.001Z

In a previous post I talked about assumptions and the reality of data access and security when configuring a simple "hands off" timesheet solution in 2010, and some concerns around functionality that is made available to users by default.

The other areas of concern for me were:

3 - Limitations of creating projects from templates via PWA

4 - curious behaviour of the auto-publish function when calculating Actual Cost

So here's some more little tidbits of information that I hope may help...

Limitations of creating projects from templates via PWA(Project.CreateProjectFromTemplate method)

As part of my solution I wanted to allow a two phase project to be created, with Phase 2 tasks disabled when the project is produced to avoid these tasks being available via the Timesheet function without the need to work via MS Project. Then I wanted the new phase tasks to be activated at a given point in the schedule ideally via PWA.

To do this I...
- Created a MSP template and saved it to the server with the apppropriate task structures, codes and with the deactivated tasks.
- Created an EPT with the new template as the default MSP template
- Created a Project Schedule view in PWA that included the Active and Publish fields for data management

At this point I created the plan in PWA via the EPT and guess what. All the tasks were active.

(face/palm)

After a little digging I found that this was by-design operations as the PSI call for createProjectfromTemplate did not include any reference to the Active field.

Although not a disaster, the next finding kinda nailed me. When I opened the plan for editing in PWA I could not edit either the Active or Publish fields, so at this point there was no mechanism to allow me to either deactivate or unpublish (sic) the tasks I want hidden from the timesheets.

My target process for htis was
- Project created in PWA
- Fill in fields via PWA PDP
- Publish
- users add tasks to timesheet

The process at this point became:
- Create project in PWA
- Complete fields via PWA PDPs
- Save and close
- open for edit in Pro
- Deactivate the phase 2 tasks
- users add tasks to timesheet

This process means that twice as many people as initially expected require MS Project and those users have to interface with two elements of the solution rather than one.

>>>> Fast forward one month >>>>>

Announcing the hotfix KB2596498 October 2011 CU for Project Server
QFE - 30887 - Creating a new project from a template ignores the Active task flag

This QFE would appear to be addressing the exact issue I have come across. However I dont believe it is (yet) addressing the lack of edit on the Publish or Active fields via PWA, but I shall be testing shortly.

At this point I think my creation process can use PWA Only, and when moving to Phase 2 the user will need to use MSP to close down Phase 1 and activate Phase 2.... more to follow (again)

Curious behaviour of the auto-publish function when calculating Actual Cost

Truth be told, I can't actually remember what this point related to. However there are a few findings to chew over that are fresh in my mind having seen them yesterday.

Auto-approval "skipped for optimization"
the Rules Engine for approvals that allows autopublish only works when the plan is not locked for editing. At this point if your rule runs to autopublish the task assignment to the plan, this will process to 20% in the queue and get the status "Skipped for optimisation". to me the correct behaviour would be to enqueue this until the plan is available for editing, or cancel it if a subsequent record change came through to be saved.

I am still risk-assessing this, as I believe that if the task assignment is NOT resubmitted with more updates at a point when the plan is checked in, those actual hours on the specific update that was skipped for optimisation are lost/never processed to the plan, unless you open and publish via MSP (again, tbc).

Get >= August CU to use this function
So far I have seen multiple examples of this rule processing failing to auto-publish, except where august CU or greater is installed.

Things to consider when looking at a streamlined, minimum intervention Timesheet system:

- There is no circumventing the Build Team option to allow resources to see plans, but at least you can do this via PWA
- You MUST implement affective closedown processes for tasks and projects to streamline the Add Task menus
- You will probably need to do some ribbon customisation to lockdown some functions on the Timesheet and Task ribbons.
- If you want to work with deactivated tasks in your project template, get October 2011 CU for Project Server (to be tested)
- if you want auto-approval and auto-publish rule engine functions to work get SP1+August 2011 CU for Project Server at a minimum
- Dont expect to work with task level Publish and Active fields via PWA as these are read-only

good luck!

Problem Provisioning PWA Site after SP1

2011-09-19T16:06:00.001+01:00

** UPDATE2: OK so I didn't reboot before that previous note. This has now resolved the issue. Reboot WAS tried prior to June CU

* UPDATE: After installing June CU2011 Server Rollups (SPF & SPS+PS) I am still seeing the same issues as before. More research to do.....

Since my rig had Project Server 2010 SP1 packages installed I have seen PWA provisioning fail when creating a brand new PWA Site with clean DB names etc.

Note: This is NOT a restore scenario:

Firstly I see the following, which is what I would expect:

09/19/2011 15:45:59.32 OWSTIMER.EXE (0x0C30) 0x05C4 Project Server Provisioning 6974 Information Provisioning 'zzzzzzzzzzzzzzzzz': Server versions: Primary 10.50.1600.1, Reporting 10.50.1600.1. 47871661-d3d4-46fa-9c73-f5faf328a37d

09/19/2011 15:45:59.35 OWSTIMER.EXE (0x0C30) 0x05C4 Project Server Provisioning 6975 Information Provisioning 'zzzzzzzzzzzzzzzzz': Databases specified for site 'ProjectServer' are as follows: Published: Name = 'zzzzzzzzzzzzzzzzz_Published', State = NotCreated Draft: Name = 'zzzzzzzzzzzzzzzzz_Draft', State = NotCreated Archive: Name = 'zzzzzzzzzzzzzzzzz_Archive', State = NotCreated Reporting: Name = 'zzzzzzzzzzzzzzzzz_Reporting', State = NotCreated 47871661-d3d4-46fa-9c73-f5faf328a37d

Then a little while later prior to the above job completing I see a second PWA Provisioning job is initiated, that reuses the PWA Site url configured by me and then shows the following in ULS:

09/19/2011 15:49:59.61 OWSTIMER.EXE (0x18CC) 0x2238 Project Server Provisioning 6974 Information Provisioning 'zzzzzzzzzzzzzzzzz': Server versions: Primary 10.50.1600.1, Reporting 10.50.1600.1. 27bbee07-9442-48ce-af3e-eb7dabaf80ea

09/19/2011 15:49:59.95 OWSTIMER.EXE (0x18CC) 0x2238 Project Server Provisioning 6975 Information Provisioning 'zzzzzzzzzzzzzzzzz': Databases specified for site 'ProjectServer' are as follows: Published: Name = 'zzzzzzzzzzzzzzzzz_Published', State = NotEmpty Draft: Name = 'zzzzzzzzzzzzzzzzz_Draft', State = Empty Archive: Name = 'zzzzzzzzzzzzzzzzz_Archive', State = Empty Reporting: Name = 'zzzzzzzzzzzzzzzzz_Reporting', State = Empty 27bbee07-9442-48ce-af3e-eb7dabaf80ea

Shortly after this, guess what:

09/19/2011 15:49:59.95 OWSTIMER.EXE (0x18CC) 0x2238 Project Server Provisioning 7070 Critical Provisioning 'zzzzzzzzzzzzzzzzz': One or more of the databases already contains schema. When editing or creating a Project Server instance, you may specify: * Four databases that do not exist * Four existing, blank databases * Four existing Project Server databases of the same version from the same installation. Combinations of blank, new, and existing databases are not allowed. 27bbee07-9442-48ce-af3e-eb7dabaf80ea

Subtle but important difference here... this second provisioning job that appears to have been initiated part way through the initial DB provisioning job now sees that the Published database = NotEmpty state.

Hypothesis here is that the first correct provision job has begun to build the DB schema, and then been kicked out by the erroneous second job, which finds a mismatch set of databases and bombs out.

Resolution:

So far it appears that the June CU resolves this issue, but I am retesting this now. be back after the break

Shiny-shiny

2011-09-09T15:40:00.003+01:00

Sometime over the last couple of weeks - whilst running some BI-focused EPM Demos - I have come to realise that sometimes no manner of demo will get people interested when they just want to jump to the good stuff, the charts and graphs, the decomposition tree, indicators and traffic lights and KPI's etc etc

Sometime's they just want the shiny-shiny

So with this in mind I've given my blog a spring clean and a spruce up. Time is pushed at the moment but I intend to attempt to post as much as I can over the coming months. As ever, there will be some trouble shooting and some how-to's that I hope people can get benefit from.

My SharePoint_Config Database fall over go bang

2011-09-09T15:03:00.000+01:00

The other day I was tibnkering on my new demo rig (I love SSD's) and the whole OS Drive locked up on my, causing me to hard-crash the machine.

Uh-ho i thought... my VM was running at crash point... better check it over.

Lo and behold, my rig was dead on reboot. Upon review my config_db was suspect - go figure - and I had to try and get it back (as I was going to lose quite a lot of work if i didn't).

So after a little trawl around I found the following. I hope it helps someone else in the future:

Run this in SQL:

alter database “Sharepoint_config” set emergency

Set DB to single user mode

Run this in SQL to find the SPID for the connection to the Config_DB

sp_who2

disconnect from SQL
ensure SQL Browser service started
open SQL Mgt but dont connect to the server
New Query

Kill SPID# from SP_WHO2 (where # is the SPID noted previously)

use Sharepoint_config
dbcc checkdb ("sharepoint_config", repair_allow_data_loss)

Run the following SQL

ALTER DATABASE DBNAME
SET MULTI_USER

Your DB should now be back and happy

2011-09-09T13:18:00.000+01:00

Timesheet solutions in project server 2010 - #1 Security black holes and foibles

This is my first outing into a full blown stand-alone timesheet solution. Yes, I’ve done the full bottom-up and top-down configurations before but never JUST timesheets.

The aim of this solution is to

- minimise the administrative overheads associated to running an EPM solution

- minimise “Project Owner” involvement in the process and thus minimise MS Project Pro licence needs

- Capture historic data (actual work and cost)

- A fixed list of “activities” per project, duplicated for work performed pre project and during the project

- Allow anyone to work on any project within their particular group/team/dept

- provide some insightful BI and reports off the back

The entry point in for this solution is the [magical] new option of automated publishing via rules, delivered as part of SP1, without which we would not be moving forward with this.

Voyage of discovery:

During the first two days of testing, I have come across the following challenges:

1 - Data Security on the Insert Row functions in timesheets (“Insert Task” and “Add Yourself to a Task)

2 - Deactivating unwanted Timesheet Add Line functions (Insert Row | Create New Task)

3 - Limitations of creating projects from templates via PWA (Project.CreateProjectFromTemplate method)

4 - curious behaviour of the auto-publish function when calculating Actual Cost

What follows in this post is a deep-ish dive into the world of data security on Timesheet Insert Row options

1 - Data Security on the insert row functions - ”Insert Task” and “Add Yourself to a Task

I started this process as I wanted to ensure that the menu options for these functions was scalable enough to be able to take the number of projects I wanted to throw at it, but also to confirm that I could get projects OUT of the list again at the appropriate time, retaining control throughout the project lifecycle.

These options are really important to me in this scenario as I need team members from certain departments/teams to be able to add tasks from the dept/team projects into their timesheets and begin to assign time, as we will not be individually resourcing tasks. We will also not be scheduling tasks as such so the ability to re-add a task (insert task function) is also key.

My starting assumption here was that the projects from which you can select tasks would be the projects you have permission to see (with appropriate Category permissions for the functions) via My Tasks so to prove this I temporarily elevated the My Tasks category to see ALL projects. I then created some dummy tasks in a handful or projects.

At this point - if the rule is true - I would be able to add any of these tasks to my timesheet via “Add yourself to an existing task” function. Of course on testing this is not the case.

How “Add yourself to an existing Task” works:

It turns out that the list of available projects form which you can pull tasks onto the timesheet is governed solely by the projects that include you in their Project Team, NOT by the application security model (which of course includes the switch “The user is on the project team” but we wont mention that).

Although I understand the logic here, as the ability to perform this task is a category permission, it seems strange that this is not governed by category data access rules. My working assumption is that this would also require Build Team from Enterprise permissions over the projects in order to allow the resource to assign themselves to the project team which all sounds logical and achievable within the constrains of application security in my head, but I dont develop software so…..

Workarounds:

the agreed workaround for this is that each dept/team resource is assigned to the project team as needed, and then the team members can pick whichever tasks they need.

Note : Tied functions

As far as I can tell so far, this function is tied to the Create new task function by category feature “Create new task or assignment” so you get either or both, which I REALLY REALLY REALLY don’t want as I need the task list is fixed and controlled across all projects (thats engineering for you) and we will be auto-approving task reassignment changes via rules. As far as I can tell so far, my only option is to deactivate this through custom code which I am currently experimenting with. More to follow (I hope)

Note: Removing Projects and tasks from the list:-

This permission/option DOES conform to the Security driven approach for locking down/closing projects. However it appears that it DOES NOT take account of projects that have no remaining tasks open for update. The Project and Summary Tasks will still appear in the list of available projects and tasks, but there will be no low-level tasks if these are all closed for update. T

How “Add Task” works

The Add Task function allows you access to existing Task Assignments to re-add them to your timesheet when they have “dropped off”. This in itself is fine and dandy for me but how does it handle closed tasks and projects as if it has all my assignments in there the project list is going to get big QUICK (as I am very busy)

During testing of the project closedown procedure I notice that using [again] Category level security to attempt to close a project (using Deny) does not impact the list of projects and tasks on display. This can only be done using the “Close Tasks to Update” function as far as I can see so far (and the project will only be hidden from view once there are no further tasks to be updated)/

A brief note on Closing Tasks and Projects:

In order to stop users accessing closed tasks during the lifespan of the project, you should use the Close Tasks to Update function as tasks are closed.

However you are also recommended to have a strong regime for closing projects down with a DENY category once everything has been completed, to ensure that the project is removed from Timesheet Inset Row functions.

Return of the data-geek. EPM OLAP Cube Custom Measures

2011-06-09T14:33:00.000+01:00

I thought i'd put rogether a quick post on Cube customisation, as this is something that I am constantly asked about. Now I am not an MDX expert at all, but there are some useful results you get from some pretty simple MDX expressions.

Firstly, custom measures are done in the OLAP Cube Database Management section of the Project Web App Server Settings page. Hightlight the cube you want to customise and then click Configure.

Once in this section you need to scroll down to the bottom and decide which cube you want to add the measure to in the OLAP database. Generally in most scenarios, the Portfolio Analyser cube is the best target, depending on the data you wish to interrogate. Once the cube is selected, click Insert.

S-Curves
OK, so you've got a decent OLAP report (in excel, PerformancePoint Services or the like) showing data by month, but you been asked for a nice S-curve report showing Baseline, Work and Actual Work on a cumulative curve. As with all useful things, its short but complex query that is needed.

This example would give you a cumulative WORK calculation, specifically from the earliest date possible.
> sum(periodstodate([time].[(all)]),[measures].[Work])

This example is a little more simple and would do the same but only over this year
> sum(periodstodate([time].[Year]),[measures].[Work])

This example is the associated Actual Work S-Curve but allows for there to be NULL values where no actual work exists - in the future - rather than the S-Curve flatlining after the last reporting period.
> iif([actual work]=0,NULL,sum(periodstodate([time].[(all)]),[measures].[actual work]))

Capacity Heatmap (Remaining capacity percentage)
A nice simple one for Excel Services conditional formatting, calculating the % of capacity that is remaining based on the Work values.
> ([Capacity]-[work])/[capacity]

Filtering a measure based on a dimension member to derive custom cube measures

imagine the scenario. On your report you want to put the following onto a stacked column chart
- Actual Work spent on Project Priority 1 projects
- Actual Work spent on Project Priority 2-4
- Resource Capacity (as a line)

note: Your priority field is a flat Lookup Table that doesnt have this "P2-4" grouping built in

In this scenario to easily derive these values:
1 - Create a measure for Actual Work spent on P1 projects:
ActWorkP1 ([measures].[actual work],[Project Priority_Project].[Priority 1])
2 - Create a measure for Actual Work spent on "everything else" besides P1 Projects
ActWorkP2-4 [Actual Work]-[ActWorkP1]

This may seem like an edge case and you can of course use a pivot table to cross analyse and filter for these values, but what this DOES give you is an interesting OLAP based source for KPI's....

Next target: I am currently looking at how I can derive a custom measure of the actual work done on one "Administrative" task across any given timesheet period....

More to follow!

Site Collection wont delete (orphaned record) - Sharepoint 2007

2010-09-17T12:51:00.000+01:00

Scenario:

- You created a managed path of /projectsites against the web app that is running your PWA Site

- you create the site collection for holding your Project Web Access Workspaces (so that you have your workspaces separated from your PWA Site)

- However this approach is changed at the last second, and you delete the managed path from the Central Admin configuration

- You then delete the site collection

result: Your Site Collection will not delete. Attempting to enumsites against the web app will result in an error

reason: the site has been orphaned in the content db

fix:

- Open your content db and select all from dbo.webs

- look fo the title of the Site Collection you created that you wish to delete in the Title field

- Copy the SiteID Guid

- in the 12/Bin folder of the app server, run:

stsadm -o deletesite -force -siteid [enter the guid here] -databaseserver [database server name] -databasename -[name of your content database]

This will delete the orphaned records.

to confirm, run enumsites again on your web app to check all is well

Deriving Milestone RAG Indicators from tasks to the Project Center

2010-09-10T11:06:00.004+01:00

updated on 10 Dec 2010 - MS1-Var-P field names were wrong. Hope Ive got it right this time!!! - Carl
updated on 13 Dec 2010 - data types and task level rollups added to field definition (shame)

Problem

In the Project Centre you wish to show the status of your "Gateway" events that are held as Milestones in the project plans to provide a Milestone Summary view. However there is no out of the box solution to doing this.

The Approach

Firstly you need a Task Level Custom Field that links to a lookup table that defines your milestones. For Example:

Field Name = C3_Milestone Type

Lookup Table = Gateways

LUT List = MS1, MS2, MS3, MS4, MS5

For each milestone you want to report on in the Project Centre (MS1...5), create the following field structure:

Note: -T represents task fields, -P represents project fields

- MS1 Switch-T

this is the control field that identifies the relevant task that is tagged with the Gateway = MS1 value (1), Baselined (2), Complete (4), not set (0)

IIf([C3_Milestone Type]="Phase.IT Specification and Design Complete",IIf([% Complete]=100,4,IIf([Baseline Finish]>56000,1,2)),0)

Type = Number
Rollup = Max

Note: you will notice 3 result isn't included. I did this in case we needed a 4th State to be defined later

- MS1 Finish-T

This field received the finish date of the task where the above flag has been enabled. Note: Rollup set to MAX

IIf([MS1-Switch-T]>=1,[Finish],"NA")

type = date

Rollup = max

- MS1 BaseFinish-T

As above but captures the baseline finish date. Note: Rollup set to MAX

IIf([MS1-Switch-T]=2,[Baseline Finish],"NA")

type = date
Rollup = max

- MS1 Finish-P

this project field receives the finish date captured by the previous task level custom field

SWITCH([MS1 Switch-T]=0,"",[MS1 Switch-T]>=1,[MS1-Finish-T])

Type = date

- MS1 BaseFinish-P

this project field receives the baseline finish date captured by the previous task level custom field

IIf([MS1-Switch-T]=2,[MS1 BaseFinish-T],"NA")

Type = Date

** UPDATED **

- MS1-VAR-P

This field does a Date Diff between the Finish-P and BaseFinish-P fields

IIf([MS1-Switch-T]=2,([MS1 Finish-P]-[MS1 BaseFinish-P]),0)

Type = Text

- MS1 RAG

Here you can apply a formula to derive your RAG indicator based on the VAR field above.

IIf([MS1 Switch-T]=0,"Not Set",IIf([MS1 Switch-T]=4,"Complete",IIf([MS1 Switch-T]=1,"No Baseline",Switch([MS1-VAR-P] >20,"4 weeks late",[MS1-VAR-p] > 10 And [MS1-VAR-p] <=20,"2-4 weeks late",[MS1-VAR-p] >0 And [MS1-VAR-p]>=10,"<2 weeks late",[MS1-VAR-p] <=0,"On Track"))))

Type = Text

** deliberate mistake = the above formula should say >0 and <=10 (rather than >=10). Sorry about that :/

The Result

** UPDATED **

CAVEAT: it appears that the issue with incorrectly calculating tiered/dependant formulas when publishing via the PSI has never been fixed. Therefore any PWA based Publish MAY cause Gateway milestones to fail or show incorrect values (or no indicators). Mileage may vary...

Project Web Access - Data Analysis (OWC) Web Part refuses to acknowledge OWC is installed

2010-09-10T10:12:00.002+01:00

Quick description:
We installed PWA, OWC and SQL AS 10.0 OLE DB Provider for a client install for Project Server 2007. Everything was hunkydory except, navigating to the Data Analysis page stated we needed to install Office Web Components. For some reason the web part wasn’t recognising that this was already installed.
Hang on… thought we had…
We reinstalled/ repaired / hopped up and down alot. Nothing. It just would not acknowledge it was there
Client PC #2 - same issue
Server side - same issue
Hop hop hop (scream)
When selecting Edit page on the Data Analysis page it actually correctly displayed the web part. I also noticed that creating a DA view was fine too
EH!?!?!??!!
On hitting hard refresh attempted to show the page toolbar before prompting again to install OWC
Several hours later…………
The Fix = remove and re-add the Data Analysis to the page (oh god, why didn’t i think of that sooner)
Time to work out the fix = 3 days
Grey hairs created through debugging = several thousand
Result = Reapplying the web part in the shared view of the page as Web Administrator fixes the issue for all users.

How to Configure Project Server 2007 Analysis Services with SQL 2008 SharePoint Addon for Reporting Services

2010-09-10T09:43:00.001+01:00

SQL 2008 analysis services for mops2007

Well we sorted it

It’s a bank holiday today but here’s the route we took

Installed SQL 2005 components from dec2005 feature pack on app server

Native client

Bacwards compatibility

note: xmo not installed due to SQL 2008 reporting services addon for sharepoint wouldn’t let it

Installed the following bits on app server to get things working

SQL 2008 native client

Xmo 2005 sp 3 cu5 - to get around the previous issue noted KB 972511

Ta daaa everything worked!!!

Only took a week :(

edit - postback to Brian Smith’s blog http://blogs.msdn.com/b/brismith/archive/2010/06/15/project-server-2007-analysis-services-issues-when-using-reporting-services-add-in-for-sharepoint.aspx

Project Server 2007 ActiveX Component Can't Create Object: Cube build failure against SQL Server 2008 Analysis Services

2010-05-26T10:11:00.004+01:00

Update: so we are getting the "cannot connect to repository" error. Having rummaged around I have just seen reference to the need for SQL 2008 Native Client install also... gonna check this out next

So, i am seriously stuck on something... hopeing to work this through today but thought I would share:

Consider....

Server Architecture:
Server 1 : DB server SQL 2008 / AS2008 / rS2008
Server 2: App server MOSS2007, Project Server 2007, 2008 Reporting Services addin for SharePoint
Server 3: WFE including 2008 RS addin for SharePoint

Configuring Server 2 for CBS
- install Native Client - OK
- Install Management Objects (XMO/AMO) - Failed (higher version aleady availble)
- Install BC

Cube run fails due with Unable to Create ActiveX component... error

http://support.microsoft.com/kb/972694

Ran the hotfix package mentioned for XMO and got the error "unable to connet to analysis services repository" - relates to native client

So as we are now in an unknown state I am now running the following action plan:-

- Revert VM lab back to stable point :)
- uninstall RS addin and reboot
- reapply Native Client, XMO/AMO, BC
- Run Cube (hopefully successfully)
- run XMO CU5 hotfix pacakge
- Run cube (hopefully successfully)
- reapply RS addin for sharepoint
- Run cube (hopefully successfully)

see you in 24hrs.....

Queue Failure - "User Synchronization for Project Web Access App Root Site and Project WSS Workspace"

2009-07-14T09:05:00.008+01:00

Event

You see a queue failure - "User Synchronization for Project Web Access App Root Site and Project WSS Workspace"

You may see a ULS entry that looks something like this:-

10/06/2008 17:11:37.16 Microsoft.Office.Project.Server (0x07D8) 0x0CF0 Windows SharePoint Services Database 6f8g Unexpected Unexpected query execution failure, error code 1205. Additional error information from SQL Server is included below. "Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." Query text (if available): "{?=call proc_SecAddPrincipalToRole(?,?,?,?,?,?)}"

What this means

Some element of PWA or Project Workspace permissions synchronisation has partially or completely failed due to a deadlock. Users may not be able to access the PWA site and/or WSS Workspaces associated to Project files

Triggers

The User Synchronisation of Project Workspaces and PWA Root Site queue job is a resource-centric operation. This means that the job can be triggered in the following scenarios:-

• A Resource Record is Saved
• A Security Group is Saved
• A Security Category is Saved
• The PWA permissions list is Saved

The following actions describe some of those key activities that do NOT trigger this specific synchronisation job as they can be described as project-centric:-

• Adding/Removing Resources on Plans/Tasks
• Synchronisation of Project Workspace via PWA Server Settings

In a normal operating scenario you would not expect high volumes of these jobs to be created - if you do then you should check your processes to minimise these occurances (see end)

Processing

The process this job follows can be described loosely as:-

I. All PWA and PWS permissions are removed (including any Fine Grained Permissions)
II. For the first resource to be synchronised, PWA site permissions are added via the appropriate (Microsoft Office Project Server) Permissions Set
III. For this resource, PWS site permissions are added based on their Project Access Rights and via the appropriate (Microsoft Office Project Server) Permissions Set across ALL Sites
IV. Steps 2&3 are iterated for each resource

The primary job that runs this process (and is captured in logging) is the SynchronizeSingleUsermembershipsInWSS.

Why can this job fail?

There is an increased risk of this job failing if one or more of the following statements are true:
Triggers:

I. There are ongoing changes being made to the Security Model (Category/Group permissions changes)
II. There are ongoing changes to Security Group Memberships
III. There are large volumes of resource account changes being made

Contributing Factors where the triggers are evident:

I. There are a large number of Project Workspaces in the environment
II. Resources have access to a large number of Project Workspaces
III. There are a large number of Active Users in the environment
IV. More than 1 of the above jobs can be triggered at any given point

So how can I resolve this?

1 - By Resource
If this problem is reported for one particular resource then open and save the resource record in PWA-> Server Settings->Manage Users and Groups

2 - For all resources
if this problem is more widespread then you can Open and Save the "Team Members" group (if this does include all resources in the Enterprise Resource Pool). Note that this should be done outside of normal working time to avoid a repeat of the problem.

So how can I stop it happening again?

1 - Minimise the amount of Resource-centric changes are made on Production during standard working hours.

2 - To minimise the user impact, grant Authenticated User access to PWA via a User Group.

or alternatively to avoid the issue completely (in conjunction with point 2 above)

3 - Turn off automatic user synchronisation for PWA, WSS or ALL

>> Run the UpdateUserSyncSetting.exe tool to set the User Sync Settings. Jon
provided you with the documentation to run the tool. This only needs to be run on
one of the servers.
>> Set up a job that will run the appropriate sync tools during non-business
hours. Make sure to start it early enough that it will finish before business
hours and not interfere with SQL jobs like backups.
>> The tools that you will need to run will be based on what setting you use for the
UpdateUserSyncSetting.exe tool.

If you set the tool to:

0 - then you will not need to use the sync tools as this will be normal
behavior.
1 - then you will need to run the WorkspaceRDBUpdate tool.
2 - then you will need to run the WorkspaceUpdate tool.
3 - then you will need to run the WorkspaceUpdate and the WorkspaceRDBUpdate
tools.

Note: My preferred method for these settings is to turn all auto synchronisation off, then use the Security Model (and some processes) to drive Project-centric synchronisations to wss workspaces (which are not affected by this tool) based on group memberships and roles on the project, and use the Authenticated Users access to PWA

These tools only need to be run on one server as they just add the necessary sync
jobs to the queue.

2009-04-29T16:06:00.004+01:00

Details on Office Servers 2007 SP2 including Project Server and April Cumulative Update Hotfix Release

- SP2 includes SP, IU and all patches thru to and including FEB 09 Cumulative Update
- April CU then includes specific fixes between Feb CU and now
- therefore you need both.

So how do i get a fully patched system:-
- Install WSS3.0 SP2
- Install Office Servers 2007 SP2
- Install WSS3.0 April CU package
- Install MOSS Uber Package - Or - Project Server 2007 April 2009 CU package

clients:-
- Install Project 2007 SP2 then April 2009 CU package

create one slipstream package for all the installations / patches required and do that (including all SP2 and April CU packages)

Note on Portfolio Server 2007 SP2 - this is not in the same release cycle - no news on release date yet but it is coming...

SP2 released

2009-04-29T14:06:00.001+01:00

Office Project Server 2007 Sp2 has now been released. see the details here - http://blogs.msdn.com/chrisfie/archive/2009/04/28/announcing-service-pack-2-sp2-for-microsoft-project-2007-and-microsoft-project-server-2007.aspx.

see teh webcast here http://blogs.msdn.com/brismith/archive/2009/04/28/project-server-2007-service-pack-2-sp2-is-now-available.aspx

Blogged with the Flock Browser

Help, my PWA site won't provision,,,

2008-10-21T11:31:00.001+01:00

Post on its way soon (just testing)

Project Server 2007 and Unique Workspace Permissions

2008-10-21T11:13:00.000+01:00

The scenario:
You have a project workspace linked to a project
That workspace has a custom document library/list or Item that requires limited user access
The permissions for the document library are not inherited from the parent site
Users for this document library/list are added individually

The action
Customer permissions are changed in PWA
User Sync kicks off and re-syncs the users permissions

The outcome
The user permissions are removed and re-added to the site as expected
Any "unique" user level permissions on the site are removed (ie if you have added additional "contributor" access as well as the Microsoft Office Project Server group permissions, etc)
The users permissions on the list/item that do no inherit from the site are also removed but are not re-added

It appears that the user synchronisation can remove all permissions from the site, lists and items including those where permissions are not inherited, but can only add site level and inherited permissions. Therefore
a - all list/item level specific permissions are removed
b - any unique site security for these resources is also removed

Workarounds/Recommendations

1 Create list access GROUPS and grant access to these lists using groups memberships (not assigning individuals)
2 Create a subsite(s) of the parent site wherever permissions on the lists/libraries are required to be different from the parent site

Carl Dalton Resume 2008 - Technical Version

2008-10-20T11:21:00.001+01:00

Carl
Andrew Dalton

Microsoft
Project Server Specialist

Project
& Programme Management Consultant

tel +44 (0)7734 274866

email:
cadalton@gmail.com

linkedin:
http://www.linkedin.com/in/carldalton

Personal Statement

Carl is an experienced
Microsoft Business Solution Architect with over seven years
experience in
working with the Microsoft Project Server and SharePoint
platforms, with a proven track record in design
and delivery of Microsoft solutions and processes that bring
measurable benefits to his clients.

He previously headed up
the EPM Solution Development Practice for a global Consultancy Firm focusing on Microsoft SharePoint and Project
Server technologies, and has recently lead the IT Programme
Office Management function within a major retail bank, focusing on
Portfolio Management and Governance of all Group internal investments

“Carl excels at
translating business process into technical solutions. He understands
key issues, defines strategies for resolving them and communicates
these to stakeholders in an open and effective way”

This recommendation taken
from my profile on http://www.linkedin.com/in/carldalton

Career History

Microsoft UK (CSS)
Dec 08 - now (CONTRACT)

Project
Server 2007/2003 Technical Lead, tasked specifically with helping to
define and deliver operational improvements into the support group,
with the direct aim of achieving end of financial year targets for
customer satisfaction. Providing technical mentoring to the
support team, and governing process compliance.

Microsoft UK (CSS)
Feb 08 - Dec 08 (CONTRACT 3+2+6 months)

Providing Technical and
Best Practice Advisory support to EMEA-wide customers on Project
Server 2003 and 2007 (including Microsoft Office SharePoint Server
2007 and SQL 2000/2005)

HBoS PLC
May
07 – Feb 08

Lead Programme Office
Manager

Responsibility for
overall governance of £15m IT Budget
In-house consultancy
provided on SharePoint 2007 design and governance

Pcubed Ltd
Jul
03 – May 07

Roles

Project Server 2003 and 2007
and SharePoint 2003 and 2007 Business Solutions Architect
SharePoint Solution
Development Practice Manager
PMO Consultant/
Manager
Project and Programme
Manager / Mentor

Example client
list:

Accenture (NHS Programme)

Red Bee Media (formerly BBC Broadcast)

Fujitsu Services (NHS Programme and Corporate)

Reuters

Central Government (multiple)

Cadbury Schweppes

British Sky Broadcasting (IT)

Arqiva (Telecoms & Engineering)

Projects Summary:-

Product
and Programme Management of Solutions Development team, focusing on
Microsoft technology-based extensions to the Microsoft
Project Server solution offering.
Design
and build of Microsoft Office Project Server 2007 prototypes for
client delivery, including design and build of WSS3/MOSS2007
based SharePoint solutions
Lead
architect for an integrated Programme Management solution (Project
Server 2003) and processes to support the management and
delivery of a multimillion pound UK wide IT programme (NHS
Modernisation Programme)
Design
and Implementation of a Proof of Concept (Project Server 2003 and
Windows SharePoint Services 2003) solution within a leading IT
Services organisation (NHS Modernisation Programme Southern Cluster)
Implementation
of Project and Programme Management solution (Project Server 2003
and Windows SharePoint Services 2003) within a large broadcast
media organisation
Implementation
of Pilot SharePoint Portal Server (SPS) collaboration and
knowledge management environment for Arqiva (Telecoms)
Consultancy
support on MOPI Programme Management solution (Project Server
2007 and Windows SharePoint Services 2007) across multiple
police forces
Delivery
of full EMEA-wide EPM solution for Cadbury Schweppes (Project
Server 2003 and Windows SharePoint Services 2003)
Lead
Solution Design outline as part of EPM Project Feasibility Study.
(Project Server 2003 and Windows SharePoint Services 2003)
Design
and Implementation of a multi-programme EPM solution for the Home
Office IT and IND (Project Server 2003 and Windows SharePoint
Services 2003)
NHS
modernisation programme exposure within both Accenture and Fujitsu
IT Services (Project Server 2003 and Windows SharePoint Services
2003).
Design,
implementation and delivery of Programme Governance / Assurance
procedures at BSkyB (£100m Portfolio) (Project Server 2002)
Home
Office IT / IND Programme Delivery Processes – defined
approaches across project delivery within both IT and Corporate
Services Modernisation programmes
Client
Relationship Management experience at Programme Director to CIO
level
Motivator
and manager of teams in extreme operational situations

Symbian
Ltd Jun
03 – Jul 03

Symbian
are a leading provider of Open OS platforms for Mobile Communications
technologies.

Leading
role in the design and implementation of both Master Planning and
Resource Planning functions within a newly created department
Solution
design consultancy to the project team responsible for the
implementation of their EPM solution based on Project Server 2002
technologies

BACS
Ltd. Programme Office Consultant Jul 01 – Jun 03

BACS
Ltd are the main Automated Clearing-House for the retail-banking
sector in the UK.

Starting
initially as a Project Support Analyst, Carl’s role developed
into having responsibility for all strategic and tactical
improvements within the NewBACS Programme Office. In addition, he is
a project team member, working on the implementation of MS Project
2002 across the enterprise.

Leading
team of 9 PSO’s across the programme, ensuring delivery of key
customer information and data quality – PRINCE 2 environment
Responsibility
for delivering Programme Office tactical improvement plan, including
Financial Audit, Reporting development, tool and process design.
Business
and technical lead on Project Server/P10 and Sharepoint Server
Installation
Re-engineering
Programme and Enterprise-wide reporting processes/templates
including review of all Project & Programme Office procedures
Project
planning troubleshooting and MS Project SME, including
training and best practice guidance
Holding
ongoing training of the project management community (technology &
process)

Versatel
NV, Amsterdam. Programme Co-ordinator (contract) Jan 01 – Jun
01

Versatel
are a broadband telecomms service provider and carrier, based in the
Netherlands, Belgium and Germany. Carl was contracted with Versatel
to co-ordinate their Billing (Singl.eView), CRM (Clarify) and
Middleware (ActiveWorks) implementation throughout their European
offices. He was solely responsible for co-ordinating all planning and
financial activities on behalf of the Programme Manager across the
company.

Review
and update Programme Office procedures
Writing
the budget proposals and tracking models for the programme
Planning
and authoring internal communications
Administering
the Change Control process
Administering
the Risk and Issues processes.
Defining
“ways of working” best practise processes for the
project teams

Ernst
& Young, London. Project Coordinator (contract) May 00 –
Jan 01

Carl
was contracted to work on a project to rollout Windows 2000, Office
2000 and Notes R5 across the UK, as part of a global desktop
strategy. His responsibilities included:

Management
of the overall project budget
Designing
and writing both technical and informative communications to all
levels of the firm, including the production of the Communications
plan
Design
and administration of the Change/Version control process
Assist
project managers in planning and scheduling
Transition
Project coordination and issue management
Working
with regional administrators to ensure the migration process ran
smoothly and on plan

Elan
IT (Database Support Unit), Leeds. Team Leader Oct 97 – Apr 00

Skills
freetext

Windows
2003 Server, Windows 2000, Windows 95,NT4, Office 95, Office 97,
Office 2000, MS Access 2000 and SQL, MS Exchange ,Project 2000,
Project 2002 Professional, Project 2002 Web Access, Microsoft Office
Project Server 2003, Project 2002 Server Administration, Project 98,
Project Central, Visio 2000, Notes R5, Lotus CC:Mail, Lotus
Organiser, MS Outlook, MS SQL Server 2000, UMT 3.3, Microsoft Office
Project Portfolio Server 2007, MOSS, Microsoft Office Sharepoint
Services 2007, WSS3, Microsoft Office Project Server 2007, Sharepoint
Portal Server, SQL Server 2000, SQL Server 2005

Certifications
and Training

UMT
3.3 (Microsoft Project Portfolio Server) Install, Design and
Configuration
PRINCE2
Practitioner - Foundation and Practitioner course (5 day
residential)
MS
Office User Specialist
MS
SQL Server, Technical Intro & Administration
Project
Central, Advanced & Administration
MS
Project 2002, Intermediate & Advanced

Page
1 of 4