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.

Advertisements

6 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Very Usefull, but I think there is something missing. The creation of the view called MSP_EpmProject_UserView, is it right?

    • Hi Christopher,

      Thank you for the feedback. The MSP_EPMProject_UserView is a default Project Server 2007 / 2010 SQL view in the RDB.

      Thanks

      Paul

  2. Hi Paul,

    Nice post. Exactly what was looking for. One question, the sample you have show the Project Status Parameter Panel above the report controls. The one I have is positioned on the right side when I surfaced it in Sharepoint. Can you tell me how you got that work? Thanks

    • Hi,
      Thanks for the feedback, I’m glad this post helped you. It depends if you use Reporting Services in Native or Integration mode, in my example SSRS is in Native mode and you probably have it installed in SharePoint Integration mode.

      Thanks
      Paul


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: