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

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.

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

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.

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.

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:

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.

 

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

Advertisement