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.
 
 

August, 2009

  • Project Programmability and Business Intelligence

    Using formulas in custom fields

    • 5 Comments
     

    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#.

     

    Some Examples

    • For reporting purposes, if you'd rather have a text field and not a number field  to display on the Gantt chart how much work resources are assigned to, you could create a custom text field, and then attach this formula to it:

     

        [work]/60/8 & "hours"

     

    1. To add the above formula, click Customize on the Tools menu, and then click Fields.
    2. Select a text field to customize, such as "Text1", and click Rename to give it a meaningful name, like "Work Time".
    3. Once you've renamed the field, click Formula.
    4. Copy and paste the above formula into the Edit Formula box.
    5. You're not done yet. After the formula has been added, go back to the Gantt chart and add the "Work Time" field.

     

     

    • Here's another example,  When added to a custom number field, the following formula returns a numerical value that indicates the number of days between the current date and the finish date of the tasks in your schedule:

     

       DateDiff("d",NOW(),[Finish])

     

    This example uses another method to customize Project fields by using functions. (don't confuse formulas with functions). Learn more about Project funcitons here.

     

     

    • A more complicated example: When added to a custom text field, the following formula (with functions included) returns a value of "No baseline," "Overbudget by 20% or more," or "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.

     

    1. First, set up the custom formula that the custom field should use. In this example, the constant, pjCustomTaskText1, is being used to specify that a Text1 field is being customized to contain a formula.

     

       CustomFieldSetFormula FieldID:=pjCustomTaskText1, _

        Formula:="[Work]& "" hours"""

     

    1. Now calculate the formula.

     

       CustomFieldProperties FieldID:=pjCustomTaskText1, _

        Attribute:=pjFieldAttributeFormula, _

        SummaryCalc:=pjCalcFormula

     

    1. Now add the field to a view.  "NewFieldName" indicates that Text1, will be used, which is equivalent to the pjCustomTaskText1 used in the CustomFieldProperties method above.

     

    TableEdit name:="Entry", TaskTable:=True, NewName:="", fieldName:="", _ 

    NewFieldName:="Text1", Title:="Work Table", ColumnPosition:=1

     

       

    1. Now apply the table to a view. I know, it sounds odd to apply the table when all you want to do is add a field, but that's the way it is with VBA code.

     

       TableApply name:="Entry"

     

    There you have it. Not so difficult. And you saved the weekend.

     

  • Project Programmability and Business Intelligence

    Project Server 2007 Report Pack II - "The Top Reports"

    • 0 Comments

    We are proud to announce that the  Project Server 2007 Report Pack II - "The Top Reports" a collection of best practices reports for SQL Server Reporting Services is available on MSDN Code Gallery.

    Project Server 2007 Report Pack II - "The Top Reports"

    This Project Server 2007 Report Pack II - "The Top Reports" addresses questions "what" reports to run in your Project Server 2007 environment and "why". It comprises of 40 reports divided into Management and Governance sections. The pack was designed by top Microsoft Consultants who deploy and enhance the Enterprise Project Management Solution (EPM) for individual customers and thus contains very valuable know-how that they have collected over the years.

    More information

    · Download: http://code.msdn.microsoft.com/EPMReportPack

    · Recorded webcast: https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032423031


    Other available solutions for Enterprise Project Management (EPM) solution

    MSDN Code Gallery

    http://code.msdn.microsoft.com/Project/ProjectDirectory.aspx?ProjectSearchText=epm

    MSDN Code Gallery solutions

     

    Project Server 2007 Sample Databases

    Project Server 2007 sample databases. The zip contains the following five databases: * Archive * Draft * Published * Reporting * Content

    EPM 2007 Project Updater InfoPath Form

    A web-enabled InfoPath 2007 Form using Project Server 2007 PSI .Net web services. The form allows project managers to easily mark tasks as complete in a sequential, process-like project plan…

    Solution Connector for Microsoft Office Project Portfolio Server 2007

    The Solution Connector is an ASP.NET web service and .NET class library which provides a set of methods for programmatically creating, updating or deleting projects and their attributes in …

    Building an AJAX Web Part for Microsoft Office Project Server 2007

    This sample contains a Project Server 2007 PSI Extension that retrieves geo data based on a custom field, and then the main feature is an AJAX Web Part that uses Virtual Earth to display the …

    Microsoft Office Project Server 2007 PSI Extension Generator

    PSI Extensions are custom web services that execute within the Project Server Interface infrastructure, sharing the same security context as other PSI web services. PSI Extensions can be very …

    Project Server 2007 Migration Rename Tool

    During a Project Server 2003 to Project Server 2007 migration, projects have "_Published" appended at the end of their name. This tool will enable you to "bulk" rename all projects and removed …

    Project Server 2007 Lookup Table Update Control

    Out of the box you cannot enforce control updates of specific lookup tables in Project Server 2007. This code sample leverages the standard Project Server interface API to limit who and what…

    Microsoft Project Fx (mPfx) for Microsoft Project 2007

    Microsoft® Office Project 2007 (Microsoft Project) underscores Microsoft’s continued commitment to developers concerned with creating robust planning and scheduling tools for organizations with…

    Project Server Interface 101 Development Samples

    10 samples of how to do 101-level PSI development tasks

    Project Server 2007 Performance Lab Kit

    The Project Server 2007 Performance Lab Kit contains two Visual Studio solutions/tools to help you test your farm environment for capacity planning purposes: the “EPMTestTools” and the “EPM Stress …

    Project Server 2007 Report Pack II - "The Top Reports"

    The Project Server 2007 Report Pack II - published in August 2009 - provides reports that can be run in your EPM environment and enable easier access to EPM information that organization can …

    CodePlex

    http://www.codeplex.com/site/search?projectSearchText=epm

    CodePlex EPM solutions

     

    Project Server 2007 Timesheet & Statusing Customization Samples

    Code samples that demonstrate how EPM (Project Server) 2007 Timesheet and Statusing functionalities could be customized using custom event handlers and Project Server Interface calls. These samples leverage the Timesheeting and Statusing API documented in the EPM 2007 SDK

    Project Server 2007 VSTS Connector

    Connector solution for Project Server 2007 and Team Foundation Server

    Project Server 2007 Timesheet Tied-Mode Service and Event Handler

    This project is used with Microsoft Project Server 2007. It consists of an NT service, an event handler, a test app and installer. The event handler is fired when a tiemsheet is saved. The handler saves the timesheet info in a SQL table. The NT service (TSAutoStatus) polls...

    EPMSync Utility

    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...

    Project Server 2007 Test Data Population Tool

    The EPM (Project Server) 2007 Test Data Population Tool enables you to load large amounts of EPM data: resources, projects, tasks, assignments into a Project Server 2007 database. You can then use this data to test loads and help your organization plan for your Project Server 2

    Project Server 2007 Queue Watch Tool

    The Project Server 2007 Queue Watch Tool will help you monitor all queue activities for a specific Project Web Access (PWA) instance. This tool leverages the standard Project Server Interface publically documented web services to query and retrieve jobs in the Project Server ...

    Project Server 2007 Log File Report Tool

    The Project Server 2007 Log File Report Tool enables the import and the reporting of log file generated by your EPM & SharePoint farm. The Log File Report Tool will import log files (in an SQL database) from all servers in your farm with the ability to filter them by date. O...

    Project Server and InfoPath 2007

    This solution starter demonstrates how to leverage InfoPath and Forms Services as well Windows Workflow Foundation (WWF) hosted by SharePoint to create your own project initiation phase. The project initiation phase is different for every customers and organizations. It typic...

    EPM Custom Fields Copy

    The principal function of this project is to provide Project Server 2007 custom fields and lookuptables migration from development environment to production environment, using some PSI methods. I want to increase my inicial project, adding more funcionallities and solve some ...

    Project Server 2007 Timesheet data population tool

    The Project Server 2007 Timesheet Data Population Tool enables you to simulate timesheet entries in your farm. This tool can help you perform scalability studies of your PS architecture and validate the sizing of an existing architecture (by measuring timesheet queue throughpu...

    Search Project Server data using SharePoint Server's BDC and Enterprise Search

    Search Project Server data using SharePoint Server's BDC and Enterprise Search

    Project Reportcard

    The Project Reportcard was developed as a tool to help Project Managers assess their projects based on organizational standards implemented by their PMO. Understanding what the organizational KPIs are going to look like before their project update is published, can help a pro...

    Project Server 2007 Auditing Solution Starter

    EPM Auditing makes it easier to auditing and debug Projec Server 2007 activities. You can audit multiple activities and output them to multiple sources. It's developed in C#.

    Project Server Workspace Sync

    This tool works with Project Server. It is a very simple tool that iterates over the list of Project Workspaces and triggers user sync for the members in the WSS site and triggers the sync for issue, risks and deliverables with the reporting database.

    Earned Value Add-In

    The Earned Value Add-In has been developed as a tool to help project managers assess and visualize projects using earned value analysis.

    Project Server 2007 AD/Resource Sync Utility

    Demonstrates how to sync additional AD fields to resource custom fields for Project Server 2007.

    Persisting SSAS OLAP Roles In Project Server 2007

    During the standard Cube Building process in Project Server 2007, any manually added OLAP Roles in an OLAP database are deleted. The Cube building creates a default Role ProjectServerViewOlapDataRole that automatically adds all the Project Server users to this Role. This customiz

    Project Server 2007 Bulk Edit

    The purpose of this project is to allow the bulk edit of resource data. With large numbers of resources Resource Center can take a long time to load, primarily due to the security checks required. This tool goes directly against the Project Server Reporting store to pull back t

    Project Server 2007 Event Handler Admin Tool

    This project contains code demonstrating how to use the PSI to add and remove event handler associations for Project Server 2007. Instead of displaying all possible event handlers like the PWA admin screens, this tool uses reflection to show only the implemented handlers and all

    Project 2007 Test Framework

    Controller/client test framework can be used to test any assembly or .exe with and object model (any MS Office app).

    Project Server 2007 Timesheet AutoStatus Plus

    Project Server 2007 Timesheet AutoStatus Plus is a major rewrite of Christophe Fiessinger's EPM tied-mode work. Planned enhancements are: multiple instance support, farm support, and elimination of the Windows Service in favor of a SharePoint Timer Job.

  • Project Programmability and Business Intelligence

    Upcoming “Project Server Report Pack II - The top reports” WebCast

    • 1 Comments

    Please mark your calendar if you are interested! Are you asking what is the difference between the SDK Report Pack and this one? Think about the SDK version as the starter on “HOW” to write queries against Reporting database. This should go one step further – once you know HOW – you are interested in “WHAT” to bubble into your reports and “WHY” :)  This report pack has been built by top Microsoft Consultants who deploy/manage and enhance the “our” EPM solution for individual customers and contains very valuable know-how that they have collected over the years...

    Start Date: Wednesday, August 19, 2009 8:00 AM Pacific Time (US & Canada)

    Overview

    The “Microsoft Office Enterprise Project Management (EPM) 2007 Report Pack II -  The top reports” provides rich reports for common information needs. The EPM 2007 Report Pack II is a collection of 12 Microsoft SQL Server Reporting Services (SSRS) reports designed for various user groups of the EPM solution. In this webcast, we provide an overview and demonstration of the EPM 2007 Report Pack. We discuss the different usage scenarios the reports are tailored to fit and review the components used to move, store, and render the reports. With this information, you should have a better understanding of how to use the EPM 2007 Report Pack II to provide business value for your organization.

    Presenters: Bulent Guzel, Senior Consultant, Microsoft Corporation and T.R. Sloan, Solutions Architect, Microsoft Corporation

    Registration: https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032423031

    Enjoy!

  • Project Programmability and Business Intelligence

    Upcoming WebCasts and available On Demand developer training (a.k.a. EPMU)

    • 0 Comments

    Reporting (BI and Data Analysis)

    Date

    Name

    Description

    Presenters

    Registration

    Wednesday, August 19, 2009 8:00 AM Pacific Time (US & Canada)

    Microsoft Office Enterprise Project Management (EPM) 2007 Report Pack II - The top reports

    The “Microsoft Office Enterprise Project Management (EPM) 2007 Report Pack II -  The top reports” provides rich reports for common information needs. The EPM 2007 Report Pack II is a collection of 12 Microsoft SQL Server Reporting Services (SSRS) reports designed for various user groups of the EPM solution. In this webcast, we provide an overview and demonstration of the EPM 2007 Report Pack. We discuss the different usage scenarios the reports are tailored to fit and review the components used to move, store, and render the reports. With this information, you should have a better understanding of how to use the EPM 2007 Report Pack II to provide business value for your organization.

    Bulent Guzel  and T.R. Sloan -  Microsoft Corporation

    link

    Real World Solutions

    This upcoming Webcast series is unique – it just does not talk about particular feature for the sake of describing it, but it introduces a particular customer scenario and discusses a way to address customer need by extending Project Server by development project... Every WebCast will be accompanied by an extensive MSDN article with code samples! Very exciting! Delivered and presented by Microsoft Project MVP - Stephen C. Sanderlin from MSProjectExperts!

    Month Name Scenario Description Presenter
    Registration
    September 2009 Importing Workspace Data into Project Server Custom Fields A client has requested a Project Center view that displays the number of active issues for each project. This is not possible out of the box. Discusses a method of importing data from Project Workspaces (PWS) into a Project-level custom field Stephen C. Sanderlin, MSProjectExperts TBD*
    November 2009 Leveraging Project Server Security within SQL Reporting Services Reports A client has requested a SQL Reporting Services report that displays sensitive financial data. However, they only want executors of the report to see information on projects to which they have access. Discusses a method of leveraging Project Server security in SRS Reports Stephen C. Sanderlin, MSProjectExperts TBD*
    February 2010 Filter Resource and Project data based on Custom Fields You are creating a custom Windows application for a client that extracts and displays Project and Resource information. Because the PSI provides no way to filter these entities prior to retrieval from the PSI, you must retrieve all of the projects or resources in order to filter them by Custom Field value. Because this client has some international users on slow WAN links, you must minimize the payload sent to the user by the PSI call. Discusses a method of extending the PSI to enable the filtering of Resources or Projects by Custom Field values Stephen C. Sanderlin, MSProjectExperts TBD*
    April 2010 Exporting Project Server Data to CSV A client would like to export data from Project Server into a legacy Line of Business system. This system only accepts data in a CSV format. Discusses a method of exporting Project Server data into CSV files Stephen C. Sanderlin, MSProjectExperts TBD*

    *Registration URLs would be also announced at the Project Developer Portal and at this blog.

    On Demand EPM University Training       

    Remember the EPM University and the wonderful Developer course? It’s available as an on demand recording free of charge right here!

    Other Resources

    · Project Developer Portal on MSDN

    · Upcoming Project Conference http://www.msprojectconference.com/

    · Questions or feedback on Project Developer-related content? mailto:epmdev@microsoft.com

    Regards

    Jan Kalis

Page 1 of 1 (4 items)