#Microsoft Community Contributor Award 2011 – Thanks

August 31, 2011 at 2:57 pm | Posted in Personal | Leave a comment
Tags:

Just a quick post today, I am pleased to announce that yesterday I was awarded with the Microsoft Community Contributor Award 2011.

Report on Project Server baseline save dates? #ProjectServer #PS2003 #PS2007 #PS2010 #MSProject #EPM

August 20, 2011 at 11:16 pm | Posted in Customisation, Functionality | 2 Comments
Tags: , , , , , ,

Quite often clients want to report on the dates when the baselines were saved. This information is not available out of the box in the databases using T-SQL. This post explains how to get this data into the database so that is accessible using T-SQL in the Reporting database.

Firstly we need to create 11 Project level enterprise custom text fields to store the baseline save dates. For the purpose of this post not all custom fields have been created, the custom fields in this example are Baseline0saveddate, Baseline1savedate and Baseline2savedate. Next add a VBA macro into the Enterprise Global that copies the baseline save dates to the enterprise custom fields created above. The following macro is just an example cut down version to copy the baseline save dates to the custom fields. This cut down version also only includes the first 3 baseline save dates.

Private Sub Project_BeforeSave(ByVal pj As Project)
ActiveProject.ProjectSummaryTask.Baseline0saveddate = ActiveProject.BaselineSavedDate(pjBaseline)
ActiveProject.ProjectSummaryTask.Baseline1saveddate = ActiveProject.BaselineSavedDate(pjBaseline1)
ActiveProject.ProjectSummaryTask.Baseline2saveddate = ActiveProject.BaselineSavedDate(pjBaseline2)
End Sub

The macro above will work for Project 2003, 2007 and 2010.

If a baseline value is not set NA will be set in the associated custom field. Once the macro has been added to the enterprise global, save the changes then close Project Professional. Re-launch Project Professional and open a project that has baselines set. Insert the new Project level custom fields into the view, click Save and you will see either the baseline save date appear in the associated custom field or NA if a baseline has not been set for a particular baseline. Once this project is published this information will be available to report on in the Reporting database. For example, the following T-SQL will give you the baseline save dates based on this post example:

use            PWA_Reporting
select        ProjectName
        ,    Baseline0saveddate
        ,    Baseline1saveddate
        ,    Baseline2saveddate
from        dbo.MSP_EpmProject_UserView

The T-SQL above will work for Project Server 2007 and Project Server 2010.

As mentioned above the VBA code is just as an example and would need to be improved for a production environment.

#ProjectServer 2010 #Project Sites #SharePoint 2010 Workflows #PS2010 #SP2010

August 11, 2011 at 12:20 pm | Posted in Functionality | Leave a comment
Tags: , ,

By default out of the box you will only see the three state workflow in the Project Sites, to make use of the other default SharePoint 2010 workflows you can activate the “Workflows” Site Collection Feature on the PWA site. Once this is enabled you will see the following workflows on top of the default three state workflow:

  • Collect Signatures – SharePoint 2010
  • Approval – SharePoint 2010
  • Collect Feedback – SharePoint 2010

To activate the Workflows feature navigate to your PWA site with the Project Server Administrator account and click Site Actions > Site Settings > Site Collection Features. Scroll down to the bottom of the page and click the Activate button next to the Workflows feature:

image

Custom #ProjectServer #Project Site / Workspace view in PWA #PS2010 #PS2007 #EPM #MSProject #SSRS

August 5, 2011 at 3:12 pm | Posted in Add-on, Customisation | 6 Comments
Tags: , , , ,

Quite often I hear clients asking for a filtered view of the Project Server project workspaces / project sites, unfortunately out of the box this is not possible. There are two main options for this, the first is to create your own custom Project Sites web part or the second option is to create an SSRS report and add this to a page viewer web part. In this blog post I will look at the second option. For the purpose of this post I will reference the sites as Project Sites (Project Server 2010) but the same will work for Project Workspaces (Project Server 2007). In this example I will show two different types of report to give an idea of how flexible the view / report can be. The examples will focus around a project level custom field – Project Status, one example report will filter the Project Sites based on the project level field selected the other example will show all project sites grouped by the project level field.

Firstly create your dataset for the report. I generally use SQL Server Management Studio to write the T-SQL queries rather than using the dataset dialogue box in Visual Studio / Report Builder 3.0. As always with Project Server 2007 / 2010 only use direct SQL queries on the Reporting database, below is the SQL query that I will use for the ProjectSiteFiltered dataset.

Project Site Filtered – SQL query

select        ProjectName as [Project Name]
        ,       ProjectWorkspaceInternalHRef as [Project Site]
        ,       ProjectOwnerName as [Project Owner]
        ,       ProjectStatus
from         dbo.MSP_EpmProject_UserView
where       ProjectType = 0 and ProjectStatus = @ProjectStatus
order by    ProjectName asc

The T-SQL will be the same for the grouped by report by without the ProjectStatus field in the where clause.

Another T-SQL query is needed to populate the report parameter values, the query below will be used for the Project Status dataset:

Project Status values – SQL query

select        distinct ProjectStatus
from         dbo.MSP_EpmProject_UserView
order by    ProjectStatus asc

Open SQL Server Business Intelligence Studio as start a new Report Server Project, give the Project a name such as ProjectSiteView.

In the Solution Explorer pane add a new data source that uses the PWA_Reporting database.

image

In the Solution Explorer pane right click on the Reports folder and click Add > New Item then select Report and give the report a name such as ProjectSiteFiltered.rdl

image

In the Report Data pane, click New > Data Source… Give the data source a name such as PWA_RDB, and set it to use the shared data source created above.

image

In the Report Data pane, click New > Dataset… Give the dataset a name such as ProjectSiteFiltered, copy and paste the Project Site Filtered SQL query into the query box.

image

Create another data set for the Project Status values.

In the Report Data pane, you should see a parameter called @ProjectStatus, this is created automatically because the SQL query in the ProjectSiteFiltered dataset contains @ProjectStatus in the where clause. Set the @ProjectStatus parameter to get values from the project status values dataset.

Design the report as required.

Set an action on the Project Name field to go to a URL, set the URL to be the Project Site field.

Upload the report to the report server then add the report to the PWA homepage using a page viewer web as seen below, select a value from the Project Status parameter and view the report:

image

Click the Project Name and this will take you to that Project Site.

Below you can see the Project Sites grouped the Project Status field. To see the projects, expand out the Project Status value then click the Project Name to go to the Project Site.

 image

The reports created above are just examples to show how easily a new Project Site view can be put together without any custom development.

Slipstream #Project Professional 2010 updates #MSProject #PS2010 #PS2007 #ProjectServer #MSOffice

August 2, 2011 at 6:29 pm | Posted in Installation | Leave a comment
Tags: , , , , , ,

Following on from my previous post Customising the Project Professional install, this post looks at slipstreaming the Project Professional 2010 updates into the Project install media. This simplifies the install as there is just one install file to run. This post will look at slipstreaming Service Pack 1 and the June 2011 Cumulative update into the Project 2010 install media. The same process applies for Project Professional 2007 but use the 2007 updates. This process also applies for other Microsoft Office applications with the relevant updates.

Firstly download the following updates:

  • Project 2010 Service pack 1 – KB 2460052
  • Project 2010 June 2011 Cumulative Update – KB 2536590

For the purpose of this post, I have both of the updates on the root of the C:\ drive, if you have the updates in a different location please update the commands to reflect the file location.

Create an Updates folder on the root of the C:\ drive

Open Command Prompt, navigate to the root of the C:\ drive and type the following command to extract Project 2010 SP1 to the new Updates folder:

project2010sp1-kb2460052-x86-fullfile-en-us.exe /extract:C:\Updates

image

Press Enter and accept the license agreement in the pop up box, you will then see the files extracting dialog box

image

Once complete you will then see installation complete message:

image

Click OK then you will see the extracted files in the C:\Updates folder:

image

Copy the contents of the C:\Updates folder and paste the contents into the Updates folder in the install media.

image

Once the files have been copied to the Updates folder in the install media for Project Professional 2010, delete all of the files from the C:\Updates folder so that you are ready to extract the Project 2010 June 2011 CU.

Open Command Prompt again and navigate to the root of the C:\ drive then type the following command to extract the Project 2010 June 2011 Cumulative Update to the Updates folder:

project2010-kb2536590-fullfile-x86-glb.exe /extract:C:\Updates

image

Press Enter and accept the license agreement in the pop up box, you will then see the files extracting dialog box appear followed by the  installation complete message:

image

Click OK then you will see the extracted files in the C:\Updates folder:

image

Copy the contents of the C:\Updates folder and paste the contents into the Updates folder in the install media.

image

Now the updates have been extracted to the Updates folder in the installation media for Project Professional 2010, the updates will be applied when Project Professional 2010 is installed.

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