Project Server technical bits

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

** 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.SqlInternalConne...

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

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

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

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.

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