#ProjectServer data snapshot for reporting #PS2010 #MSProject #SP2010 #Excel #SSRS #SQL

April 27, 2012 at 4:33 pm | Posted in Administration, Configuration, Customisation, Functionality, Reporting, T-SQL | Comments Off on #ProjectServer data snapshot for reporting #PS2010 #MSProject #SP2010 #Excel #SSRS #SQL
Tags: , , , , , ,

Quite often there is a requirement for some form of data snapshot in Project Server for trend analysis reporting. This post will give a simple explanation of how this can be achieved using the Project Server Reporting database and a SQL job. This example just captures some key project metrics such as Actual Work, Actual Cost, Status Date and a couple of custom field values each month to demonstrate the process.

Below is the first SQL query that will need to be run against the Project Server Reporting database to create the first snapshot:

select        ProjectName
        ,    ProjectUID
        ,    ProjectType
        ,    ProjectActualWork
        ,    ProjectActualCost
        ,    ProjectStatusDate
        ,    [PM TEST]
        ,    [PM TEST 2]
        ,    DateName(month,GETDATE()) + ‘ ‘ + DateName(year,GETDATE())  as SnapshotDate
into        CustomSnapshotProjectData
from        dbo.MSP_EpmProject_UserView

The query above will get the data for the fields listed in the select statement from the MSP_EpmProject_UserView and insert the data into the CustomSnapshotProjectData table. Update the query to get the fields / data that you require to be snapshotted.

To add the next months data the SQL query will look slightly different as the CustomSnapshotProjectData table already exists. This time we need to insert into rather than select into, the query can be seen below:

Insert into CustomSnapshotProjectData (ProjectName, ProjectUID, ProjectType, ProjectActualWork, ProjectActualCost,ProjectStatusDate,[PM TEST], [PM TEST 2],SnapshotDate)
select        ProjectName
        ,    ProjectUID
        ,    ProjectType
        ,    ProjectActualWork
        ,    ProjectActualCost
        ,    ProjectStatusDate
        ,    [PM TEST]
        ,    [PM TEST 2]
        ,    DateName(month,GETDATE()) + ‘ ‘ + DateName(year,GETDATE())  as SnapshotDate
from        dbo.MSP_EpmProject_UserView

This can either be run manually each month at the specified time or a SQL job can be created to execute the SQL query automatically each month.

The contents of the CustomSnapshotProjectData table can be seen below, please note the data in my test system is not ideal for reporting / snapshotting:


This data can be accessed using the usual reporting tools such as Reporting Services and  Excel / Excel Services.

As mentioned this is just a simple example to show how quickly and easily snapshotting can be set up for Project Server 2007 and 2010.

#ProjectServer and #SharePoint 2010 April 2012 Cumulative Update #PS2010 #SP2010 #MSProject

April 25, 2012 at 8:48 pm | Posted in Administration, CU, Information | Comments Off on #ProjectServer and #SharePoint 2010 April 2012 Cumulative Update #PS2010 #SP2010 #MSProject
Tags: , , , ,

The Office 2010 April 2012 Cumulative Updates are now available, please see the links below:

Project Server 2010 Server Roll up package April 2012 CU (Recommended):
Project Server 2010 April 2012 CU (Included in the Server Roll up package):

Project 2010 April 2012 CU:
As always, test these updates on a test environment before deploying to production.

#ProjectServer 2010 views export to #Excel AutoSum #PS2010 #MSProject

April 20, 2012 at 4:18 pm | Posted in Administration, Functionality, Information | Comments Off on #ProjectServer 2010 views export to #Excel AutoSum #PS2010 #MSProject
Tags: , , ,

While on client site recently a query was raised regarding using the AutoSum function in Excel for the exported Resource Availability view, this didn’t work due to the hours containing an h. For the AutoSum functionality to work only numeric characters can be in the range. There is a very quick and simple method to remove the h.

Export to Excel:



As you can see the hours contain the digits as well as h, to remove h just use find and replace, typing h in the find what field and leave the replace with field empty. Firstly select the dataset / range as below:


Now press Ctrl + H and enter h in the find what field and leave the replace with field empty:


Click Replace All and the h will be removed:


Any calculations will now work including AutoSum:


Check server times for #SharePoint / #ProjectServer farms #PS2010 #SP2010 #MSProject #PowerShell

April 13, 2012 at 4:40 pm | Posted in Administration, Installation, Performance, PowerShell | 2 Comments
Tags: , , , ,

For a server farm it is important that all server times are in sync, for Project Server this is key. For example if the Application server has a different time to the SQL server you might see jobs in the Project Server queue that are in sleeping state. A useful PowerShell script to check all of the server times in the SharePoint farm can be seen below:

The script can be downloaded from the script center here:


#Script needs to be run on a SharePoint server
#Run script with account that has admin access to all servers
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA 0
$servers = (Get-SPServer) | foreach {$_.Address}

foreach($server in $servers)

$time = Get-WmiObject Win32_LocalTime -computer $server  -EA 0

$hour = $time.Hour
$minute = $time.Minute
$second = $time.Second
Write-Host “$server current time is $hour : $minute : $second”

Write-host “If the server times are not in sync please adjust the time settings. Press any key to continue”
$null = $host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)

An example output can be seen below:


#ProjectServer 2010 environment rollover steps #PowerShell #PS2010 #MSProject #SP2010

April 10, 2012 at 6:56 pm | Posted in Administration, Functionality, Migration, PowerShell | 2 Comments
Tags: , ,

This blog post will detail the PowerShell commands required to carry out an environment rollover using Windows PowerShell where possible. The steps below use the 5 database approach to rollover over the Production environment to the Test / Development environment.

For more details on the Project Server database restore please see Andrew’s blog posts found below:



As a prerequisite I would recommend taking full backups of the Test / Dev farm to enable the environment to be rolled back if required. Use your usual farm backup procedures.

Test / Dev environment Prep

Firstly connect to the Test / Dev Application server and launch the SharePoint 2010 Management shell. If you are not familiar with the management shell it can be found in the Microsoft SharePoint 2010 Products folder – Start | All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010 Management Shell.

Type the following:

Remove-SPProjectWebInstance –Url <URL of Test / Dev PWA site that you want to refresh with the Production data and config>



Press Enter and the command will remove the existing Test / Dev instance


You can check in Central admin in the Project Server Service Application to check that the PWA site has been removed:


Using the SharePoint 2010 Management Shell detach the existing content database from the web application that hosted the PWA site collection that was removed in the previous step.

Type the following:

Dismount-SPContentDatabase “<Database name here>”



Press Enter:


Press Y to confirm and Press Enter.

Test / Dev environment SQL Prep

Take a backup of the 5 databases from the Production environment and copy these over to the Test / Dev SQL server and restore the databases. The databases required are:

  • Content database that contains the PWA site and Project Sites
  • ProjectServer_Archive
  • ProjectServer_Draft
  • ProjectServer_Published
  • ProjectServer_Reporting

Make a note of the database names used when these databases are restored as they will be required later.

Test / Dev environment configuration

Using the SharePoint Management Shell, attach the restored content database to the web application that will host the PWA site collection.

Type the following:

Mount-SPContentDatabase “<content database name restored in previous step>” -DatabaseServer “<Test / DEV SQL Server>” –WebApplication <web application URL>



Press Enter:


Now using the SharePoint 2010 Management Shell provision the PWA site collection in the web application where the database was just attached to and using the 4 Project Server databases that were restored previously. Please note, use the same PWA path name used in Production. For example if the instance is called /PWA in Production, use /PWA in the command below on the Test / Dev environment.

Type the following command:

New-SPProjectWebInstance -Url “<web application URL + PWA path>” -AdminAccount “<Project Server Administrator account> -PrimaryDbserver “<Test / Dev SQL Server>” -PublishedDbname “<ProjectServer Published database>” -ArchiveDbname “<ProjectServer Archive database>” -DraftDbname “<ProjectServer Draft database>” -ReportingDbserver “<Test / Dev Reporting SQL Server>” -ReportingDbname “<ProjectServer Reporting database>”



Press Enter:


You can check the provisioning status of the PWA site using PowerShell or in Central admin in the Project Server Service Application to check that the PWA site has been created:

Type the following:

Get-SPProjectWebInstance –URL <PWA URL> | Select ProvisioningStatus



Or in Central Admin:


Post Provisioning

The Project Sites will need to be relinked using the “Bulk Update Project Sites” functionality in Server Settings on the newly provisioned Test / Dev PWA site.

The Project Server timer jobs (Resource capacity, Daily Scheduled backup and Cube build – also update the SQL AS server / cube name) will need to have the scheduled times modified on the Test / Dev system so that the Timer Jobs are recreated / relinked in Central Admin on the Test / Dev environment.

The Excel services reports will need to be updated to use the ODC files from the Test / Dev environment as they will currently point to the Production ODC files. This is done by opening the Excel reports in Excel, changing the ODC file then saving the file back to the library.

Create a free website or blog at WordPress.com.
Entries and comments feeds.