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.
Many people have asked, “How do I get started working with the PSI?” So I figured I would blog about creating a very simple application that interacts with the PSI. For this example, I will create a simple Windows Application that connects to Project Server and retrieves a list of resources for a given project.
Before we begin, it is important to realize that the PSI is made up of a number of Web Services. You can find a list of all the Project Server Web Service here: http://msdn2.microsoft.com/en-us/library/ms488627.aspx These Web Services are logically separated by business objects. For this example, we will be using both the Project and Resource Web Service.
To get started, open visual studio and create a Windows Application. The first step will be to add web references to the Project and Resource Web Services:
1. In the Solution Explorer, right click on References
2. Click on Add Web Reference.
3. Type in the URL to the Project Web Service.The URL for the web service is: http://SERVER_NAME/PWA_INSTANCE/_vti_bin/psi/project.asmxWhere SERVER_NAME is the name of the server Project Server is hosted on and PWA_INSTANCE is the name of the Project Web Access instance you want to connect to. _vti_bin/psi is where all the Project Server PSI Web Services reside. project.asmx is specific to the Project Web Service.
4. Give the Web Reference a name, such as WSProject
5. Click Add Reference
This will add a reference to the Project Web Service. Repeat the same steps again, except this time, on step 3 specify resource.asmx instead of project.asmx and in step for name the Web Reference WSResource.
Now that the Web References are step up, we can start to program! When I develop against the PSI, I always create a connection object to handle the various connections to the PSI. This allows me to reuse the connection class in a number of applications. Below is the source code of my connection class:
public class Connection
public const string Resource = "Resource";
public const string Project = "Project";
private static Dictionary<string, SoapHttpClientProtocol> WSDictionary;
private string ms_ProjServURL;
public Connection(string as_ProjServURL)
ms_ProjServURL = as_ProjServURL + "/_vti_bin/psi/";
WSDictionary = new Dictionary<string, SoapHttpClientProtocol>();
public SoapHttpClientProtocol GetWebService(string as_WSName)
if (WSDictionary.TryGetValue(as_WSName.ToString(), out lo_WS) == false)
Auth(Resource, new WSResource.Resource());
Auth(Project, new WSProject.Project());
lo_WS = WSDictionary[as_WSName];
public static void Reset()
private void Auth(string as_WSName, SoapHttpClientProtocol as_WS)
object  parameters = new object ;
parameters = ms_ProjServURL + as_WSName + ".asmx";
MethodInfo setUrlMethod = as_WS.GetType().GetProperty("Url").GetSetMethod();
parameters = CredentialCache.DefaultCredentials;
MethodInfo setCredentialsMethod = as_WS.GetType().GetProperty("Credentials").GetSetMethod();
catch (Exception ex)
This connection class has a dictionary of all the available PSI Web Services. With this implementation we are only concerned with the Project and Resource Web Service, so I have not included any other web services, but it would not be difficult to add additional PSI Web Services. It would only require a couple more lines of code for each Web Service. I will save that for another post.
The method that handles the setup to the Web Service is Auth. For each Web Service, we need to set the URL for the server that we want to connect to at run time. The URL was passed in with the constructor and this can be a different URL then the one used for the Web Reference. The second step is to set the credentials. For this example, we will only do NT authentication, but if there is interest, I can post an extension for Forms Authentication. Once that is done, we add the Web Service to the dictionary and it is ready to be used.
Next, I am going to add three controls, plus a few labels to the Windows form. The first control is a text box for the URL to the Project Server (txtURL), the second control is a drop down which will be populated with all the Projects the user has access to (cboProjects) and the third control will be a list box which will contain the names of resources that belong to the selected project (lstResources). Below is a screen shot of the form:
This form has two methods that contain all the calls to the Web Services. The first method is the Leave event for the URL textbox:
private void txtURL_Leave(object sender, EventArgs e)
conn = new Connection(txtURL.Text);
projWS = (WSProject.Project)conn.GetWebService(Connection.Project);
DataTable projList = projWS.ReadProjectList().Tables;
foreach (DataRow dr in projList.Rows)
cboProjects.Items.Add(new ProjListItem(dr["Proj_Name"].ToString(), new Guid(dr.ToString())));
if (cboProjects.Items.Count > 0)
cboProjects.SelectedItem = cboProjects.Items;
In this method, we instantiate the Connection object and pass in the URL for the Project Server. This is the URL that will be used at run time. Next, we get the Project and Resource Web Services from the connection object. This allows us to read the projects and populate the drop down with all the project names. I have created a basic object, ProjListItem, which contains the GUID and name of the project so that we can easily retrieve the GUID later on to get the list of resources. It is important to note here, that we are working with datasets. The majority of our Web Services have datasets that can be manipulated and sent back to the server to update the data.
The second method is the select index changed for the drop down list of project names:
private void cboProjects_SelectedIndexChanged(object sender, EventArgs e)
ProjListItem projItem = (ProjListItem)cboProjects.SelectedItem;
pds = projWS.ReadProjectTeam(projItem.getGuid());
DataTable dt = pds.Tables["ProjectTeam"];
foreach (DataRow dr in dt.Rows)
Here we retrieve the GUID for the selected project from the ProjListItem object that we populated the drop down list in the first method and we get the resources on the team by calling ReadProjectTeam method and passing the selected project GUID. ReadProjectTeam returns a dataset that contains a data table “ProjectTeam” that lists all the resources that are team members on the project.
So, now we have a little application that is able to connect to the server and retrieve data,
Pat Malatack (Program Manager, Microsoft Corporation) has a comprehensive series of webcasts on extending the JS Grid in Project Web App.
Note: This blog post was originally created Monday, April 19, 2010. The webcasts were missing for the past several months, but have been reinstated on the Microsoft Events site (https://msevents.microsoft.com/).
MSDN Webcast: Project 2010 JS Grid Extensibility: Project Web App (Part 1 of 3) (Level 400) Tuesday, April 20, 2010 1:00 P.M.-2:00 P.M. Pacific Time This is the first webcast in a three-part series on extending grid pages in Microsoft Office Project Web App. In this webcast, we focus on initializing custom grid code and interactions between the grid and the Project Web App Ribbon. In particular, we show you how to add additional functionality to the Project Center. MSDN Webcast: Project 2010 JS Grid Extensibility: Project Web App (Part 2 of 3) (Level 400) Thursday, April 22, 2010 1:00 P.M.-2:00 P.M. Pacific Time This is the second webcast in a three-part series on extending grid pages in Microsoft Office Project Web App. In this webcast, we focus on the use of events and delegates to interact with the timesheet grid in a way that allows for the addition of custom features and functionality to that page. MSDN Webcast: Project 2010 JS Grid Extensibility: Project Web App (Part 3 of 3) (Level 400) Tuesday, April 27, 2010 1:00 P.M.-2:00 P.M. Pacific Time This is the third webcast in a three-part series on extending grid pages in Microsoft Office Project Web App. In this webcast, we walk you through more grid customizations, including adding custom commands to the column header dropdown and custom row header states.
With the Project 2010 release, we have deprecated the Project Guide content. For the end user, this means there is no way through the UI to show the Project Guide. However, there is away to display the Project Guide through the OM. So if you have your own custom guide, you can still use it with a bit of work. This post will show you how to do this in three easy steps.
The first step is to write a method that turns the guide on and off. To do this, we need to have a guide, so I am going to use the one that we shipped in Project 2007. However, the default Project Guide files need some changes:
The folder structure should be flattened. All Project Guide files unzip to a subfolder named DefaultProjectGuideFiles.
The gbui:// protocol is removed. The custom "goal-based user interface" protocol and Project Guide resources are not installed with Microsoft Project 2010. For example, the following line in MainPage.htm: <script src="gbui://mainpage.js" language="JScript"></script>
... is changed to: <script src=mainpage.js" language="JScript"></script>
You can find the modified Project Guide files in the Project 2010 SDK download:
Once you have the guide downloaded and extracted into a directory, for example, C:\PG\DefaultProjectGuideFiles, you need to author the following method in VBA:
If (Application.DisplayProjectGuide = False) Then
OptionsInterfaceEx DisplayProjectGuide:=True, _
Update the ProjectGuideContent and ProjectGuideFunctionalLayoutPage to point your Project Guide content.
The next step is to create a button in the ribbon. In this example, I will add a button to the View tab:
Here is the code to do this:
Private Sub AddGuideRibbonButton()
Dim ribbonXML As String
ribbonXML = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"
ribbonXML = ribbonXML + " <mso:ribbon>"
ribbonXML = ribbonXML + " <mso:qat/>"
ribbonXML = ribbonXML + " <mso:tabs>"
ribbonXML = ribbonXML + " <mso:tab idQ=""mso:TabView"">"
ribbonXML = ribbonXML + " <mso:group id=""Project_Guide"" label=""Project Guide"" autoScale=""true"">"
ribbonXML = ribbonXML + " <mso:button id=""Project_Guide_Btn"" label=""Guide"" imageMso=""CategoryCollapse"" onAction=""Guide""/>"
ribbonXML = ribbonXML + " </mso:group>"
ribbonXML = ribbonXML + " </mso:tab>"
ribbonXML = ribbonXML + " </mso:tabs>"
ribbonXML = ribbonXML + " </mso:ribbon>"
ribbonXML = ribbonXML + "</mso:customUI>"
The last step is to hook up loading the button to an event. For this example, I am doing on the Project Open event, however, you may want to choose a different event based on your scenario.
Private Sub Project_Open(ByVal pj As Project)
At this point, we have a working Project Guide in Project 2010:
Note: Because the Project Guide is an add-in, Project 2010 adds the Tasks, Resources, Track, and Report drop-down menus to the Add-Ins tab on the ribbon.
Hope this helps,
I am very excited about the Visual Studio Beta 2 release, as it now has support for Project 2003 and Project 2007 add-ins! This makes coding and debugging managed code very easy in Project Client. It also makes it really simple to call into the PSI from Project Client to retrieve and update data on the server because you have the complete .Net framework at your disposal.
You can download Visual Studio Beta 2 to try it out for yourself:
I plan to do a few complex blog posts with regards to VSTO over the next couple of months, but to get started, here is a basic example:
1. Download and install VS Beta 2 from: http://msdn2.microsoft.com/en-us/vstudio/default.aspx
2. Open VS Beta 2 and Create a New Project selecting Project Add-in:
3. Give it a name and click OK
4. Add a Windows Form called SimpleDemoForm to the project:
5. A label to the form called: lblProjectName
6. In the ThisAddIn.cs class file add the following code in the startup method:
SimpleDemoForm frmSimpleDemoForm = new SimpleDemoForm(Application);
This code will load and show the form we created in step 4. Note that we pass the Application into the form's constructor.
7. View the code for the form and have it match the following:
using MSProject = Microsoft.Office.Interop.MSProject;
public partial class SimpleDemoForm : Form
private MSProject.Application Application;
public SimpleDemoForm(MSProject.Application Application)
this.Application = Application;
private void SimpleDemoForm_Load(object sender, EventArgs e)
lblProjectName.Text = Application.ActiveProject.Name;
Couple of things to note with this code sample. We have referenced Microsoft.Office.Interop.MSProject and we have passed the Application variable to the form so that we can manipulate the project in the same way we can manipulate it in VBA. In the form load method, we read the project name and show it on the form.
8. Build and run the application.
This should launch Project automatically and load the add-in for you, with no configuration work involved. You can also add any debug breaks to troubleshoot your code.
Here is what you should see when you build and run the add-in:
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.
SharePoint Server 2010 handles user authentication through claims processing, which is a new feature for SharePoint and Project Server. SharePoint handles both Windows authentication and Forms authentication for Project Server users. For authorization, you can use the ReadResourceAuthorization and SetResourceAuthorization methods in the Resource service of the PSI. Because you probably don’t often change security authorization settings for users, you would normally go to the Manage Users page in Project Web App to select a user and set the global and category permissions.
The Security business object in Project Server (with programmatic access through the PSI Security service) manages security groups, categories, templates, and the global Project Web App permissions. The Security service can add existing permissions or remove permissions from the sets available for Project Server users. However, the Security service does not have a method for creating a custom permission. For example, if you created a Project Server extension that updates a Siebel CRM system, you might want a custom permission that enabled users to use that extension.
In Office Project Server 2007, you can create custom global and category permissions by modifying security tables in the Published database. Custom permissions show in the PWA lists of permissions, where Project Server administrators can secure the 3rd-party extension the same way they secure other Project Server features. The Walkthrough: Creating and Using Custom Project Server Permissions article is the only SDK example where an exception is made for changing the Published database.
NOTE: The Project team would like some feedback on the importance of custom permissions. If you need to create custom permissions in Project Server, please respond to this post.
In Project Server 2010, that process for creating custom permissions still works as it did in Project Server 2007. In future versions of Project Server, no modifications to tables in the Published, Draft, or Archive databases will be supported. Custom permissions and secure links that rely on table modifications still work in Project Server 2010, but that process will be deprecated. As an alternative, you may have to create your own user interface to manage custom permissions, or use claims augmentation in a custom application. For more information, see Claims Provider.
For more information about Project Server security, including a discussion of global and category permissions, see the Project Server Security Primer in the Project Server 2007 SDK and Security and protection for Project Server 2010 in TechNet.
In Project Server 2010, Project developers were able to create Project Workflows using Visual Studio 2010. In Project Server 2013, we enabled creating the workflows with SharePoint Designer, which makes it much easier and faster to create Project Workflows. In that blog post, we showed how we have simplified the workflow creation for Project using SharePoint Designer 2013.
We are, however, still supporting creating Project Workflows with Visual Studio for the more complex set of workflows, and in fact, have also made it easier to create Project Workflows with Visual Studio 2012. Below, we are going to use a sample two-stage workflow to show how you can create workflows with Visual Studio 2012:
1. Creating the Workflow solution: File Menu>New>Project>Office/SharePoint>SharePoint solutions > SharePoint 2013 Project. Give this project a name, and hit OK:
In the customization wizard, enter the address of the PWA web you’d like this workflow to be published to. Then, pick the sandboxed solution option to limit this workflow to this particular PWA web:
2. At this point, the project you’ll see the empty canvas. In the Project Menu, click on the Add New Item, and from the Office/SharePoint tab, select Workflow, enter a name, and hit Add:
Then, in the customization wizard, pick Site workflow:
Then, pick the history list and the workflow tasks list from that site. We recommend that you use the default lists since a number of PWA UI entry points, use these default lists. Then, hit Finish:
3. Now, we need to set up the environment to use the Project Server activities. In the toolbox, right click and click on “add tab”, and call the new tab “project server”:
Then, right click on the “project server” tab and click on “choose items” from the menu, and you’ll see this dialog:
In the dialog click on Browse, and navigate to where the workflow dlls are located. They are usually located in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\WorkflowActivities You’ll see two activities dll there. Open the project server one (Microsoft.Office.Project.Server.WorkflowActivities.dll), and hit OK. You are now taken back to the “toolbox items” dialog, and highlights the selected corresponding activities. Hit OK to continue.
4. You might see a “sequence” in the canvas. Delete that, and from the toolbox, pick Flowchart and add it by dragging it into the main area. This flowchart will be the main container of all the stages of the workflow:
In the toolbox, click on Control flow, and add the sequence inside that flowchart. Throughout this sample workflow, we will use sequence to represent workflow stages in Visual Studio. This is similar to how SharePoint Designer handles each stage, i.e. each stage is equivalent to a separate sequence in Visual Studio:
Rename the sequence to “Create_Stage” by clicking on the “Sequence” and start typing to change the name. Drag the line from start to “Create_Stage” to connect them together:
5. Double click on the “Create_Stage” to drill into this sequence
a. Under project server in toolbox, add the “EnterProjectStage” and “ExitProjectStageGate” activities to the sequence. These two activities are required in any of the PWA stages in Visual Studio.
b. In the properties of “EnterProjectStage”, change the StageID to the Stage ID of the particular stage you’d want this sequence to represent. You can find the stage ID in the URL of that stage, and is available if you navigate to that stage in PWA Settings > Workflow Stages, and then click on the particular stage. Since stageID is a string, the ID should be provided in quotation marks.
c. Put another sequence between “EnterProjectStage” and “ExitProjectStageGate”. Essentially, everything in this sequence is what is represented in the text-based designer in SharePoint Designer stage definition.
d. From project server item in the toolbox, drop the “waitForProjEvent” activity in that sequence:
e. Change the EventName property to “OnProjectSubmit”. The other supported Event Names are “OnProjectCommit” and “OnProjectCheckIn”
6. In the breadcrumb, click on Flowchart to go one level up. Add another sequence after Create_Stage and call it Finished_Stage, and connect the wire from Create_Stage to the Finished_Stage:
7. Similar to the Create_Stage, add the EnterProectStage and ExitProjectStageGate activities to the sequence as well as the WaitForProjectEvent activity in the middle, and set the properties accordingly:
8. This completes building the workflow in Visual Studio. However, in order to make sure that the workflow can be properly published to the PWA, we need to make a few more changes in the xaml files of the project: From solution explorer, pick “Elements.xaml” under the workflow node
a. Replace the WSEventSourceGUID with the following so that the workflow is correctly identifies as a project workflow: <Property Name="WSEventSourceGUID" Value="5122D555-E672-4E5D-A7C4-8084E694A257" />
b. Inject the following properties under the “Url = WorkflowStartAssociation”: <Property Name="Microsoft.ProjectServer.ActivationProperties.ProjectId" Value="" /> <Property Name="Microsoft.ProjectServer.ActivationProperties.CurrentStageId" Value="" /> <Property Name="Microsoft.ProjectServer.ActivationProperties.RequestedStageId" Value="" /> <Property Name="WSEventContextKeys" Value="Microsoft.ProjectServer.ActivationProperties.CurrentStageId;#Instance
9. Now that everything is set, and the workflow is ready for publishing, click on the “Build Solution” under the Build menu, and then click on the “Deploy Solution” under the Build menu. The wsp file is now deployed to the site. You can also find a copy of the wsp file in the file system, under [project name]>bin>debug
Now, the workflow will show up in PWA. If you navigate to PWA Settings > Enterprise Project Types, and create a new Enterprise Project Type, you will see this workflow as one of the options in the workflow dropdown list.
For more information, see Getting started developing Project Server 2013 workflows in the Project 2013 SDK.
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.
Due to the size of the Zip file, I need to split the file into two. This post has the second file. The main post will have the first file attached.
Unlike the project Web Access “My Tasks” web part, the Statusing web service API restricts team member task assignment updates that combine with the create/reassign action to one per approval cycle. This means that once a create/reassign plus updated status is submitted it cannot be further updated until accepted or rejected by the Project Manager.
This limitation is caused by the absence of “Node Consistency” functionality in the API. This component ensures that the task is kept schedule consistent during team member updates. The project Server 2007 architecture placed this component on the Project Web Access web front end server, rather than in the Project Server Interface business object layer, so as to deliver a responsive user experience in the UI.
Unfortunately the application developer who is tasked with synchronizing status with an external system (such as an ERP or CRM system) has no means to detect this “blocked” status. We plan to add such a method in an upcoming release of Project Server. In the interim this documentation can be used to extend the Project Server Interface to add a web service/method to deliver the status update status.
Note: This document contains interim documentation for tables in the Published database of a RTM-edition Project Server 2007 installation. Entities in this schema are subject to change without notice. This documentation is made available on a “best efforts” basis, Microsoft Product Support Services have not been trained in its usage and will not offer technical support for issues related to custom queries against this schema. This schema should not be updated by custom code, any updates to the data may break Project client cache code.
This is documented in the following Project Server 2007 Software Development Kit article.
Note that any code will be required to connect to the Project Server 2007 Published database – in a single site farm this may safely be hard coded, however in a multi-site farm it is recommended that the developer implement a method for connecting to the correct published database, suggestions include:
All status updates are stored in the MSP_ASSIGNMENT_TRANSACTIONS table together with flags indicating the current state of the update. The flags necessary to locate a blocked task assignment are documented below.
There are four ENUM fields that govern state, these are:
So a blocked task assignment and will have ASSN_TRANS_STATE_ENUM = 1 (pending approval) and ASSN_TRANS_TYPE_ENUM = 2,3,4 (indicating a pending task assignment add/change)
SELECT TOP 1 ASSN_TRANS_STATE_ENUM
WHERE ASSN_UID = '617633E6-8B2A-4620-BCAD-F82A95AD398D' -- Assignment UID
AND ASSN_TRANS_TYPE_ENUM IN (2,3,4)
If the task assignment status update is not blocked then no data will be returned; if it is blocked then a scalar value of integer 1 will be returned.
See comments in the performance section before changing this query.
PSI Extensions are responsible for implementing their own security. In this case there are mitigations which mean that additional permissions checks may be unnecessary:
Note that it would be simple to make the call as the “interface user” and test for that in the PSI extension should further restrictions be required.
The above query has been optimized for performance – strictly speaking the query is stronger than required as only the second task assignment status update is blocked by the pending approval. However checking for this first pending update would require the addition of a self-join to the query which would reduce performance considerably. This would test for the above condition AND a pending task assignment status update (ASSN_TRANS_TYPE_ENUM = 0). Provided the work is added in the same ChangeXML payload that changes the task assignment, it is superfluous.
It is recommended that a query covering index be created on the MSP_ASSIGNMENT_TRANSACTIONS table to avoid both clustered index scans, secondary index scans or bookmark lookups as the table is indexed on a non-sequential UUID (so data access will be very random).
The following index definition is suggested:
CREATE NONCLUSTERED INDEX [CUSTOM_STATUS_SYNCH] ON [dbo].[MSP_ASSIGNMENT_TRANSACTIONS]
) ON [PRIMARY]
The sample query (above) uses a TOP 1 clause to further reduce the SQL Server results processing (either no rows or a scalar result set will be returned).
If you alter the query to return more data, or to use different search arguments then this index definition might need to change.
Note that creating a stored procedure and using output parameters would further improve performance.
Jim Corbin has written a draft article on how to customize e-mail notifications from Project Server:
Here is a draft of an article and source code that will be published in the Project 2007 SDK update late this year. The draft article Customizing Notifications.doc is in the attached NotificationsEventHandler.zip.
The article explains how to create an OnSending event handler for notifications, and shows the code for extracting relevant information from the notification XML data. If the event handler matches a notification type (specific alerts and reminders you want to customize), it transforms the XML data to a text or HTML e-mail using your custom XSLT file, sends the e-mail to the proper recipient, and then cancels the OnSending pre-event so that Project Server doesn’t send the default notification.
The article includes a discussion of how to extract the default XSLT files for notification e-mails from the Published database. The download includes the English language XSLT files, but you need to extract them for other languages. The article also includes a discussion of how to configure an SMTP virtual server for a test installation of Project Server.
Attached is my source code and power point presentation of today's webcast on custom fields and lookup tables:
We will be publishing the presentation to the web in the next few days. Once it is available, I will post a link to it.
The Project 2013 SDK download is updated. The conceptual, how-to, and reference documentation in the download closely matches the Project 2013 developer documentation online content published on MSDN.
The Project2013SDK.msi download file now installs properly on Windows 8 and Windows Server 2012.
The Project 2013 SDK download contains:
The top-level online landing page for the Project SDK is Project for developers (http://msdn.microsoft.com/project).
Want to try out Project 2013?
We have published preliminary version of the Scrum Solution Starter for Project 2010 on MSDN Code gallery http://code.msdn.microsoft.com/P2010Scrum please participate actively in the Discussion to notify us about any issue you may encounter or post any feedback!
Scrum is an iterative, incremental methodology for project management often seen in agile software development. Although Scrum was intended for management of software development projects, it can be used to run software maintenance teams, or as a general project/program management approach. There are 3 main items in Scrum: Product backlog: A product backlog is dynamic—Items may be deleted or added at any time during the project. It is prioritized—Items with the highest priority are completed first. It is progressively refined—Lower priority items are intentionally course-grained. Sprint backlog: A sprint backlog is a negotiated set of items from the product backlog that a team commits to complete during the time box of a sprint. Items in the sprint backlog are broken into detailed tasks for the team members to complete. The team works collaboratively to complete the items in the sprint backlog, meeting each day (during a daily scrum) to share struggles and progress and update the sprint backlog and burn down chart accordingly. Burn down: The sprint burn down chart is a publicly displayed chart showing remaining work in the sprint backlog. Updated every day, it gives a simple view of the sprint progress. It also provides quick visualizations for reference
A Scrum Master wants to use Project for the basics of running a sprint, including:
The skip to stage feature is designed to allow administrators the ability to restart a workflow and have it attempt to skip along the workflow until it reaches a particular stage. A common scenario for this functionality is when a project has progressed to a specific point in a workflow, but needs to be pushed back to a previous stage due to any number of reasons, such as, a new stage was inserted that needs to be addressed, certain fields need to be changed that are exposed only in previous stages, etc.
Project Server workflows, like SharePoint workflows, must always be executed linearly. They cannot begin execution at random locations within a workflow. Neither can they “jump” from one point to another point, unless coded to do so within the workflow. The skip to stage functionality cannot circumvent this limitation. As such, the issue with restarting a workflow and attempting to push it to a particular point is that any activities which “stopped” the workflow before will continue to do so again. For Example, activities such as SetProjectStage, OnProjectSubmit, OnProjectCommit, the officeTask, ect, will still cause the workflow to dehydrate and give control back to the user. Although for the SetProjectStage activity we have done some extra work and put logic inside it so that it can be skipped, so long as there are no required fields left in the stage
Therefore, to fully work with the skip to stage functionality you will need to wrap activities within “if statements”. When an administrator restarts a workflow and chooses to skip to a certain stage, we pass into the workflow two properties. The first property indicates that a skip stage command has been sent, and the second property contains the stage Guid that the workflow should stop at. Our SetProjectStage activity reads these two variables and skips if the first variable is set to true, and if the current stage is not the stage that is set in the second property. However, it won’t skip if there are required fields in the stage. So you will need to mimic this behavior around your other activities. You should wrap all of the activities between the SetProjectStage activities (not including the SetProjectStage activity) with an ifElseActivity statement that checks the first Property.
To make any workflow work correctly with the skip to stage functionality an “if statement” should be placed around all of the activities between each of the SetProjectStages. The key example of when this would be necessary is for the office task activities.
Screen shot of example workflow:
In the above example the “ifElseActivity1” is used to bypass the leadApproval sequence activity, which contains all of our approval activities.
As you can see from the above screen shot, it is the “if statement” that we need to figure out how to code. In order to do so, we need to be aware of the two properties that are passed into the workflow during a skip to stage event:
WorkflowContext.SkipToStage : Boolean : If true, a skip to stage functionality has been requested
WorkflowContext.StageUid : Guid : Guid of the stage the workflow should skip to
Steps for creating the workflow:
1. Insert and set the properties of a SetProjectStage activity
· You will need to set the “AlwaysWait” property to True in order for the Skip to Stage scenario to properly work.
· If you do not set the Always wait property to true the workflow may not be able to skip to and stop at previous stages.
2. Insert an IfElseActivity
3. Have the activity check the “projectSequence1.WorkflowContext.SkipToStage” variable.
· If variable is set to false, execute all activities
· Else, bypass
The WorkflowContext.StageUid is not needed for the “If Statement” but is there in case you wish to check what stage the user is trying to skip to.
*The complete above example is found in the attached Visual Studio solution.*
To attempt to force a project to skip to a particular stage, do the following within Project Server.
1. Log into Project Server as an Administrator
2. Go to Server Setting
3. Click on “Change or Restart Workflows”
4. In the top drop down, select the EPT that the project(s) that you would like to restart and skip to a stage belong to.
5. Select the project(s) which you would like restart and skip to stage from the left panel and add them to the right panel.
6. Check the “Restart current workflow for the selected projects” option
a. Alternatively you can also select the “Associate projects with a new Enterprise Project Type:” option. If you select an Enterprise Project Type that has a workflow associated with it, you can then select which stage in that workflow the project(s) should attempt to skip to.
7. Next select which stage the project(s) should attempt to skip to
a. Skip until the current workflow stage
i. Selecting this option will tell the projects to attempt to skip until they reach the stage that the project(s) are in currently
b. Skip to a particular workflow stage & then selecting a particular stage
i. Selecting this option will tell the projects to attempt to skip until they reach the stage that has been selected
ii. If the selected stage does not exist, the workflow will stop at the very first opportunity.
8. Press OK
9. The project restart jobs will be sent to the queue, and the projects will attempt to restart and skip.
Expected behavior of the skip to stage can be summed up by the following example.
· A workflow has 3 stages
· All stages are marked as “AlwaysWait”
· Project is on Stage 2
· Stage 1’s required custom fields are filled out
· Stage 2’s required custom fields are not filled out
· Stage 3’s required custom fields are not filled out
Administrator will go and:
o Restart Skip to current stage
§ This will make the workflow restart and stop at stage 2
o Restart Skip to stage 1 (trying to make the workflow stop on a stage where all of the require custom fields are filled out)
§ This will make the workflow stop at stage 1 and wait for submit
o Restart Skip to stage 3 (trying to make it jump over stage 2 even though its required custom fields are not yet filled out)
§ Workflow will stop at stage 2
§ Workflow will not be able to skip stage2 if it has required fields.
§ If you really want to skip it, you need wrap the SetProjectStage as well in an if/else skip condition, which checks the SkipToStage variable and the StageUid variable.
o Restart Skip to stage 5 (trying to make the workflow stop on a stage that does not exist)
§ Workflow will stop at stage 1. This is because of the combination of “Destination stage” doesn’t exist & current stage=”Always wait��.
This feature has been designed and created to address the majority of our customer needs when it comes to being able to skip between stages. In this blog we reviewed there are two steps to use this feature. The first is to correctly develop your workflows to be able to skip over the activities that are between the SetProjectStage activities. This is done by wrapping the activities within an ifElse activity. And the second stage is to restart the workflows and select the stage the project should attempt to skip to. This is done with Project Server --> Server Settings --> Change or Restart Workflows.
If you have any additional questions please feel free to post comments, and I will follow up accordingly.
Using formulas within custom fields has been a part of Microsoft Project for a long time, but typically they are used by the more advanced project managers and by developers of Project applications and add-ons.
That doesn't mean that you shouldn't learn how to use them. After all, of all the ways you can use to customize and automate Project, formulas within custom fields remain one of the simpler ones.
The first question that arises to those new to Project programming is, Why use a formula within a custom field in the first place?
The primary reason is that too often Project's default fields aren't displaying information precisely as you want. The second reason is that using formulas within custom fields is a much quicker and easier way to customize and automate Project than using VBA, VSTO, PowerShell, or C#.
[work]/60/8 & "hours"
This example uses another method to customize Project fields by using functions. (don't confuse formulas with functions). Learn more about Project funcitons here.
Switch(Len(CStr([Baseline Finish]))<3, "No baseline", ([Cost]+1)/([Baseline Cost]+1)>1.2,"Overbudget by 20% or more", ([Cost]+1)/([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")
Switch(Len(CStr([Baseline Finish]))<3, "No baseline", ([Cost]+1)/([Baseline Cost]+1)>1.2,"Overbudget by 20% or more", ([Cost]+1)/([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")
An Example using VBA
Here is VBA code that does the same as the first example above. You might do this if you have numerous reports that need columns set in a specific way. The code could be added as a toolbar button. Adding custom fields using formulas within VBA is a four-step process. Here is an example that adds a "Work Value" column to the Gantt chart, with work values that are calculated by a formula.
CustomFieldSetFormula FieldID:=pjCustomTaskText1, _
Formula:="[Work]& "" hours"""
CustomFieldProperties FieldID:=pjCustomTaskText1, _
TableEdit name:="Entry", TaskTable:=True, NewName:="", fieldName:="", _
NewFieldName:="Text1", Title:="Work Table", ColumnPosition:=1
There you have it. Not so difficult. And you saved the weekend.
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.
I felt that it is time to give VBA some love. I was surfing Web and came across this post on Changing the Cell Background Color which gave me an idea for a blog post. This post is a simple example of how to write an event handler that checks if a name of a task has a prefix of “XYZ_”. If it does, it changes the background color of the Task Name cell to yellow.
This could be useful in scenarios where there is validation when saving to the server and you want to warn the user before the save. For example, say there is a third party application that inserts tasks into project plans automatically. When tasks are inserted by this application, it is prefixed with a code, “XYZ_”. This allows project managers to quickly identify tasks that have been inserted by the third party app. To prevent project managers from inserting the task with the same prefix, a Project Server event handler has been written to prevent tasks with the prefix from any user, except for the user context used by the third party app. This event is only fired during a save to Project Server. To give an early warning to the project manager that the project will fail on saving to the server, we can do following:
Public WithEvents App As ApplicationPublic WithEvents Proj As Project
Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean) MsgBox ("Test") End Sub
Note: This link is to the Project 2003 SDK. It is a list of all the Project Client events you can hook into:
Dim X As New EventHandlers
Sub Initialize_App() Set X.App = MSProject.Application Set X.Proj = Application.ActiveProjectEnd SubThis will setup the event handler to fire before a task is changed.
This will stub out the built in event handler that will fire when the project opens. Here we want to call the initialization method we created in step 6:
At this point we have the event handler hooked up and every time the user changes a task, they will get an annoying test message box. To test it, run: ThisProject.Initialize_App.
Here is what you should get when you change a task:
Now that we have the before task change event handler working, we need get the task that changed to change the cell background color to yellow if the task name begins with "XYZ_". In step 4 we created the event handler, we will need to change the code from displaying the test message box to:
Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean) If (Field = pjTaskName) Then If (InStr(NewVal, "XYZ_") = 1) Then ActiveCell.CellColor = pjYellow End If End If End Sub
Now every time a user changes a task name to begin with "XYZ_" they will see the background color of the cell change to yellow:
My scenario may be a bit of overkill, but hopefully it illustrates how to use the Before Task Change event and how to change the background color of a cell. Maybe in a future post, I will implement the server event that checks the names of the tasks.
We are currently in the early stages of planning the next version of Project. To help plan out the work, we are using Project 2007. To begin, we needed to create a number of projects in bulk and decided to use the PSI to help out with the process. We already had the name for all the projects and the PMs who would be the owners. So, I created an application that read from a text file and set the PM as the project owner and some custom fields to be able to identify the projects.
It was important to set the project owner so that PM can easily access and manipulate the project plan created by the application. This is a fairly easy task when working with the project dataset. To set the project owner, set ProjectOwnerID to the GUID of the resource. The following method may help to look up the resource GUID:
private Guid GetResourceGuid(string ls_name)
WSResource.Resource lo_resWS =
WSResource.ResourceDataSet lo_resDS = new WSResource.ResourceDataSet();
string nameColumn = lo_resDS.Resources.RES_NAMEColumn.ColumnName;
string resUID = lo_resDS.Resources.RES_UIDColumn.ColumnName;
PSLibrary.Filter.FieldOperationType equal =
PSLibrary.Filter lo_filter = new PSLibrary.Filter();
lo_filter.FilterTableName = lo_resDS.Resources.TableName;
lo_filter.Criteria = new PSLibrary.Filter.FieldOperator(equal, nameColumn, ls_name);
lo_resDS = lo_resWS.ReadResources(lo_filter.GetXml(), false);
Acourse, I knew all the PMs were resources on the Project Server instance and did not trap for errors for trying to access the dataset if no rows existed. If you use this method in general, you should put a try catch around:
This method also uses the connection object that I described in an early post for connecting to the PSI.
Note: For the updated article, see Using the ProjTool Test Application in Project Server 2010 in the Project 2010 SDK. The attached ProjTool_ProjectServer2010.zip file includes the ProjTool application and the complete Visual Studio 2010 solution.
The ProjTool test application was introduced in the Microsoft Office Project 2007 SDK. Many developers found it useful for creating, examining, and changing data by using the PSI on a test installation of Project Server, and checking actions by impersonating users on a test server. The 2007 release of ProjTool does not work correctly with Project Server 2010.
ProjTool was originally developed by many different testers on the Project team. In the SDK release, the internal test code has been removed. The download that is attached to this post includes an update of ProjTool for Project Server 2010, along with a draft document on how to use ProjTool. The article and source code will be included in a future update of the Project 2010 SDK.
The updated ProjTool uses the .NET Framework 3.5; all calls to PSI methods use WCF instead of the ASMX interface. ProjTool now works for Windows authentication, Forms authentication, multi-authentication (both Windows and Forms on the same Project Web App port address), and impersonation on Project Server 2010.
Here is an example screenshot from ProjTool, which shows part of the AssignmentDataTable in the ProjectDataSet for one project, after the user has changed the value of the ASSN_PCT_WORK_COMPLETE field in an assignment. When Update Project is clicked, ProjTool shows fields in all of the datatables that have changed as a result.
The How to: Install and Test a Project Server Workflow article in the Project 2010 SDK needs information about how to debug a workflow. Because Visual Studio 2010 can install a workflow solution on the local Project Server computer during development and testing, you can use Visual Studio to attach to a process that the workflow uses.
After you deploy the workflow project in Visual Studio, on the Debug menu, click Attach to Process. In the Attach to Process dialog box, check Show processes from all users and Show processes in all sessions.
Following are considerations to choose the correct process:
When you attach to the correct process, a breakpoint remains as a solid red dot in the left of the code pane. If you do not attach to the correct process, the breakpoint turns into a hollow red circle with yellow caution indicator, and the tooltip for the breakpoint states, “The breakpoint will not currently be hit. No symbols have been loaded for this document.”
When you click Attach, Visual Studio is ready to catch a breakpoint that you set in the workflow code.
The Web Part to List Projects with a Custom Field Value.docx article in the attached ListProjectsWebPart_JSGrid.zip is a draft of procedures that will be included in new articles in the next update of the Project 2010 SDK.
The attached Project2013Reporting.exe file contains a draft release of the schema reference for the reporting tables and views in Project Server 2013. To install the files, run Project2013Reporting.exe. The next release of the Project 2013 SDK download will also include the reporting schema reference; that will be announced here when the Project 2013 SDK update is published.
The Project Server 2013 RDB and OLAP cubes are accessible only with an on-premises installation of Project Server. For Project Online, you can use REST queries of the ProjectData OData service. The ProjectData service is also available with on-premises installations. For more information, see ProjectData - Project 2013 OData service reference in the Project 2013 SDK.
The attached file includes the following:
Note: The draft, published, and archive database objects are not documented. Project Server 2013 combines objects from the four Project Server 2010 databases into one database. The default name of the database is ProjectService; different Project Web App instances can have different database names.
The Project Server 2013 RDB includes three new tables, 27 new views, and a total of 33 tables and views that include new fields. You can easily find the new items by searching, for example, for “New field” (with quotes). The following screenshot shows the new ProjectVisibilityMode field in the dbo.MSP_EpmProject_UserView view:
There are two main places to check for configuration and runtime errors when you are developing solutions for Project Server. The Unified Logging Service (ULS) trace logs can be more detailed than the application event log.
· Application events In the Start menu on the Project Server computer, click Run, and then type eventvwr. In the left pane of the Event Viewer window, click Application to see the events logged by Project Server, Windows SharePoint Services, ASP.NET, SQL Server, custom event handlers, and other applications. The Project Server event sources include ProjectQueueService and pjevtsvc.
· ULS You can configure the ULS trace log to record specific or all categories and levels of activities in Project Server and SharePoint. To view the trace logs, you can use Windows Notepad, Microsoft Excel, or the Log Viewer add-in feature for SharePoint. Log Viewer is a useful download that is available from CodePlex.
To configure the ULS trace log for a specific Project category:
1. Open the SharePoint 3.0 Central Administration application, click Operations, and then click Diagnostic logging in the Logging and Reporting section.
2. In the Event Throttling section, select a specific category such as Project Server - Server-Side Events. If you select Project Server - General, all Project categories will be logged.
3. Select the Verbose level for the least critical event to report in the trace log.
Caution Run verbose logging only when you need it. Especially on a production server, select only a specific category. The size of logs can grow to be large. To turn off all logging, select the empty category and None for the trace log least critical event. To record relatively few events, select High or Monitorable.
4. Use the default path for the trace logs, for example, C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\. If you use the Log Viewer add-in for SharePoint, it looks for trace logs in the default path.
5. The default maximum number of log files to maintain is 96.
6. The default number of minutes to use a log file is 30. That is, ULS tracing creates a new log file after 30 minutes. Log files include the date and time in the filename, for example, SERVERNAME -20070424-1612.log.
To use the Log Viewer add-in for SharePoint:
1. Download and install the Log Viewer feature from CodePlex SharePoint 2007 Features. See the Releases tab for the list of downloads. The release notes include installation instructions.
2. The Log Viewer is a global feature. After it is installed, click Operations on the Central Administration page, and then click View Unified Logging Service in the Utilities section.
3. On the Unified Logging Service (ULS) Logs page, select a file to view, and then select the Project Server category. That shows all of the specific categories such as Project Server Queue, Project Server Server-Side Events, Project Server Reporting, and so forth.
4. To see all events in the ULS log, leave the trace severity drop-down list blank. If you select Verbose, that shows only the verbose level events.
5. Click Go.
Log Viewer add-in for SharePoint showing a ULS log for Project
I have been asked many times how to get the task time phased data from the PSI. Unfortunately there is no way to get this through the PSI. I suggest that you either go to the RDB for this information or the cubes. Here is a quick example.
I created a project with one task and broke the work down over a week:
I saved and published the project so that it would make its way into the reporting database. Here is the query I wrote to retrieve this data:
INNER JOIN MSP_EpmTask_UserView ON MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID
(MSP_EpmTask_UserView.TaskName = 'Task 1')
Here is the result of the query: