Project Programmability

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.
 
 

November, 2006

  • Project Programmability and Business Intelligence

    Using the Reporting Database and Excel – Part 1

    • 1 Comments

    I am not sure if this is a programmability post, but there have been many requests to do a post on writing a report in Excel that collects data from the reporting database. Since this will involve SQL and many developers who write code against Project Server will also need to write reports; this may be useful to the developer community. I am going to break this into two posts because it is fairly long and there is a logical break. In the first post, we will create the SQL query to retrieve the data from the reporting database. In the second post, I will describe the process of using the query in Excel to write reports.

    Before we begin, it should be known that there is a dedicated reporting database in Project Server 2007. This database is separate from the working and publish database that is heavily used by Project Professional and Project Server. Every time a project is published, or resource is updated, the data is pushed to the reporting database. The reporting database provides a supported interface for gathering project data via SQL queries. This reporting database has been setup to easily create reports.

    Someone posted that they wanted a report that showed the assigned work for each resource, grouped by project and then by month. This will allow project managers to better understand the allocation of resources by month. I will try my best writing the query for this, but I shell add the warning that this has not been tested. If you find a mistake, or a better way of writing the query, please post it as a comment.

    To begin, we need to write a SQL query against the reporting database to get at the data we need. I wrote the SQL query in stages. The first step was to get the assignment data from the projects. To do that, I wrote the following query:

    SELECT

    base.ResourceUID as 'Resource',

    assn.TimeByDay as 'Day',

    assn.AssignmentWork as 'Assigned'

    FROM

    MSP_EPMAssignmentByDay_UserView  AS assn

    INNER JOIN MSP_EPMAssignment_UserView AS base

                ON (assn.AssignmentUid  = base.AssignmentUID)

    This gives us all the assignments for each resource by name. We will also want the project name that the assignment is coming from. To do this, we will have to do another join on the MSP_EmpProject_UserView:

    SELECT

    base.ResourceUID as 'Resource',

    assn.TimeByDay as 'Day',

    assn.AssignmentWork as 'Assigned',

    ProjectName

    FROM

    MSP_EPMAssignmentByDay_UserView  AS assn

          INNER JOIN MSP_EPMAssignment_UserView AS base

                ON (assn.AssignmentUid  = base.AssignmentUID)   

    INNER JOIN MSP_EpmProject_UserView

    ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID

    Now we have all the assignment data for each resource. For this report we will also want to capture assignments outside of a project. An example of this would be vacation time. To get at this information, we are going to query the Timesheet tables in the reporting database.

    SELECT

    MSP_TimesheetResource.ResourceUID as 'Resource',

    ts.TimeByDay as 'Day',

    ts.ActualWorkBillable as 'Assigned'

    FROM

    MSP_TimesheetActual AS ts

          INNER JOIN MSP_TimesheetLine AS tl

                ON (ts.TimesheetLineUID = tl.TimesheetLineUID)

    INNER JOIN MSP_TimesheetClass AS tc

    ON tl.ClassUID = tc.ClassUID

    INNER JOIN MSP_Timesheet AS tsowner

    on tl.TimesheetUID = tsowner.TimesheetUID

    INNER JOIN MSP_TimesheetResource

    ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID

    WHERE

    tc.[Type] = 2

    This returns all the timesheet lines for none project work.

    Now we have all the assignments. The next step is to combine the two queries. To do this, I used the union statement. You will notice that in the timesheet query we do not have a project name in the select. We are going to add the project name to the select by adding 'None Project Time' as ProjectName to the select statement. Here are the combined queries:

    SELECT

    base.ResourceUID as 'Resource',

    assn.TimeByDay as 'Day',

    assn.AssignmentWork as 'Assigned',

    ProjectName

    FROM

    MSP_EPMAssignmentByDay_UserView  AS assn

          INNER JOIN MSP_EPMAssignment_UserView AS base

                ON (assn.AssignmentUid  = base.AssignmentUID)   

    INNER JOIN MSP_EpmProject_UserView

    ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID

     

    Union ALL

     

    SELECT

    MSP_TimesheetResource.ResourceUID as 'Resource',

    ts.TimeByDay as 'Day',

    ts.ActualWorkBillable as 'Assigned',

    'None Project Time' as ProjectName

    FROM

    MSP_TimesheetActual AS ts

          INNER JOIN MSP_TimesheetLine AS tl

                ON (ts.TimesheetLineUID = tl.TimesheetLineUID)

    INNER JOIN MSP_TimesheetClass AS tc

    ON tl.ClassUID = tc.ClassUID

    INNER JOIN MSP_Timesheet AS tsowner

    on tl.TimesheetUID = tsowner.TimesheetUID

    INNER JOIN MSP_TimesheetResource

    ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID

    WHERE

    tc.[Type] = 2

    So now we have all the assignment data together. The last step is join with the resource table to get the name of the resources and to group by various fields to roll up the data.

    SELECT

    ProjectName,

    MSP_EpmResource.ResourceName,

    CAST(YEAR(Day) as Varchar(4)) + '-' + CAST(MONTH(Day) as Varchar(2)) as Month,

    SUM(Assigned) as Assigned

    FROM

    MSP_EpmResource

    Right Join

    (

     

    SELECT

    base.ResourceUID as 'Resource',

    assn.TimeByDay as 'Day',

    assn.AssignmentWork as 'Assigned',

    ProjectName

    FROM

    MSP_EPMAssignmentByDay_UserView  AS assn

          INNER JOIN MSP_EPMAssignment_UserView AS base

                ON (assn.AssignmentUid  = base.AssignmentUID)   

    INNER JOIN MSP_EpmProject_UserView

    ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID

     

    Union ALL

     

    SELECT

    MSP_TimesheetResource.ResourceUID as 'Resource',

    ts.TimeByDay as 'Day',

    ts.ActualWorkBillable as 'Assigned',

    'None Project Time' as ProjectName

    FROM

    MSP_TimesheetActual AS ts

          INNER JOIN MSP_TimesheetLine AS tl

                ON (ts.TimesheetLineUID = tl.TimesheetLineUID)

    INNER JOIN MSP_TimesheetClass AS tc

    ON tl.ClassUID = tc.ClassUID

    INNER JOIN MSP_Timesheet AS tsowner

    on tl.TimesheetUID = tsowner.TimesheetUID

    INNER JOIN MSP_TimesheetResource

    ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID

    WHERE

    tc.[Type] = 2

     

    ) b

    on MSP_EpmResource.ResourceUID = Resource

    GROUP BY

    ProjectName,

    Resource,

    CAST(YEAR(Day) as Varchar(4)) + '-' + CAST(MONTH(Day) as Varchar(2)), MSP_EpmResource.ResourceName

    Order By

    Month

    You will notice that I have stripped out the day from the date. This is how we can roll up the assignments for each resource by month.

    We now have our completed query! The next step is to get this data into Excel…

    Chris Boyd

     

  • Project Programmability and Business Intelligence

    The Microsoft Office Project 2007 SDK

    • 1 Comments

    The Microsoft Office Project 2007 SDK has been published to MSDN:

    http://msdn2.microsoft.com/en-us/library/ms512767.aspx

    The down load version should be available in the next few days. I will post a link once is becomes available.

    Though this is the RTM release of the SDK, we will be continually updating the SDK though out the life of the product. One source of SDK articles is this blog. So, if you have samples you would like see posted, please comment with your suggestions.

    Chris Boyd

  • Project Programmability and Business Intelligence

    Microsoft Project 2007 Client Developer Help is Now Available Online

    • 0 Comments

    Jack Dahlgren  on his Project Blog talks about the release of Microsoft Project 2007 client developer documentation online.

     

    This is a great resource for Project VBA and other Project client developers.

     

  • Project Programmability and Business Intelligence

    Getting a Project GUID on the Cheap

    • 1 Comments

    Many people have requested for a complementary method to GetProjectNameFromProjectUid where they can pass in a project name and it returns the project's GUID. Unfortunately it did not make it into the Project API. Since it has been requested many times, I figured that it would be useful to post a method, GetProjectUidFromProjectName, which did just that:

    public static Guid GetProjectUidFromProjectName(string projectName)
    {
      Guid projectGUID;

      WSProject.ProjectDataSet readProjDs = projWS.ReadProjectStatus(
                                                                                                Guid.Empty,
                                                                                                WSProject.DataStoreEnum.WorkingStore, 
                                                                                                projectName, 
                                                                                                0
                                                                                                         );

      if (readProjDs.Project.Rows.Count == 1)
      {
        projectGUID = new Guid(readProjDs.Project[0].PROJ_UID.ToString());
      }
      else
      {
        throw new Exception("No Project by the name: " + projectName + " Found");
      }

      return projectGUID;

    }  

    First thing to note is that projWS is defined elsewhere and it is a connection to the Project Web Service. See my earlier post on Getting Started with the PSI on how to make the connection to the Project Web Service.

    To get the GUID we are going to use the ReadProjectStatus method, as it is the cheapest call to make to get the project's GUID. As you will see, this method takes 4 parameters. The first parameter is the project GUID. In this case, we pass in an empty GUID because that is what we are looking for. Passing in an empty GUID tells Project Server not to search for a project by the GUID.  

    The second parameter is the store to get the project GUID from. Here we have the option of getting the GUID from the working, published or archived store. In this example, I am getting it from the working store, since I want to get the GUID for projects that are actively being worked on and may or may not have been published. Note that this will also get projects that have been published, since they exist in both stores with the same GUID. You may want to change the store based on your requirements.

    The third parameter is the name of the project. This is passed into our GetProjectUidFromProjectName method.

    The last parameter is the type of project. There a number of different project types:

    http://msdn2.microsoft.com/en-us/library/microsoft.office.project.server.library.project.projecttype.aspx

    In most cases 0, which represents standard projects, will be the correct project type.

    The ReadProjectStatus returns a project dataset. If the number of rows returned for the project table is equal to 1, than we have found a project with the given name and we can return the GUID for the project. If the number of rows is not equal to one, than no project by the name was found and we throw an exception.

    Hope this helps,

    Chris Boyd

  • Project Programmability and Business Intelligence

    Adding a Project to a Category

    • 3 Comments

    Brian Smith from PSS has passed along this sample that we thought might be helpful:

    The scenario here is that you have a lookup table that shows the categories you want users to select from when creating a project, and then the GUID for the "real" security category is held in the description for the lookup table value.  You make the CF that feeds from the Lookup Table a required Project Level text field.  The Project.Created event fires and the dataset is read - the custom field identified and the GUID of the security category is then used to add the project to the security category.

    No error checking or exception handling is shown - you can do this bit. You would also need to set the categories to the rule "only projects...".  I've hardcoded my lookup table and a reference required to the Microsoft.Office.Project.Server.Library and Events. A Web References to LookupTable, Project, Security and LoginWindows is also required.

    The code will run as the user running the services - so you will either need that account to have PWA permissions or to change to use impersonation.

    using System;
    using System.Collections.Generic;
    using System.Net;
    using System.Diagnostics;
    using System.Text;
    using Microsoft.Office.Project.Server.Events;
    using Microsoft.Office.Project.Server.Library;

    namespace TestEventHandler
    {

        public class AutoCategory:ProjectEventReceiver
        {

            public override void OnCreated(PSContextInfo contextInfo, ProjectPostEventArgs e)
            {

                // cfGuid holds CF for Project Category
                Guid cfGuid = new Guid("9bbc698f-5c1d-4f8d-a3d0-163006416bf2");

                // ltGuid holds LT for Categories
                Guid ltGuid = new Guid("625bab60-4427-4f0b-941b-9860d1293338");

                // lt_Struct_Uid gets the id for the selected LT value
                Guid lt_Struct_Uid = new Guid("00000000-0000-0000-0000-000000000000");

                // securityCategoryGuid gets the Security Categorty Guid from the Descriptio field in the lookup table
                Guid securityCategoryGuid = new Guid("00000000-0000-0000-0000-000000000000");

                // 32 is used to just get the CF entities from the readProjectEntities
                const int PROJECT_ENTITY_TYPE_PROJECTCUSTOMFIELD = 32;

                Guid SECURITY_CATEGORY_OBJECT_TYPE_PROJECT = new Guid("1771B1C0-6E26-4FB3-A480-C798AB506E82");

                WebSvcLoginWindows.LoginWindows loginWindows = new TestEventHandler.WebSvcLoginWindows.LoginWindows();
                WebSvcProject.Project project = new TestEventHandler.WebSvcProject.Project();
                WebSvcSecurity.Security security = new TestEventHandler.WebSvcSecurity.Security();
                WebSvcLookupTable.LookupTable lookupTable = new TestEventHandler.WebSvcLookupTable.LookupTable();

     

                //login to Project Server - this assumes the event service has a login with permissions

                // Impersonation would be better
                loginWindows.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/LoginWindows.asmx";
                loginWindows.Credentials = CredentialCache.DefaultCredentials;
                loginWindows.Login();

                // Get the dataset
                project.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/Project.asmx";
                project.Credentials = CredentialCache.DefaultCredentials;

                lookupTable.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/LookupTable.asmx";
                lookupTable.Credentials = CredentialCache.DefaultCredentials;

                security.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/security.asmx";
                security.Credentials = CredentialCache.DefaultCredentials;

                WebSvcProject.ProjectDataSet dsProjectDataSet = new TestEventHandler.WebSvcProject.ProjectDataSet();

                dsProjectDataSet = project.ReadProjectEntities(e.ProjectGuid, PROJECT_ENTITY_TYPE_PROJECTCUSTOMFIELD, TestEventHandler.WebSvcProject.DataStoreEnum.WorkingStore);

                for (int i = 0; i < dsProjectDataSet.ProjectCustomFields.Count; i++)
                {
                    if (dsProjectDataSet.ProjectCustomFields[i].MD_PROP_UID == cfGuid)
                    {
                        lt_Struct_Uid = dsProjectDataSet.ProjectCustomFields[i].CODE_VALUE;
                    }
                }

                Guid[] arrayLtUid = new Guid[1]{ltGuid};
                WebSvcLookupTable.LookupTableDataSet dsLookupTable = new TestEventHandler.WebSvcLookupTable.LookupTableDataSet();
                dsLookupTable = lookupTable.ReadLookupTablesByUids(arrayLtUid, false, 1033);

                for (int i = 0; i < dsLookupTable.LookupTableTrees.Count; i++)
                {
                    if (dsLookupTable.LookupTableTrees[i].LT_STRUCT_UID == lt_Struct_Uid)
                    {
                        securityCategoryGuid = new Guid(dsLookupTable.LookupTableTrees[i].LT_VALUE_DESC.ToString());
                    }
                }           

    WebSvcSecurity.SecurityCategoriesDataSet dsSecurityCategories
       = new TestEventHandler.WebSvcSecurity.SecurityCategoriesDataSet();

    // Read the existing values for the security category into the dataset
    dsSecurityCategories = security.ReadCategory(securityCategoryGuid);

                // Get a new objects row to put the created project into

                WebSvcSecurity.SecurityCategoriesDataSet.SecurityCategoryObjectsRow dsSecurityCategoryObjectsRow
    = dsSecurityCategories.SecurityCategoryObjects.NewSecurityCategoryObjectsRow();

                //Set the values
                dsSecurityCategoryObjectsRow.WSEC_OBJ_TYPE_UID = SECURITY_CATEGORY_OBJECT_TYPE_PROJECT;
                dsSecurityCategoryObjectsRow.WSEC_CAT_UID = securityCategoryGuid;
                dsSecurityCategoryObjectsRow.WSEC_OBJ_UID = e.ProjectGuid;

                // Add the row to the dataset and then pass to SetCategories to update
                dsSecurityCategories.SecurityCategoryObjects.AddSecurityCategoryObjectsRow(dsSecurityCategoryObjectsRow);

                // Create an EventLog instance and assign its source.
                EventLog myLog = new EventLog();
                myLog.Source = "Project Event Handler";

                // Get information from the event arguments, and

                // write an entry to the Application event log.
                string userName = contextInfo.UserName.ToString();
                string projectName = e.ProjectName.ToString();
                string secCatUid = securityCategoryGuid.ToString();
                int eventId = 3652;
                string logEntry;

     

                logEntry = "User: " + userName +

                        "\nProject: " + projectName +

                        "\nSecurity Category Uid: " + secCatUid;

                    myLog.WriteEntry(logEntry, EventLogEntryType.Information, eventId);

                security.SetCategories(dsSecurityCategories);           

            }
        }
    }

     

Page 1 of 1 (5 items)