Developing Project Essentials Applications – Reading and Updating Values for a Project

[reposted from http://ro.umt.com/blog/..]

The core functionalities of UMT Project Essentials 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) to perform basic cost management operations programmatically.

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 benefits data is similar, expect that the concept of cost centers does not apply.

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 Essential extends this concept with the notion of a financial project instance – a row in the CostInfo.cmInstances table. Each row corresponding to a project in Project Server, and it contains the current cost and benefits settings for the project:

  • ProjectGUID – used to relate to the Project Server project instance (required)
  • CostTreeTemplateGUID – the identifier for the cost tree template that is currently associated to project (required – project needs to have a cost tree template for any financial tracking)
  • CostDetailGranularityGUID – the identifier for the granularity at which the project budget cost values are editable (Entire Project, Years, Semesters, Quarters, Months); the identifier for each granularity can be referenced from the GUID attribute of the corresponding granularity class in the UMT.CostModule.Library.Core namespace (required)
  • CostDetailLevel – controls which level of the cost tree determines cost level aggregation; when a lower level (reflected by a higher value) of the tree is set as the Cost Detail Level, all levels above that level are read-only and are automatically calculated from values at the set Cost Detail Level (required)
  • 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)
  • StartDate – the date at which cost tracking starts (optional, but in order to store cost value for the project it has to be defined)
  • EndDate – the date at which the cost tracking ends (optional, but in order to store cost value for the project it has to be defined)

The list above covers just the properties that we will be using in this article. A separate follow up 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 are several ways in which an entry gets created for a project in the cmInstances table:

  • if a Cost Tree 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)
  • if the Enterprise Project Type does not have a Cost Tree Template associated, you can manually associate one to the project, and you can configure the financial settings on a per project basis

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.

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.

Budget Cost Values

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 ReadCostTemplate(Guid templateUid, bool includeStructure) method of the ProjectCost service:

  • templateUid – the identifier of the cost tree template, you can retrieve this from the project cmInstancesRow
  • 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) in the cmCostStructureTable, otherwise only the list of node identifiers is provided in the cmTemplateNodesDataTable

The cmCostStructure 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 ReadGranularityPeriodsForProject(Guid projectUid,Guid valueTypeUid, bool isCost) method of the Granularity service:

  • projectUid – the identifier of the project
  • valueTypeUid – specifies if you want the granularity periods for budget, actuals or forecast; you can reference the GUID from the UMT.CostModule.Library.Core.ValueTypes class for each value type
  • isCost – specifies if the time periods are for cost or benefits values

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, Q2 2012 (for Quarters 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 2 (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 ReadFinancialValues(Guid projectUid, Guid versionUid, Guid[] costCenters, Guid[] costNodes, DateTime startDate, DateTime endDate, Guid granularityUid, int nMaxLevel, Guid calendarTypeUid, Guid valueTypeUid, bool isCost) 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
  • versionUid – allows you to read the values specific to a certain version; Guid.Empty will retrieve the current data; use the Versions service to get the list of all the available versions for a project
  • 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 1st 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 CostDetailGranularityGUID value on the cmInstancesRow); 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 Cost Detail Granularity is Quarters, you request the data in Months granularity)

To exemplify, we will consider a project starting on the 7th of May 2012, and ending on the 24th 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)

Month period Period start date Number of working days Period value
May-12 05/07/2012 19 19 * 1000 = 19,000$
Jun-12 06/01/2012 21 21 * 1000 = 21,000$
Jul-12 07/01/2012 22 22 * 1000 = 22,000$
Aug-12 08/01/2012 18 18 * 1000 = 18,000$

(05/07/2012 – 08/31/2012)

80 = 80,000$ -> 1.000$ per working day

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

Month period Period start date Number of working days Period value
May-12 05/07/2012 19 19 * 1000 = 19,000$
Jun-12 06/01/2012 21 21,500$
Jul-12 07/01/2012 22 21,500$
Aug-12 08/01/2012 18 18 * 1000 = 18,000$

(05/07/2012 – 08/31/2012)

80 = 80,000$ -> 1.000$ per working day33,000$ / 2 complete periods
  • nMaxLevel –you can filter out the values by the depth of the structure; take into account that levels in PE start at 1, not at 0; for example, specifying 1, will bring just the values for the Total Costs node
  • calendarTypeUid – the calendar indentifier, Standard (Gregorian) or Fiscal
  • valueTypeUid specifies if you want the granularity periods for budget, actuals or forecast; you can reference the GUID from the UMT.CostModule.Library.Core.ValueTypes class for each value type
  • isCost – specifies if the values are for cost or benefits

To update the financial values, you use the UpdateFinancialValues(ValueInfo valuesDataSet, bool isCost) method:

  • valuesDataSet – the dataset with the changes
  • isCost – specifies if the values are for cost or benefits; you cannot update both cost and benefits at the same time

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, CenterGUID, StartPeriod, GranularityUnits, ValueTypeGUID, VersionGUID, IsCost 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. For example, in the following structure for a project having the cost detail level set to 3, though in the data set you pass to the update method you have added values for all nodes, some will be discarded:

Level Node UI Editable Aggregates to ancestors API Changes Behavior
1 Total Cost No (calculated) No (root) Discarded
2 -One Time Costs No (calculated) Yes (above detail level) Discarded
3 –Expenses Yes Yes (at detail level) Kept
4 —Labor Yes No (below detail level) Kept
4 —Training Yes No (below detail level) Kept
3 –Capital Expenditures Yes Yes (at detail level) Kept

You cannot update both cost and benefits at the same time; though the dataset allows you to add rows for both, the methods will throw an exception if this is the case. In the changes the dataset, you cannot have values all the values have to the at the cost detail granularity.

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

Sample Application

Sample Application

Sample files

About these ads

About UMT

UMT is a thought leader in project and portfolio management consulting, software products and training. UMT consultants provide end-to-end premier implementation and adoption services. We drive results using proven methodologies, technical expertise, training delivery and integrated solutions. UMT offers best in class software products based on hundreds of successfully completed client engagements over the last 20 years.
This entry was posted in UMT Project Essentials. Bookmark the permalink.

One Response to Developing Project Essentials Applications – Reading and Updating Values for a Project

  1. Pingback: Developing Project Essential Applications – Working with Cost Center Values | UMT Blog

Comments are closed.