The core functionalities of UMT Project Essentials 2012 deal with managing time-phased costs for projects.

In the current article we will analyze how you can use the CMSI (the service interface provided by Project Essentials 2012) to perform basic cost management operations programmatically and the differences between Project Essentials 2010 and Project Essentials 2012.

New concepts added in Project Essentials 2012:

•  Financial Dimensions: are used to capture cost and benefits values. Out of the box, Project Essentials 2012 offers 3 Financial Dimensions: Budget, Actuals and Forecast.Project Essentials Pro allows the user to define more custom Financial Dimensions using one of the 3 types as a base.For example: “Budget”, “Approved Budget”, “Remaining Budget” would be Financial Dimensions of type Budget“. Actuals” and “Approved Actuals” would be Financial Dimensions of type Actuals.By creating Financial Dimensions, the user can associate them with another Financial Dimension to map as needed. (i.e Forecast to Budget per Change Request Approval). A financial dimension association is unique which means that a financial dimension of type Actuals cannot be associated with multiple financial dimensions of type Forecast and a financial dimension of type Forecast cannot be associated with multiple financial dimensions of type Budget.
• Enterprise Financial Types: contains structures that are designed to be used for capturing time-phased data. These types (for example Cash Flow or Expenses) can be used to obtain financial metrics such as ROI or NPV. The concept of Enterprise Financial Types includes a hierarchical structure and various other settings (Cost Centers, Change Requests, etc.).

The article assumes you are familiar with the fundamental concepts of Project Essentials: cost structures, cost templates, cost centers, granularities etc. We will also take a closer look at some of these concepts and the way value distribution happens from a technical perspective. Most of the examples refer to cost data, but working with other enterprise financial type is similar.

In Project Essentials 2012, the concept of cost centers applies to Benefits as well.

When developing Project Server applications to manipulate data for a project, the starting point for any business logic is usually the ProjectRow entity in the ProjectDataSet. It contains the essential project data such as the identifier, the name, the schedule start and end dates.

Project Essentials 2012 extends this concept with the notion of financial project instance – a row in the FinancialTypesInfo.cmInstances table. Each row corresponds to a project in Project Server and contains the settings for the project:

• ProjectGUID – used to relate to the Project Server project instance (required)
• EFTGUID – the identifier for the Enterprise Financial type (required)
• ProjectName – project name
• WorkflowStageGUID – the identifier for the Workflow stage that the project reached
• CalendarTypeGUID – identifier of the calendar used by the project to manage financials, may be set as Standard Calendar (Gregorian) or Fiscal Calendar defined in PWA; you can reference the values from the UMT.CostModule.Library.Core.CalendarTypes class (required)
• CustomFieldValuesDirty – this value is set to “true” when custom field values are modified at project level. This field is used for reporting
• CurrencyCode – identifier of the currency that is set for the project (required)
• ChangeRequestApprovalGUID – the identifier for the approval setting.
• AllowPartialAggregation – value set to true if at stage setting level this option was enabled. This means that a percentage will get multiplied with the individual financial values of the corresponding linked project and the result will be the one that will get aggregated.
• EnableChangeRequests – value is set to “true” if change requests were enabled on stage settings.

Project timeline is stored in a different table: FinancialTypesInfo.cmInstanceTimelines.

• ProjectGUID – identifies the project (required)
• SettingsGroupGUID – the identifier for the entity setting (required)
• EFTGUID – the identifier for the Enterprise Financial type (required)
• StartDate – the date at which enterprise financial type values tracking starts (optional, but in order to store values for the project it has to be defined)
• EndDate – the date at which the enterprise financial type values tracking ends (optional, but in order to store values for the project it has to be defined)
• ValuesAreDirty – this value is set to “true” when financial values are modified at project level. This field is used for reporting.

The list above covers just the properties that we will be using in this article. A separate article will provide more information about the cost centers associated to a project, and manipulating actual values once the project reaches the execution stage.

The financial instance cannot be created explicitly through the API – it is the result of an automated process. There is a ways in which an entry gets created for a project in the cmInstances table:

• if a Financial Template is associated to an Enterprise Project Type, when a project is created based on the EPT, the workflow will automatically create the instance copying the financial settings corresponding to the workflow stage; when a project enters a new workflow stage, the settings are replaced with the settings of the new stage (it this way you can automatically enforce processes such as changing from yearly to quarterly granularity for more detailed cost tracking, increasing the cost detail level, control the display mode of the financial web parts)

In Project Essentials 2012, if the Enterprise Project Type does not have a Financial Template associated, you can’t manually associate one to the project.

To access the financial instance for a project, you use the ReadProjectInstance(Guid projectUid) of the ProjectIntegration Service.

Note, that if the value of the projectUid parameter does not correspond to a project in Project Server, you will get a NoPermission error. This is the same error you will get if the user that executes the code does not have the OpenProject permission.

The users interact with the cost values for a project by using the web parts provided by Project Essentials. All the operations that the user performs in these web parts can be achieved in a programmatic manner by using the CMSI. Let’s analyze the case of the budget cost values.

Section 1 (blue) contains the cost nodes from the cost structure that are available for the project through the associated cost tree template. To read this data you use the ReadTreeTemplate(Guid templateUid, bool includeStructure) method of the EnterpriseFinancialTypes service:

• templateUid – the identifier of the cost tree template, you can retrieve this from the cmEntitySettings
• includeStructure – if set to “true” the returned dataset will also include details about every node part of the template (the node name, the order, the level, the parent node) and the associated Enterprise Financial Types information. The following tables will be populated: cmEnterpriseFinnacialTypes, cmTreeTemplates, cmStructure and cmTreeTemplateNodeLinks. Otherwise only the template information will be provided in the cmTreeTemplates table.

The cmStructure table has a foreign key relationship with itself, based on which you can derive the parent-child hierarchy of the nodes in the template, and easily display them in a tree view. The root node (Total Cost) has the ParentNodeGUID value set to NULL, and since a single root is allowed, all the other nodes will have a value for the column.

Section 2 (red) contains the granular cost time periods for the project’s financial timeline. You do not need to compute these yourself, they are available through the GranularityReadPeriodsList

(DateTime startDate, DateTime endDate, Guid granularityType, Guid calendarType) method of the Granularity service:

• startDate – corresponds to cost start date
• endDate – corresponds to cost end date
• granularityType – the identifier for the project granularity, you can retrieve this value from cmEntitySettings table. For values that are not in execution, the corresponding column is BudgetGranularity, for actual values column is TrackingGranularity
• calendarType – identifier of the calendar used by the project to manage financials, you can retrieve this value from cmInstance row – CalendarTypeGUID

The method returns an array of TimePeriod objects, each object has the following properties:

• Name – the name of the time period, it depends on the granularity: Entire Project (for Entire Project granularity),  <year> like 2012,  2013 (for Years granularity),  S<semester_number> <year> like S1 2012, S2 2012 (for Semesters granularity), Q<quarter_numer> <year> like Q1 2012, Q22012 (forQuarters granularity),  <Month_name_first_3_characters>-<year_last_2_digits> like Jan-12, Feb-12(for Months granularity)
• StartDate – the start date of the period; the start date of the financial timeline is ignored, i.e. if you have a project with a Cost Start Date set to 01/05/2010, and Months granularity, the first time period will have the StartDate set to 01/01/2010
• Length60 ­– the unit length of the time period, that can be used to derive the end date; Project Essentials uses 1/60 sec as the time unit, days are considered to be 24 hour long (not 8 hours as the working days in Project Server), and only working days (according to the project calendar) are taken into account

TimePeriod.Lenght60 = NumberOfWorkingDaysInPeriod * 24 (HoursInADay) * 60 (MinutesInAnHour) * 60 (SecondsInAMinute) * 60 (SecondDivisions).

Section 3 (green) contains the cost values for the project, distributed in a time-phased manner across the cost nodes in the tree template. The ProjectValues web service provides the ReadFinancialValuesForProject (Guid projectUid, Guid eftUid, Guid dimensionUid, List<Guid> costCenters, List<Guid> costNodes, DateTime startDate, DateTime endDate, Guid granularityUid, int nMaxLevel, Guid snapshotGuid) method to read the values for a project.

The method parameters offer considerable flexibility for filtering and aggregating the values, just as the Project Essential web parts allow you to select the Display Calendar, the Display Granularity, the Display Detail Level and the portion of the timeline (Scroll to Year) for which to load the values. You do not need to roll out your own logic for these operations, just adjust the parameter values accordingly when making the method call:

• projectUid – the identifier of the project
• eftUid – the identifier of the Enterprise Financial Type
• dimensionUid – the identifier of the Financial Dimension
• costCenters – filters the data just for the specified cost centers; only applicable if the project has a cost centers template assigned, otherwise specify NULL
• costNodes – filters the data just for the specified cost nodes; if NULL is specified, the values for all nodes are retrieved
• startDate – filter the values for just a portion of the start timeline, starting from this date onwards; if you specify a date that follows the start date of the granular time period, the values for the period will be trimmed, based on a daily distribution (working days); if you want all the timeline value set this to the instance start date

Let’s assume a project with monthly granularity starting on the 1^st of May 2012, with a budget cost value of 23,000$ for the first months. May 2012, with Saturdays and Sundays as non-working days, has a total of 23 working. If we set the StartDate parameter to 05/07/2012, the value for the May-12 time period that you get from calling the method is 19,000$, since we have just 19 working days in the partial time period.

• endDate – same as the startDate parameter, you can filter the values for just a portion of the timeline, up to this date; a similar value trimming algorithm is applied according to the number of working days in the period; if you want all the timeline value set this to the instance end date
• granularityUid – the granularity at which you want the data to be aggregated in the response data set, you can specify any granularity, regardless of the granularity the project is set to use (the BudgetGranularity value on the cmEntitySettings); the value distribution algorithm used depends on the selected global setting when going from a coarser granularity to a more refined one (i.e. the Granularity is Quarters, you request the data in Months granularity)

To exemplify, we will consider a project starting on the 7^th of May 2012, and ending on the 24^th of August 2012, having quarterly granularity and a total budget cost value of 80.000$. We ask the ReadFinancialValues method for the full timeline values at a monthly granularity.

The distribution global setting is set to Daily – the quarter value is split to the total number of working days in the quarter (between the start and end dates for the project) to get a daily value, then for each month the daily value is multiplied with the number of working days in that month (again, considering the start and end dates for the project).

The distribution global setting is set to Evenly – the quarter value is split to the total number of working days in the quarter (between the start and end dates for the project) to get a daily value, then for the first and the end months the daily value is multiplied with the number of working days in that month (since they are ‘incomplete’ periods), the resulting values are subtracted from the total and the remainder is equally split between the other months (since they are ‘complete’ periods).

• nMaxLevel – you can filter out the values by the depth of the structure; take into account that levels in Project Essentials start at1, not at 0. For example, specifying 1, will bring just the values for the Total node
• snapshotUid – specify GUID empty for financial values. If you want to read snapshot/change request values specify the identifier of the snapshot/change request.

To update the financial values, you use the UpdateFinancialValues(Values valuesDataSet, Guid eftUid) method:

• valuesDataSet – the dataset with the changes
• eftUid – the identifier of the Enterprise Financial Type

The method will handle value additions, updates and deletions based on the DateRowState property of each row in the cmFinancialValues table. The recommended pattern is that you read the existing values using the read method and process the response dataset by either changing the Value property of the row for an update, adding a new row to add a value for a node that has no value, or by calling the Delete() method on a value row you wish to remove. The primary key for the cmFinancialValues table is composed from the ProjectGUID, NodeGUID, CostCenterGUID, StartPeriod, GranularityUnits, DimensionGUID properties.

The dataset returned by the read method, contains not just the nodes with ‘true’ values, but also the node with aggregated values. In turn, in the update dataset, there is no constraint to change the values for calculated nodes, but the internal product logic will discard these changes and enforce consistency based on the aggregation rules.

• If child node values are modified, values for parent node will be recalculated.

For example, in the following structure we have a project with the cost detail level set to 3, though in the data set you pass to the update method you have added values for all child nodes.

• If parent node values are modified, those values will be saved and all child node values will be lost.

For example, in the following structure we have a project with the cost detail level set to 3, though in      the data set you pass to the update method you have added values for all parent nodes, child node values will be discarded

Level	Node	UI Editable	API Changes Behavior
1	Total Cost	Yes	Kept
2	-One Time Costs	Yes	Kept
3	— Expenses	Yes	Initial values will be deleted
4	— Labor	Yes	Initial values will be deleted
4	—Training	Yes	Initial values will be deleted
3	–Capital Expenditures	Yes	Kept

• If both child and parent node values are modified, the aggregation will start from the maximum detail level of the modified rows.

Similar to the examples above, we have a project with the cost detail level set to 3 and in the data set you pass to the update method values for all nodes. The maximum detail level will be calculated (in this case it is 4) and values for child node will be kept and values for parent nodes will be discarded.

Level	Node	UI Editable	API Changes Behavior
1	Total Cost	Yes	Discarded
2	-One Time Costs	Yes	Discarded
3	–- Expenses	Yes	Kept
4	— Labor	Yes	Kept
4	—Training	Yes	Kept
3	–Capital Expenditures	Yes	Kept

 

Section 4 (yellow) contains the web part configuration information. In the Financial Settings part you can define the Enterprise Financial Type and the Financial Dimension.

There also a sample application included, as a proof-of-concept on how to use the CMSI methods that we’ve detailed.

 

Code Samples