Implementing My Tasks “Read Only Custom Fields”

Using Project Server 2007’s Reporting Database and SQL Server Reporting Services 2005

The attached report illustrates how to safely query the RDB to allow team members to see a read only view of task assignment data. We’ve picked a random task custom field to illustrate the concept. I’ve deliberately defined the report to use “drill down” by Project Name to mimic the My Tasks grid behavior and to (UI) scale for users with lots of task assignments.

The figure below illustrates the My Tasks web part placed above the Report Viewer web part which is displaying the sample report.

Customizing and Installing the Report

1. Use the SQL Server 2005 Business Intelligence Workbench (available with SQL Server 2005) to customize the report to add the fields and custom fields that you want to display to your users.
   
2. Customize the report’s data source to connect to your Reporting Database.
   
   Note that when you deploy this report to the server you should ensure that the data source is customized with a cached userid and password that has access to read the Reporting Database. These credentials are used to avoid prompting the user for credentials and are stored securely in SQL Server.
   
    
   
3. Add the Reporting Services Report Viewer web part to the My Tasks page and edit the web part properties to point to the report.
   
   
   
   Note the non-default web part settings for the report viewer:
   
   - Tool Bar: Small (the user needs to be able to enter the date filter)
   - Fixed Height: 12 Inches

Notes on Implementation

The report has two parameters that are used in the query:

1. (Hidden) UserAccount is the userid – this is used to get the caller’s ResourceUID and hence filter assignments correctly, this should never be “unhidden” as the user could then enter any userid with the risk of information disclosure.
2. TimeFilter – defaults to two weeks previous from “now”, but can then be set by the caller to any date via the calendar picker. It filters out all task assignments that started before that date. [Note that you could get creative here and add different/additional filters if you want]. Use Report/Report Parameters to edit the default setting if required.
3. Note that the query filters out “unpublished” task assignments – when a project is published its data moves to the Reporting database. Task assignments only move to the My Tasks “statusing” tables if the Project Manager has decided that they be published.

Other notes on deployment

To maximize performance we avoided use of the _Userview SQL Views and referenced the underlying tables. If you want to access Task Assignment custom fields (the example show Task Custom Fields) then the query in the report will need to be reworked slightly, this should be trivial.

The RDB is only minimally indexed. We strongly recommend the addition of secondary indexes on the following tables:

Msp_Epm_Resource (Composite on ResourceNTAccount, ResourceUID)

Msp_Epm_Assignment (Composite on ResourceUID, AssignmentStartDate)

Finally…

And if you are delivering a small sub set of custom fields you could consider an indexed view over those fields to maximize performance.

Patrick Conlan