Reporting Database Extensions: Local Custom Fields Custom Code

Introduction

This custom solution builds on the Project Server 2007 server-side event model to cache a report-friendly copy of Project Local Custom Fields in the Reporting database. The following data is cached:

• Task Text Custom Fields (with and without lookup tables) by Project
• Lookup table values by Project
• Task Outline Code values by Project

The design pattern used means that addition of other custom field types is a trivial matter requiring edits to a SQL Server stored procedure, avoiding event code change.

Some customization is required to install this solution – refer to the “Customization” section.

Solution Components (Local Custom Fields in RDB.zip)

• PostReportProject – Event Handler for the three Reporting events that fire when handling projects, this is shipped as an executable and as source in Visual Studio 2005 form.
• CustomLocalCustomFields.sql – Stored Procedure to process local custom fields
• LocalCustomFieldsTables.sql – Table definitions to hold local custom field data
• EventParameters.ini – INI file containing RDB connect string

Customization

There are two main customizations required:

1. EventParamters.ini - RDB Connect String
   
   Alter the string to point to the RDB database server and database
   
2. LocalCustomFields.sql
   
   Search for “CUSTOMIZE”, there are three occurrences where “[pwa_Published]” should be replaced with the name of your Published database.
   
   Note that if the Reporting database is on a separate server you will need to define a linked server and use a four part name for the Published tables.

There is an additional customization – the EventParameters.ini file is expected in the C:\Windows directory – this location can be changed by editing the event handler code (“PostReportHandlers.cs”) and recompiling & re-gac’ing

Installation

** Do not install the event handler until the SQL Components have been installed and tested, and the .INI file has been placed in (default) C:\Windows directory on each application server **

It is important that these steps are followed in the order below.

1. Install Local Custom Field Cache tables (do this once)
   
   Connect to the RDB and run the LocalCustomFieldsTables.sql to create the cache tables.
   
2. Install customized Stored procedure (do this once)
   
   Connect to the RDB and run the customized CustomLocalCustomFields.sql to create the main stored procedure.
   
3. Test SQL Components
   
   Select a project (you can get its ProjectUID from MSP_EPMProject_UserView in the Reporting database) with local task text custom fields, and/or local task text custom fields connected to a lookup table and local task outline codes.
   
   Replacing the guid in the statements with the ProjectUID guid run the following commands to test the data in the local custom field cache.

exec Custom_LocalCustomFields 'f799fad0-f896-4731-90ab-2fa740f43e88',2 -- Create (1st publish) 
select * from dbo.Custom_TextCustomField -- Mix of nulls, text fields and lookup table guids 
select * from dbo.Custom_OutlineCode -- Mix of nulls & lookup table guids 
select * from dbo.Custom_ProjectLookupTable -- Contains all the lookup table (value list entries) 
exec Custom_LocalCustomFields 'f799fad0-f896-4731-90ab-2fa740f43e88',3 -- Normal publish 
exec Custom_LocalCustomFields 'f799fad0-f896-4731-90ab-2fa740f43e88',1 -- Delete

4. Install customized INI file
   
   Copy the customized EventParamters.ini to the (default) C:\Windows folder on each application server (not the SQL Server)
   
5. Recompile & GAC Event handler on all application servers
   
   Make any changes to the event code (e.g. ini file path) and recompile. Copy to each application server. On each application server Start: Run: assembly to load the Global Assembly Cache(GAC) view, copy the PostReportProject.dll into the Assembly view to “gac” it. (See the appendix on Event Handlers for an alternate method using GACUTIL from the Net 2.0 Framework SDK)
   
   In the assembly view right mouse the installed dll and obtain its key, use this in step 6.
   
   

Reporting	ProjectChanged
Reporting	ProjectCreated
Reporting	ProjectDeleted



6. Define Event handler in PWA
   
   From the Server Settings page, select Server-Side Event Handler Configuration
   
   For each of the three Reporting events add the event handler, the three events are listed in the figure above.
   
7. Sample Event settings:
   
   
   
   Assembly name field sample:
   
   PostReportProject,Version=1.0.0.0,Culture=neutral,PublicKeyToken=e0fd07f9ec9c9c3f
   
8. Test the Event Handler (end to end)
   
   The event handler appendix article explains how to debug event handlers.

Appendix 1: Working with Project Server Event Handlers

Project Event Handlers can be installed by a variety of methods – these are documented in the reference article at: http://msdn2.microsoft.com/en-us/library/ms469450.aspx)

SDK (contains GACUTIL.EXE and SN.EXE) at:

** Note use the OS appropriate version 32 or 64 bit **

X86: http://www.microsoft.com/downloads/details.aspx?FamilyID=fe6f2099-b7b4-4f47-a244-c96d69c35dec&DisplayLang=en

X64: http://www.microsoft.com/downloads/details.aspx?familyid=1AEF6FCE-6E06-4B66-AFE4-9AAD3C835D3D&displaylang=en