EPM Customization Taking your Microsoft EPM Solution Beyond

EPM Customization Taking your Microsoft EPM Solution Beyond the “box” Brendan Giles , PMP, MCP November 21 st, 2007 EPM Customization Series Part 2

Acronyms EPM n n n VBA n n Project Data Service – Project Server 2003 API. XML based request / reply. PSI n n Visual Basic for Applications programming language common to all Microsoft Office products including Microsoft Office Project. PDS n n Enterprise Project Management – Centralized control, Shared Resources, Portfolio / Project Reporting and tracking at the enterprise level. Project Server Interface – Project Server 2007 API. Web services based using ADO datasets and the. NET Framework. SOAP n Simple Object Access Protocol – Communication Protocol used to transfer data in a Microsoft EPM Solution via Http. Slide 2

Overview n n Microsoft Office Project Data Model Microsoft Office Project VBA Project Data Service API (Microsoft EPM 2003) and Enterprise Data Maintenance Project Server Interface (Microsoft EPM 2007) n Sharepoint Web Services n Questions and Wrap-up Slide 3

Project Object Model Slide 4

VBA Benefits VBA allows: n Access to Microsoft Office Project Objects n Access to other Office Applications n Macro recordings to expose VBA Code n Key data elements: i. Project ii. Task iii. Task Time-Phased iv. Resource Time-Phased vi. Assignment vii. Assignment Time-Phased Slide 5

VBA Code Techie Alert Sub Save. Plan(Share_Drive as String, Plan As String, Saved. Plan As String) 'Retrieve the specified project plan from Project Server 'and save it to the fileshare File. Open Name: ="<>" & Plan, Read. Only: =True, _ Format. ID: ="MSProject. MPP", Openpool: =pj. Do. Not. Open. Pool 'Don't Try to update this project on the server when I save it to a fileshare 'besides it is read-only so it won't let you File. Save. As Name: =Share_Drive + ": Latest Project Plans" & Saved. Plan File. Close pj. Save End Sub Slide 6

VBA Retrieval of Assignments Time-Phased Data For Each Assignment In Resource. Assignments ' Check if assignment is within our report range If Assignment. Start <= End. Of. Period _ And Assignment. Finish >= Start. Date Then Set tsvs = Assignment. Time. Scale. Data(Start. Date, End. Of. Period, pj. Assignment. Timescaled. Work, pj. Timescale. Days) Assignment_Remaining_Work_Hours = Assignment. Remaining. Work / 60 For Each tsv In tsvs If tsv. Value <> "" Then Assignment_Work_Hours = tsv. Value / 60 'time is in mins ' Create a DBRecord Entry for this effort Add_DB_Assignment _ Project_Name: =Saved_Project_Name(prj. Name), _ Workgroup_Category: =Resource. Enterprise. Outline. Code 4, _ Resource_Workgroup: =Resource. Enterprise. Outline. Code 2, _ Resource_Name: =Resource. Name, _ Task_ID: =Assignment. Task. ID, _ Task_Name: =Assignment. Task. Name, _ Task_Start_Date: =tsv. Start. Date, _ Task_Finish_Date: =tsv. End. Date, _ End If ' If tsv. value <>. . . Next tsv End If ' If Assignment. Start <=. . . Next Assignment Techie Alert Slide 7

VBA also Allows Office Integration Set cnt = New ADODB. Connection With cnt. Open db. Connect. Str 'Create the Assignments Table. Execute "CREATE TABLE Assignments_Table " & _ "([Assignment ID] COUNTER CONSTRAINT Primary. Key PRIMARY KEY, " & _ "[Workgroup Category] Text(250) WITH Compression, " & _ "[Resource Workgroup] Text(250) WITH Compression, " & _ "[Resource Name] Text(250) WITH Compression, " & _ "[Project Name] Text(250) WITH Compression, " & _ "[Task ID] NUMBER, " & _ "[Task Name] Text(250) WITH Compression, " & _ "[Start Date] DATETIME, " & _ "[Finish Date] DATETIME, " & _ "[Assignment Remaining Work Hours] NUMBER, " & _ "[Assignment Remaining Work Days] NUMBER, " & _ "[Assignment Work Hours] NUMBER, " ' ' Create the Tasks Table '. Execute "CREATE TABLE Tasks_Table " & _ "([Task ID] NUMBER, " & _ "[Project Name] Text(250) WITH Compression, " & _ "[Task Name] Text(250), " & _ "[Start Date] DATETIME, " & _ "[Finish Date] DATETIME, " & _ "[Task Remaining Work Hours] NUMBER, " & _ "[Task Remaining Work Days] NUMBER, " & _ "[Task Work Hours] NUMBER, " ‘End With Set cnt = Nothing End Sub Techie Alert Slide 8

VBA Object Model Changes n VBA changes in Project Professional 2007 n Simplified custom fields and outline codes n Multiple undo and redo actions n Effective calendar dates and calendar exceptions n Advanced desktop reporting using Visual Reports n Task drivers and recalculation change highlighting to help clarify scheduling processes n Costs and budget n Queuing Service events n Importing of a Windows Share. Point Services 3. 0 project n Saving of a local copy of a Project Server project for sharing Slide 9

Project Data Service n Steps to access all PDS Methods n Logon to Project Server Programmatically using valid ID and password n Obtain Authentication Cookie n Create a PDS Request in XML n Call PDS Web Service with PDS Request and authentication Cookie via a SOAP call n Receive PDS XML Reply n Parse PDS XML Reply Slide 10

PDS Authentication Cookie -<Reply> <Hresult>0<Hresult> <Cookie>(F 55 A 3……. . </Cookie> </Reply> Slide 11

PDS Logon Cookie private void btn. Chk. Cookie_Click(object sender, System. Event. Args e) { // Create a persistent cookie for the URL, if the cookie doesn't // exist or if the Project Server URL changes. if (url. Reset && ie. Cookies. OK) { Create. Cookie(COOKIENAME, VALUENAME, txt. MSPURL. Text, DAYSPERSIST); url. Reset = false; } //Clear the listbox, for subsequent creations of the project list lbl. Logon. Status. Text = ""; ps. Logon. Url = txt. MSPURL. Text + "/Lgn. Int. Au. asp"; string text. In. Reply = ""; if (Get_Cookie. Logon. Project. Server(ps. Logon. Url, ref ps. Cookie, ref text. In. Reply)) { //Trace. Warn("Logon Okay"); lbl. Logon. Status. Fore. Color = Color. Green; lbl. Logon. Status. Text = "Log on OK: " + txt. MSPURL. Text; ps. URL = txt. MSPURL. Text; //Check the PDS Call Here <----} else { //Trace. Warn("Logon Failed"); //Trace. Warn(text. In. Reply); lbl. Logon. Status. Fore. Color = Color. Red; lbl. Logon. Status. Text = "Log on failed"; } } Techie Alert Slide 12

PSI Login via Web Service public bool Logon. PS(bool use. Win. Logon, string base. Url, string user. Name, string password) { const string LOGINWINDOWS = "_vti_bin/PSI/Login. Windows. asmx"; const string LOGINFORMS = "_vti_bin/PSI/Login. Forms. asmx"; bool logon. Succeeded = false; try { if (use. Win. Logon) { login. Windows. Url = base. Url + LOGINWINDOWS; login. Windows. Credentials = Credential. Cache. Default. Credentials; if (login. Windows. Login()) logon. Succeeded = true; } } // Catch statements return logon. Succeeded; } Techie Alert Slide 13

Project Data Service n XML Based Request / Reply API n Consists of: n Project Methods n Enterprise Project Creation Methods n Resource Methods n Enterprise Custom Field Methods n Timesheet / Assignment Methods n Administrative Methods n Version Methods Slide 14

Enterprise Data Maintenance n n Builds on the PDS to provide higher level data exchange Uses “file drop” and SOAP programmatic interface Adds a business layer mapping to integrate with existing Project server data Tightly integrates Project server with other line of business applications n Resource data synchronization n Project Team Synchronization n Updating Custom field value lists Slide 15

Microsoft EPM Solution 2003 Slide 16

Microsoft EPM Solution 2003 Slide 17

Microsoft EPM Solution 2007 Slide 18

Project Server Interface (PSI) n Web Services API using ADO. NET Datasets n Over 350 Public Methods n Firewall Friendly (Ports 80 and 443) n Access via SOAP over Http n Managed Code but supports managed and unmanaged clients n VB. NET, C#. NET and ASP. NET development n Simply add a web reference (http: //servername/projectservername/_vti_bin/psi/. . n Better Design Time Control Slide 19

Project Server Interface (PSI) n Synchronous Methods n a. Synchronous (*Queue Methods) n Bulk data changes n Transactional / Restartable n Over 100 Server Events to listen for n ADO Datasets used for Data Transfer n Typed datasets that are familiar to developers and provide safety n Design Time Validation n Improved Performance Slide 20

Project Server Interface (PSI) Slide 21

Project Server Interface (PSI) Slide 22

Project Server Interface (PSI) n A Wealth of Web Services: n Object. Link. Provider n Admin n Project n Archive n PWA (Internal use) n Authentication (Internal Use) n Queue. System n Calendar n Resource n Cube Admin n Security n Custom Fields n Statusing n Events n Timesheet n Login Forms n View (Internal Use) n Login. Windows n Win. Proj (Internal Use) n Lookup. Table n Wss. Interop n Notifications Slide 23

Project Server Interface (PSI) Simply add a web reference in Visual Studio (http: //servername/projectservername/_vti_bin/psi/resource. asmx Slide 24

Project Server Interface (PSI) Simply add a web reference in Visual Studio (http: //servername/projectservername/_vti_bin/psi/. . Slide 25

Project Server Interface (PSI) View Web Service Properties: Slide 26

Project Server Interface (PSI) Expose the methods available ------ Retrieve data in an ADO Dataset PSI relies heavily on GUIDs (Globally Unique Identifier) in contrast to PDS that used IDs that were not always unique across Project Server Instances Slide 27

Project Server Interface (PSI) Slide 28

Project Server Events Applications can listen for Project Server Events Slide 29

Project Server Events Applications can listen for Project Server Events Slide 30

Project Server Interface (PSI) Developers should not directly Access Project Server Databases other than the Reporting Database n PSI Web Services and methods should be used n Slide 31

Sharepoint Web Services give developers access to Sharepoint Sites and their content n Slide 32

Sharepoint Web Services Windows Sharepoint Services 3. 0 for EPM 2007 Ships with Windows Server 2003. n Slide 33

Sharepoint Web Services Microsoft Office Sharepoint Server 2007 is recommended when using the workflow features of EPM 2007. n Slide 34

Sharepoint Web Services n Project Web Access is now a Sharepoint Site in EPM 2007 Slide 35

Fetching Issues via a Sharepoint Web Service Techie Alert Slide 36

Summary n Customization of Microsoft EPM via: n Visual Basic for Applications n Project Data Services API (2003) n Project Server Interface (API) (2007) n Sharepoint Web Services n Visual Studio n ADO. NET 2. 0 n . NET Framework Slide 37

Questions and Answers n ? ? No such thing as a stupid question ? ? n Answers ---- we’ll try to have smart answers Slide 38