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.
 
 

  • Project Programmability and Business Intelligence

    Timesheet Solutions for Webcast: Understanding the PSI in Project Server 2010

    • 1 Comments

    The MSDN Webcast: Understanding Project Server Interface (PSI) in Project Server 2010 (Level 300) presentation is available for download, when you register for the event. Several of the sample applications that were demonstrated in the webcast are available in the Project 2010 SDK download. The TimesheetSamples.zip file that is attached to this blog post includes the two example solutions for timesheets. (Steven Haden and Olivier Laymand, Microsoft Corporation, did the webcast and created the sample code.)

    • MyTsSample shows how to update a  timesheet for a resource, where the timesheet is in single entry mode.
    • TimesheetEventHandler shows how to use impersonation with a TimesheetEventReceiver.OnCreated event handler, to update the timesheet name.
  • Project Programmability and Business Intelligence

    You can use Project with SharePoint Composites, too

    • 1 Comments

    The Guide to creating SharePoint Composites includes a 73-page download that explains SharePoint Composites. To quote from the download, “A SharePoint Composite combines data, documents, and business process in a useful, productive way. … A SharePoint Composite is a ‘do-it-yourself’ business solution.” The SharePoint Composites.docx handbook includes a detailed introduction along with common design patterns that can help provide ideas on how to integrate and present data for collaboration and BI, by using SharePoint.

    Although the Office 2010 integration points section includes only a brief mention of Microsoft Project, the handbook overall is a useful introduction to the concepts. For those who work with Project and Project Server, the handbook can help to expand your view of what is possible – as well as what is useful.  Similar terms you might have heard in the past include mashup and OBAs (Office Business Applications). The SharePoint Composites handbook extends those ideas specifically for SharePoint-based solutions.

  • Project Programmability and Business Intelligence

    Building an app for Project Server 2013

    • 1 Comments

    Alex Burton, a Project MVP, has published a series of posts titled Building your first Project Server app on the EPMSource blog. The app is named Publish All; it publishes all enterprise projects for the PWA instance where the app is installed. The Publish All app is developed by using the Project Server JavaScript object model (JSOM) with "Napa" Office 365 Development Tools, and can be deployed to Project Online and to an on-premises installation of Project Server 2013.

    The blog series includes the following parts:

    Alex has even gone so far as to add a support page for the Publish All app, at http://epmsource.com/2013-apps/. It looks to be a very useful series.

    For more information about the Napa tools, see How to: Create a basic app for SharePoint by using Office 365 Development Tools on MSDN. For information about using Visual Studio 2012 with the JSOM for Project Server, see Getting started with the Project Server 2013 JavaScript object model.

  • Project Programmability and Business Intelligence

    Adding the Project Programmability RSS Feed to Live

    • 1 Comments

    This is a little off topic, but I thought that it might be interesting to some...

    You can easily add a RSS feed to this blog on your live.com homepage:

    1. Go to: www.live.com
    2. Click on "Add Stuff":

    3. Click on "Advanced options":

    4. In the "Subscribe to a specific feed by entering the URL" text box enter:

      http://blogs.msdn.com/project_programmability/rss.xml
    5. Click "Subscribe" and you should now have a RSS feed to the blog:

       
  • Project Programmability and Business Intelligence

    Registering with Events over the next few weeks?

    • 1 Comments

    I just want to give everyone a heads up who is developing and registering event handlers for Project Server over the next few weeks. When you create, update or delete an event handler, a WSS timer job is created to do this work. These jobs are delayed by an hour due to a DST issue in WSS.

    There are a couple of workarounds:

    ·         Wait an hour for the event to be updated

    ·         Set the clock back one hour and let the timer job run

    For more information, see this KB: http://support.microsoft.com/kb/932563

     

  • Project Programmability and Business Intelligence

    Fearing the Password Expired Message

    • 1 Comments

    Since is it common practice for passwords to expire after a set number of days, this can cause a bit of a pain for developers who have setup their Project Server development environment using their own credentials. This happens to me all the time, so I figured I would share a couple of solutions that I use to get around this:

    ·         Create a local administrator and setup Project Server using that account

    ·         Or follow Christophe Fiessinger's blog post on how to change the password:

    http://blogs.msdn.com/chrisfie/archive/2006/12/15/how-to-change-an-administrator-password-on-your-project-server-2007-farm.aspx

    Chris Boyd

  • Project Programmability and Business Intelligence

    Getting your Custom Project Guide to work in Project 2007

    • 1 Comments

    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:

    <object id="TextConv"

          classid="clsid:0F5E0D2C-54F2-41D9-835F-06B3045B7059"

          type="application/x-oleobject"

          style="display: none">TextConv failed to load!

    </object>

    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:

    Object Name

    Project 2003 Class ID

    Project 2007 Class ID

    Text Converter

    1CFC2250-9B5C-4546-ABA1-1F69A06DCA12

    0F5E0D2C-54F2-41D9-835F-06B3045B7059

    Document Event Object

    494B3458-3EFF-4C66-9C86-D47670D69634

    5500517E-1890-48B4-800E-D9FC609E6DC2

    Application Event Object

    04EEB710-3EB7-4B69-9281-E9BBB7B35959

    13D338F1-AA3F-444E-A2B7-BC98EFB03484

    Datepicker

    A709EC93-E1F9-4bc4-A9CB-7FDB51CD0EF1

    03660567-F7F2-4e2b-A13C-C6607A726AF5

     

    Also, you can download the default Project Guide files that has been attached to this post.

     Nada

     

  • 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 Web Reference to the PSI in the RTM Build

    • 1 Comments

    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.

    Chris

  • 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

    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

    Using the Reporting Database and Excel – Part 2

    • 1 Comments

    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!

    Chris Boyd

  • Project Programmability and Business Intelligence

    Writing and Debugging Event Handlers for Project Server 2007

    • 1 Comments

    Another Visual How-to for Project development is published on MSDN: Writing and Debugging Event Handlers for Project Server 2007

     

    In less than 12 minutes, the video shows the development, testing, and debugging of a simple event handler for publishing projects. The OnPublishing event handler cancels publishing if the project name does not satisfy a specified condition, and then logs an application event on the server. The video shows the use of Visual Basic code; the related article includes both Visual Basic and C# code.

     

    The Visual How-to is based on the Project SDK article, How to: Write and Debug a Project Server Event Handler.

  • Project Programmability and Business Intelligence

    Check out the Post on Scalable Issues & Risks Report

    • 1 Comments

    It seems that somehow I messed up and the post "Scalable Issues & Risks Report" ended up showing up in the past. So in case you missed it:

    http://blogs.msdn.com/project_programmability/archive/2007/09/28/scalable-issues-risks-report.aspx

    Chris

  • Project Programmability and Business Intelligence

    Local Custom Fields

    • 1 Comments

    Introduction

    This post 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 post contains two sections:

    - Schema fragments, showing the core tables required to traverse local custom field data

    - Sample queries, showing how to extract custom field and lookup table data for a specific project

    Many of the principles of local custom fields were initially developed in earlier Project Server versions and further questions may be answered by the server schema documentation supplied with those versions, for example:

    DOCS/PJDB.HTM // Primary source of custom field schema documentation

    DOCS/PJSVR.HTM // Additional enumerations

    Both of these files are supplied on the Project Server 2003 installation media.

    Joining Publishing and Reporting

    It is recommended that you implement views in the Reporting database that reference the four tables below as well as MSP_PROJECT_RESOURCES (as local resources are aggregated in the Reporting database)

    For example:

    CREATE VIEW dbo.MSP_PROJECT_RESOURCES AS
    SELECT * FROM [PUBLISHED_DB].dbo.MSP_PROJECT_RESOURCES
    

    Note that if the Reporting Database has been installed on a separate SQL Server instance that you will have to use a four part name in the views including the linked server name.

    Schema (Fragment)

    This section documents the relevant attributes and entities required to extract local custom field data for reporting purposes.

    This table holds the master list of all local custom fields in use, keyed on the Proj_UID. Obtain the MS_PROP_UID (which keys the custom field values in the next two tables) from the MD_PROP_NAME or by using the MSP_WEB_VIEW_FIELDS table (get the WFIELD_UID that corresponds to the WFIELD_NAME_OLEDB of the local custom field that you want) – we use “Magic GUIDs” to identify local custom fields (ie 000039b7-8bbe-4ceb-82c4-fa8c0b400033 is always TaskText1)

    This table contains the values (or a pointer to a lookup table value) for task local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

    Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry MD_LOOKUP_TABLE_UID in the previous table data is held in the appropriate *_VALUE column.

    This table contains the values (or a pointer to a lookup table value) for resource local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

    Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry, MD_LOOKUP_TABLE_UID in the Project Custom Fields table, data is held in the appropriate *_VALUE column.

    This table contains lookup table values pointed to by the previous two tables. Assuming either text or hierarchical selection the LT_VALUE_TEXT and LT_VALUE_FULL are most interesting. (_FULL contextualizes the selection and corresponds to the value stored in the enterprise equivalents in the Reporting database.

    Working With Project Task and Resource Custom Fields

    This section contains a series of queries to get you started. These all return tables that can be joined (use an outer join to ensure that data doesn’t get stripped) onto our Reporting USERVIEW views to obtain custom field data.

    Note 1: I’m assuming that the majority of data will be in text custom fields or outline codes, the *_custom_field_values tables contain other buckets for the other field types.

    Note 2: I haven’t qualified these query snippets with a PROJ_UID so they will return all data, you should remember to add this field for project-specific reports otherwise performance will be sub optimal.

    Note 3: I’ve packaged the resource outline code query in two ways, the second as a sample function to make usage a little bit simpler, as the function abstracts out all the joining to get the values.

    Join Table of Task_UID and a text Custom Field
    SELECT task.task_uid, tlcf.text_value 
    FROM dbo.msp_tasks AS task 
    INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
    ON (task.task_uid = tlcf.task_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'CustomFieldName'
    Join Table of Res_UID and a text Custom Field
    SELECT res.res_uid, rlcf.text_value 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'RescourceCustomFieldName'
    Join Table of Task_UID and an Outline Code
    SELECT task.task_uid, tloclt.lt_value_full, tloclt.lt_value_text 
    FROM dbo.msp_tasks AS task 
    INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
    ON (task.task_uid = tlcf.task_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS tloclt 
    ON (tlcf.code_value = tloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'TaskOutlineCodeName'
    Join Table of Res_UID and an Outline Code
    SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
    ON (rlcf.code_value = rloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'RescourceOutlineCodeName'
    Sample Function for Ease of Use
    CREATE FUNCTION dbo.Custom_ResOC (@OutlineCodename NVARCHAR(50)) 
    RETURNS TABLE AS 
    RETURN (SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
    ON (rlcf.code_value = rloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = @OutLineCodename) 
    GO 
    -- Can be used in a join clause
    SELECT * FROM dbo.Custom_ResOC(N'ResourceOutlineCodeName') 
    GO
    

  • Project Programmability and Business Intelligence

    Tied Mode between Timesheet & My Tasks

    • 1 Comments

    We have published today an EPM 2007 solution starter that introduces Tied Mode between Timesheet & My Tasks on CodePlex: http://www.codeplex.com/EPMTSST

    MWSnap024 2007-11-15, 16_16_05.jpg

    Project Server 2007 provides for full separation between the project-focused My Tasks “Statusing” functionality and the time-period-focused Timesheet functionality, together with the ability to manually import data from one set of functionality to the other to keep them synchronized.

    This solution attempts to address this frustration by treating the timesheet system as the master system and attempting to synchronize the data into the My Tasks functionality (including task approvals) whenever a timesheet is saved (similar to Project Server 2003 integrated (“Tied”) timesheet and statusing functionality).

    This solution starter includes a 18 pages document as well as source code and a compiled DLL ready to deploy and test.

    A special thank you to Julie Ripoteau and Olivier Laymand from MCS France for testing the code extensively, as well as Patrick Conlan and Chris Boyd from the Product Group for reviewing the code and documentation. 

  • Project Programmability and Business Intelligence

    EPMsync Tool Released to CodePlex

    • 1 Comments

    Boris Scholl has just passed along this great news: 

     

    After months of development we have published the EPM sync tool on CodePlex for other customers and Partners to try out for themselvesGiven the complexities of the integration, we expect partners to develop solution offerings based on this tool.

     

    Microsoft Office Project Server 2007 (Project Server) and Microsoft Office Project Portfolio Server 2007 (Portfolio Server) integrate via the Portfolio Server gateway. Data is exchanged by running either an import or an export from Portfolio Server. But this exchange is done in bulk and typically batched. There is a desire to have a more granular experience in near real-time. The EPMSync Solution Accelerator is a proposed solution to this problem. The EPMSync Solution Accelerator synchronizes Portfolio Server attributes with Project Server custom fields (and a set of project level built-in fields). The solution is made up of a Windows Service and a number of supporting applications.

     

     

    About CodePlex

    CodePlex is Microsoft's open source project hosting web site. You can use CodePlex to create new projects to share with the world, join others who have already started their own projects, or use the applications on this site and provide feedback. A word about Microsoft’s role: Microsoft does not control, review, revise, endorse or distribute the third party projects on this site. Microsoft is hosting the CodePlex site solely as a web storage site as a service to the developer community.

      

    Q&A

     

    Q: Is the tool supported?

    A: There is no support in terms of CSS/PSS. We expect the support being a CodePlex community effort.

     

    Q: Is the tool for free?

    A: Yes it is.

     

    Q: Can I distribute the tool and the source code to customers and partners.

    A: Customers and Partners can use both. Please point them to the website as they have to agree on the license terms

     

    Q: Can a Partner distribute the tool and code as is?

    A: No, but he can point his customer to the website to download it, so he makes sure that the customer agrees with the license terms.

     

    Q: Technically can a customer just install and use it?

    A: Yes the customer can, but the customer is responsible for testing it and running it.

     

    Q: Can I suggest changes to it?

    A: Yes, join the CodePlex community or send us an email: epmssdev@microsoft.com

     

    Q: Will this tool be distributed in other ways (i.e DVDs)

    A: Nope

     

    Q: What skills do I need to modify or change the tool?

    A: .NET 2.0, C#, T-SQL, PSI

     

    Q: I’m trying to modify the code and do have questions. Who do I ask?

    A: Go to the EPMsync discussion forum on CodePlex.

     

     

  • Project Programmability and Business Intelligence

    Working with the Custom Fields Data Table

    • 1 Comments

    There have been a number of inquiries with regards to the custom field column definitions. So here they are:

    MD_AGGREGATION_TYPE_ENUM: Specifies the roll up type for a custom field.

    MD_ENT_TYPE_UID: Specifies the type of entity (project, task, or resource) for the custom field.

    MD_LOOKUP_TABLE_UID: Specifies the GUID of the lookup table to use for the custom field.

    MD_PROP_CHECKOUTBY: GUID of the user who has the custom field checked out for modification.

    MD_PROP_CHECKOUTDATE: Date the custom field was checked out for modification.

    MD_PROP_DEFAULT_VALUE: GUID of the default value in a lookup table for a custom field.

    MD_PROP_FORMULA: Formula that calculates the value of a custom field.

    MD_PROP_GRAPHICAL_INDICATOR: Definition of a graphical indicator for a custom field.

    MD_PROP_GRAPHICAL_INDICATOR_TOOLTIP: Specifies whether the graphical indicator has a tool tip.

    MD_PROP_ID: Integer ID of the enterprise custom field.

    MD_PROP_ID_SECONDARY: Assignment custom field ID for resource and task custom fields.

    MD_PROP_IS_LEAF_NODE_ONLY: Specifies whether the custom field value must be a leaf node in the lookup table.

    MD_PROP_IS_REQUIRED: Specifies whether the custom field is required when the entity (project, resource, or task) is created.

    MD_PROP_MAX_VALUES: Specifies whether the custom field has a single value or uses a multi-value lookup table.

    MD_PROP_NAME: Name of the custom field.

    MD_PROP_PROJ_SUMM_GRAPHICAL_INDICATOR: Definition of the project summary graphical indicator for the custom field.

    MD_PROP_ROLLDOWN_TO_ASSN: Specifies whether the resource or task custom field rolls down to assignments.

    MD_PROP_SUMM_GRAPHICAL_INDICATOR: Definition of the custom field graphical indicator for the summary task.

    MD_PROP_TYPE_ENUM: Type of the custom field.

    MD_PROP_UID: GUID of the custom field.

    MD_PROP_UID_SECONDARY: Assignment custom field GUID for resource and task custom fields.

    MD_PROP_USE_FOR_MATCHING: Specifies whether to use the resource custom field for matching generic resources.

    Chris Boyd

  • Project Programmability and Business Intelligence

    New Technical Article: "Importing Project 2007 Tasks from Excel Using a Managed Code Add-In"

    • 1 Comments

    Jim has just posted a new technical article. Here are some details he sent me: 

     

    The article "Importing Project 2007 Tasks from Excel Using a Managed Code Add-In" is now published in the Technical Articles section of the Project 2007 SDK.

     

    Article URL:

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

     

    Associated download:

    http://www.microsoft.com/downloads/details.aspx?familyid=416b606a-88c8-496f-9ad3-9afa09780e12&displaylang=en 

     

    The article:

      -- Explains how to develop a Project add-in that integrates with Excel, using Visual Studio 2008

      -- Summarizes the differences between C# and VB development for add-ins

      -- Includes extensive C# and Visual Basic code examples

      -- Explains examples of routines that validate and convert subsets of Excel task data for import

      -- Handles multiple task predecessors

      -- Shows a way to handle task predecessors that would create COM exceptions
      -- Shows how to configure ClickOnce for deployment of the add-in

     

    The download includes the complete C# and Visual Basic.NET code. Visual Studio Tools for Office (3.0), in Visual Studio 2008 (Beta 2), now includes templates for Project 2003 and Project 2007 add-ins. The new templates and ClickOnce publishing make it *much* easier to develop, deploy, and update add-ins for all Office applications.

     

    You can download the Beta 2 release of Visual Studio 2008 from http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx.

  • Project Programmability and Business Intelligence

    Adding a Web Reference Using Visual Studio Orcas Beta 2

    • 1 Comments

    Lately I have been playing around with VS Orcas Beta 2; primarily to play around with VSTO support for Project Client. When trying to add a web reference to our web services (PSIs), at first I could not find the option to do it in the VS IDE. After digging around, I figured it out. Here are the steps to adding a web reference to one of the PSI web services in Visual Studio Orcas Beta 2:

    1. Right Click on Service Reference and Select Add Service Reference...

    image

    2. Click the Advanced Button:

    image

    3. Click the Add Web Reference Button:

    image

    This will bring up the familiar web reference UI from Visual Studio 2005 and you can add the web reference just like you did in the past.

    Chris Boyd  

  • Project Programmability and Business Intelligence

    Project 2007 Resource Kit

    • 1 Comments

    Hello,

    I just wanted to point everyone to our other team blog:

    http://blogs.msdn.com/project/archive/2008/01/25/project-2007-project-resource-kit.aspx

    The latest post is about the release of the Project 2007 Resource Kit. There are a lot of useful tools in the resource kit that you might want to check out!

    Chris

  • Project Programmability and Business Intelligence

    Project Server 2007 - Project Server Developer Community Expanding

    • 1 Comments

    Leveraging the development aspects of Project Server 2007 spans a wide range of skills.  PWA customizations (ASP.NET), workflow development, line of business applications integration using the PSI, extending Project Server functionality using server side events, specialized reporting from the Project Server databases and experience using VSTO are all areas of discussion. 

    The Project Server Product Group currently supports these efforts through channels such as the this Blog, Project Support WebLog, Project.Server Discussion, Project.Developer Discussion, and the Project 2007 Team Blog.

    To streamline and augment the existing efforts, we are seeking developers to join the already established Project.Developer Discussion Group

    By joining we invite you to share and discover knowledge about Project Server 2007's highly extensible out-of-the-box developer's platform, access Project Server Developer best practices, utilize a technical window back to the MS Project Server Development Group, and take part in several specialized webcasts on Development topics coming up in the next couple months.

    To identify yourself as someone that would like to join the Development Community, please contact Joyce Bileau, v-joyceb@microsoft for more information.

    To join, go to http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.project.developer

  • Project Programmability and Business Intelligence

    Project Server 2007 VSTS Connector

    • 1 Comments

    Hello,

    There have been a number of people that have asked me about connecting Project Server to VSTS. Here is a pointer to a CodePlex project that will do just that:

    http://www.codeplex.com/pstfsconnector

    "The Visual Studio Team System Project Server 2007 Connector is designed to integrate the project management capabilities of VSTS with Project Server 2007. It's been developed by the Visual Studio Team System Rangers in response to significant customer demand for a connector solution. Future versions of Team System will have native integration with Project Server, in the meantime this Connector solution is the best way to integrate the two Microsoft products. This solution builds on the previous PS2003 VSTS Connector, published on GotDotNet. This solution is intended to provide guidance, provided as source code that can be used "as is," extended, or modified by developers to use on enterprise development projects."

     

    Chris Boyd

  • Project Programmability and Business Intelligence

    Version 2 of the Office Interactive Developer Map

    • 1 Comments

    Just got an e-mail from Jim that Version 2 of the Microsoft Office Interactive Developer Map was published today, by the Office Developer Center folks.

    clip_image001

    The new version includes lots of new stuff (new products and new interactive links) – such as VS2008, Communications Server, Performance Point Server, Office Business Application (OBA) resources, and more.

    After you install it, the shortcut is in the 2007 Microsoft Office System Developer Resources folder in your Start menu.

     

  • Project Programmability and Business Intelligence

    Update: Statusing Transaction (Partial Documentation)

    • 1 Comments

    Hello,

    Just a small update to the Statusing Transaction (Partial Documentation) post. The ASSN_TRANS_ACTION_ENUM enum was missing a couple of values:

     

       enum StatusTransactionState

       {

          /// <summary>Saved by team member.</summary>

          Saved = 0,

          /// <summary>Submitted by team member, awaiting action by manager.</summary>

          Submitted = 1,

          /// <summary>Submitted for processing by manager, awaiting processing by message handler.</summary>

          InProcess = 2,

          /// <summary>Applied to working store.</summary>

          ApplySuccessful = 3,

          /// <summary>Failed to be applied to working store.</summary>

          ApplyFailed = 4

       }

     

    Chris Boyd

Page 4 of 11 (255 items) «23456»