This blog focuses on customizations and programming for Project Web App, Project Server, Project Professional and Project Standard. Includes User Interface (UI) customizations, Project Server Interface (PSI) and Visual Basic for Applications (VBA) Programming. It also covers Business Intelligence.
A number of people have asked for entity-relationship diagrams of the Reporting database (RDB) for Project Server 2007. The attached RDBSchema.zip file contains RDB Schema.vsd,a Microsoft Office Visio file with the main tables and views that most people use to make reports. The diagram pages are formatted for Tabloid (B)-size paper, 11 x 17 inches.
The pages are:
· EPM User Views: schemas of the Project, Resource, Task, Assignment, and related ByDay user views.
· EPM Relationships: a simplified E-R diagram of the Project, Resource, Task, Assignment, and related ByDay tables.
· Timesheet Tables
· SharePoint Data: tables and user views for issues, risks, deliverables, and list item associations.
The diagrams were created using Visio Professional 2007. Visio Standard doesn’t include the database capabilities. You can use Visio Professional 2003 SP2 or Visio Professional 2007 to reformat the pages for a larger printer or rearrange and create additional E-R diagrams. The first page, in particular, could use a larger page size to expand the views (MSP_EpmTask_UserView has a 4.8 point font size to fit on an 11 x 17 page). To see the list of all 108 tables and views in the RDB, click the Visio Database menu, click View, and then click Tables and Views. Create a new page and drag items from the Tables and Views pane to the page.
If you don’t have one of the required versions of Visio, you can use the free Visio 2007 Viewer to see the diagram pages in Internet Explorer, although printouts of the large pages with the Visio Viewer are not as good as printing with Visio.
The Project 2007 SDK download includes the HTML Help version of the RDB Schema reference, pj12ReportingDB.chm. In the Visio diagram, the Notes fields for the tables, views, and columns include the same comments that are in the pj12ReportingDB.chm (with an update for the MSP_WssListItemAssociation table). To see the notes in Visio, right-click a table or view, and then click Database Properties. Click Notes in the Categories list to see the table or view notes; or click Columns to see the notes and other properties of all the columns in that table. You can modify the RDB to add your own tables, views, and columns for custom reports, and then add notes and custom content in the RDB Schema diagram with Visio.
Jim Corbin has passed along this great post:
When you impersonate a user in an application for Project Server 2007, you adopt the global and category permissions of the impersonated user. This article shows how to develop a Web application for Project Server that uses impersonation. The attached pj12Impersonation.zip file includes a complete Web application that allows you to log on Project Server using Forms or Windows authentication, checks your permissions for listing and creating projects, and then lets you impersonate any other Project Server user.
Important: The Impersonation Web application is an example for demonstration purposes only. The application is designed to run on a test installation of Project Server. It allows anyone with a Project Server account to log on, impersonate any other user, and create projects. To use any impersonation application on a production server, you must programmatically limit usage and add security checks that are appropriate for your organization.
Most applications for Project, including Project Professional 2007 and Project Web Access, call the Project Server Interface (PSI) Web services through the Project Web Access URL. Project Server enforces the security permissions of your account when you log on through an application that uses the Project Web Access URL. Impersonation requires direct calls to the PSI through the Shared Service Provider (SSP) Web application that hosts Project Web Access.
The Project 2007 SDK includes the section Using Impersonation in Project Server with the article How to Write a Simple Impersonation Application. The ProjTool application in the SDK download also uses impersonation.
The following figure shows the Impersonate page in the Web application, which indicates the identity of the application user. The application user can log on Project Server using Windows authentication; in the figure, the user has clicked Forms for the Authentication Type and logged on a Project Server user named Joe. Joe has the NewProject global permission to create a project, but is denied the ManageQueue permission necessary to execute the ReadProjectList PSI method. If you (as the application user) select Joe in the Select User drop-down list and then click Impersonate, you would run the application with Joe’s permissions. If you click List Draft Projects, the application would return an exception because Project Server does not allow Joe to use ReadProjectList. If you check the Use ReadProjectStatus() checkbox while impersonating Joe, the application would call the ReadProjectStatus method instead, and Joe could get the list of draft projects.
In the figure, the logged-on user Joe is impersonating the Administrator user, who does have the ManageQueue permission. Therefore, Joe can use the ReadProjectList method even though his own account does not have permission to do so. The application also enables an impersonated user (who has the NewProject permission) to create and publish a project, and then shows the new project in the list. The Draft Projects grid shows up to six projects and dynamically creates additional grid pages as needed.
To install the Impersonation Web application:
1. Create a directory for the source files on your test Project Server computer, for example, C:\Project\.
2. Unzip all of the files in pj12Impersonation.zip to C:\Project\. The top-level folder in the zip file is named Impersonation, so the local directory for the Web application is C:\Project\Impersonation.
3. Using Internet Information Services (IIS) Manager on your Project Server computer, create a top-level Web site named, for example, Impersonation. Use the local path you created in Step 1 (C:\Project\Impersonation). Allow script access and executables. Disable anonymous access (use Integrated Windows Auth only). The Impersonation Web site can't be a SharePoint site, so set the port to something besides the ports that Project Web Access and Windows SharePoint Services use, such as 5636. Project Web Access typically uses port 80 for Windows authentication and port 81 for Forms authentication. Your Impersonation site URL would therefore be the following: http://ServerName:5636
4. The Impersonation Web site needs to run under a service account that is trusted by Project Server. Create a new application pool in IIS, for example, ImpersonationAppPool. On the Identity tab of the ImpersonationAppPool Properties dialog box, set the Configurable property of the Application Pool Identity to the same user account and password for the Project Web Access site administrator. To find the user account for configuring Project Web Access, do the following:
a. Open the SharePoint 3.0 Central Administration site, and click Application Management.
b. On the Application Management page, click Create or configure this farm’s shared services.
c. Click the name of the SSP where Project Web Access is installed. For example, click SharedServices1 (Default).
d. On the Home page of the Shared Services Administration site, click Project Web Access Sites.
e. On the Manage Project Web Access Sites page, pause the mouse pointer over the site instance you want, click the down-arrow, and then click Edit.
f. On the Edit Project Web Access Site page, use the value in the Administrator Account text box. For example, set the Application Pool Identity to domain\pwaAdminName.
5. In IIS Manager, right-click the Impersonation Web site, click Properties, and then click the Home Directory tab. Set the Application pool value to the ImpersonationAppPool you created in Step 4.
6. On the Impersonation Web site Properties page in IIS, click the ASP.NET tab, and then set the ASP.NET version to 2.0.50727.
7. In IIS Manager, right-click the local computer name, and then click Properties. Click Enable Direct Metabase Edit. Then use Notepad to open the metabase.xml file in %systemroot%\system32\inetsrv. Search for the site name within an IISWebServer tag. Add the attribute NTAuthenticationProviders="NTLM”. For example, following is the complete element for the new Impersonation site.
<IIsWebServer Location ="/LM/W3SVC/784768436"
8. Restart IIS.
9. Copy the following files to the Bin subdirectory in the Impersonation Web site:
· Microsoft.Office.Project.Server.Library.dll (copy from C:\Program Files\Microsoft Office Servers\12.0\Bin)
· Microsoft.SharePoint.dll (copy from C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI)
10. Start Visual Studio 2005, and then open the Impersonation Web site from the Local IIS. When you click Save All, save the solution file as Impersonation.sln in C:\Project\Impersonation, for easy access.
11. The Web application needs the same validation key that Project Web Access uses for calling the PSI. The <machineKey> element in web.config sets keys to use for encryption and decryption of Forms authentication cookie data. The <machineKey> element allows developers to configure a validation key that performs message authentication checks on Viewstate data and Forms authentication tickets. <machineKey> can be declared at the computer, site, or application levels, but not at a subdirectory level . If you don't specify the correct <machineKey> attributes, then you get a Viewstate error. Set the <machineKey> attributes for your Project Server computer in web.config for the Impersonation site. Copy the complete <machineKey ...> line from the web.config file in your top-level site for Project Web Access (typically the default Web site), and replace the <machineKey … > line in the web.config file of the Impersonation site. The element should be all on one line, and a child of the <system.web> element, for example:
<machineKey validationKey="7C9DF8E41A03170EFF870936E0FED824859E541C6CF5768F" decryptionKey="EAAECB67BFF6AED2F4F812ADE1967CB6AB33A94A9FDE400C" validation="SHA1" />
12. In the App_Code subdirectory, the Global.asax.cs file has all of the Application_Start, etc., methods that normally are in Global.asax. There’s no particular reason that Global.asax.cs is in App_Code, except that is where Visual Studio prefers to put it. You could put Global.asax.cs under Global.asax, if you add the following attribute to the Application directive in Global.asax:CodeBehind="Global.asax.cs"
13. In ImpersonationUtils.cs, change the SERVER_NAME constant to use your server name. Change the name of the Shared Service Provider (SSP) from SharedServices1 to the correct name for your SSP, and change the SSP port value if necessary. Build the Web site.
14. Your Impersonation Web site should now work. Test the site on the local Project Server computer and on a remote computer. If the Impersonation application works on the local Project Server computer but not on a remote computer, it is likely that the IISWebServer tag (Step 7) is not correct. If you get an HTTP 401 (unauthorized) exception when you first try to log on with a Windows account, check that the application pool owner is set properly (Step 4).
When you log on Project Server with the Impersonation application, calls to the PSI use PROJECT_SERVER_URI for the value of the ResourceDerived.Url property. For Forms logon, the application sets the Url property to PROJECT_SERVER_FORMS_URI. During impersonation, the application sets the Url property to SSP_URI, for PSI calls to the ProjectDerived and SecurityDerived objects.
There are comments in the code that explain several parts of the application. For an explanation of the proxy and derived classes for the PSI Web services, see How to: Write a Simple Impersonation Application in the Project 2007 SDK. The logon routines in the ImpersonationUtils.cs file are based on code in the ProjTool sample; for more information, see Using the ProjTool Test Application.
In 2003, we released an Agile custom Project Guide, based on MSF for Agile Software Development methodology (which is scenarios driven , context based, Agile software development process). We have been able to get it working for Project 2007 as well. The Agile Project Guide allows the project manager to easily implement the Agile methodology for their projects, by outlining the Agile process and offering templates for process documentation.
The Agile Project Guide integrates with Microsoft Office Project 2007 in order to manipulate tasks within the project plan itself and provides context sensitive task/resource information in the project guide panel.
Here is what you need to do to get the Agile custom Project Guide working:
1. Download the attached zip file that contains the Agile Custom Project Guide files to your local machine
2. Launch Project 2007 client,
3. Enable the Project Guide from the View menu or just go to Tools | Options | Interface and select the “Display Project Guide” checkbox
From the Tools | Options menu | Interface tab
4. Select the “Use a custom page” radio button (from the “Project Guide Functionality and Layout Page:” section)
a. Click the Browse button and point to the mainpage.htm file, in the Agile Project Guide folder you’ve copied locally in step 1
5. Select the “Use custom content” radio button (from the “Project Guide Content:” section)
a. Click the Browse button and point to the AgileGuide.xml file, in the Agile Project Guide folder you’ve copied locally in step 1
6. Click Ok on the main dialog
7. You should now see the Agile Custom Project Guide
Due to the size of the Zip file, I need to split the file into two. This post has the first file. I will do a second post that will have the second file attached.
Happy Holidays! This post is from Nada Alwarid, a co-worker that is responsible for the project guide:
In Project 2007, class IDs have changed. Because of that, your custom Project Guide solutions cannot work until you update those class ID’s. In this post, I’ll outline what those changes are so you can get your custom solution working.
We have objects declared like this:
style="display: none">TextConv failed to load!
The id of the object is not important, its only used for referencing it within the HTML or Jscript code. The important part is the classID. It is used to actually look up that object in the list of ActiveX objects registered with the OS and load it.
These IDs have changed from Project 2003 to Project 2007.
The mapping from Project 2003 to Project 2007 is:
Project 2003 Class ID
Project 2007 Class ID
Document Event Object
Application Event Object
Also, you can download the default Project Guide files that has been attached to this post.
With the RTM build of Project Server, you may run into the following issue, where you are unable to add the Web Reference to the PSI:
The workaround is to click on the “Service Description” link. This will allow you to add the Web Reference to your project.
This is a continuation from my previous post. In this post, I will be illustrating how someone can import data from the Project reporting database from a SQL query. To began, we will need to open up Excel.
The first step is to create a connection to the Project reporting database:
1. Click on the Data tab
2. Click “From Other Sources”
3. Click “From SQL Server”
This will kick off a wizard that will guide you through the steps of setting up a connection to a SQL server. To run through the wizard you will need the following details:
Server Name –This is the server name of the server that is hosting the reporting database
Credentials – These are the credentials that are used to log on to the reporting database
Database Name – The name of the reporting database, which for me is ProjectServer_Reporting
You don’t have to connect to a table, since we are going to use the SQL query from the last post to get the data we need from the reporting database. Once we have created the connection, we need to configure the connection to use the SQL query. To change the configuration:
1. Click Connections from the Data tab
2. Select the Workbook Connection we just created
3. Click Properties
4. Select the Definition tab
5. Change the Command type to SQL
6. Copy and Paste the SQL Query into the Command text box
7. Click OK
8. Click Close
Now we need to have the data show up in our workbook.
1. On the Data tab, click on Existing Connections
2. Select the connection that we created for the workbook
3. On the Import Data dialog, just click OK
This will select the default settings and the returned table from our query will be returned to the selected worksheet.
Now we have the data from the reporting database populated in the Excel worksheet where we can manipulate the data:
Hope this helps!
Do you have something you’d like to see added or corrected in the Project 2007 SDK? Now you can add or edit Community Content on any page of the online SDK; all you need is a Microsoft Passport account.
For example, the following pages have Community Content:
· How to: Calculate Resource Availability in OLAP Cubes
· Equivalent PSI Methods
For more information, see MSDN Community Content FAQ.