Microsoft Project Export-Import and Roundtrip

Microsoft Project Integration

With the new Microsoft Project integration users can exchange data with Microsoft Project for better planning and scheduling tasks and resources.

That is:

• Users can import existing Microsoft Project documents to Codebeamer
• Users can export Codebeamer tasks to Microsoft Project
• Roundtrip exchange of data: the previously exported and adjusted tasks of Microsoft Project can be imported back to Codebeamer to update the scheduling and assignments of the corresponding tasks in Codebeamer.

Let's see the details:

Exporting to Microsoft Project

Exporting to Microsoft Project will export the most important project-planning, resource and task information

from Codebeamer for processing it in Microsoft Project.

Initiating an Export in Codebeamer

The Microsoft Project export just accessible via the same menus and appears on the same dialog where the Word and Excel exports appear.

Just go to any tracker (Bug/Task/Requirement) and in the "more" menu you will find the "Export to Office" option. This is available both on the normal table-view of a tracker and when using the Document View as well.

Then in the export dialog choose the "Microsoft Project export" option.

The export will start and an XML file will be generated soon and download will start.

Unfortunately the export XML file can not be directly opened in MS-Project, and an error will appear if you try to do so:

So to open the export XML file first:

• save the XML file to file-system
• use the File->Open menu from MS-Project and open the XML file there
• the import wizard will appear, typically just use "As a new project" option to open it:

What is Exported?

The export contains followings:

• Tracker-items
  The items (tasks/bugs/requirements) from the current tracker as filtered by the current tracker-view. From each item in Codebeamer one Task with the same name will be created in MS-Project.
• All children items
  The parent-child relationship is kept during export, so the same structure will appear in MS-Project as appeared in Codebeamer. Exporting of all children is necessary to get a complete picture of the Tasks and their relations in MS-Project for planning.
• Planning properties
  These are mapped to the appropriate Task fields in MS-Project. Such are for example the "Planned Effort" and "Spent Effort" fields, or user assignments.
• User/Role assignments
  The user/role assignments are converted to Resources in MS-Project and assigned to appropriate Tasks
• Dependencies
  Dependency associations between the exported items are converted to predecessor-successor relationships in MS-Project.
• Other properties
  Other non-planning related properties from Codebeamer are mapped to custom fields in MS-Project

Mapping of Codebeamer Fields to MS-Project Tasks

The mappings is:

Item Description and Hyperlink to Item

The description of the items will appear as a small Note icon in front of the Tasks in MS-Project.

The limitations are:

• The description appears as wiki text only, no rich text is possible here
• Only first 1000 characters may appear here, the rest is cut down (size limitations)

Also a small blue link icon will appear in front of each Task, which is a hyperlink pointing back to Codebeamer. By clicking on that the original item will open in the web-browser, which makes navigation from MS-Project very easy.

Scheduling: Dates, Durations and Progress

As mentioned the "Start Date" and "End Date" from Codebeamer are exported to "Start" and "Finish" properties to MS-Project.

The duration fields like the "Estimated Hours" and "Spent Hours" are exported to the "Duration" and "Actual Duration" fields. Also MS-Project will automatically calculate the "% Complete" value from the ratio of "Actual Duration"/Duration.

Task types: Manual versus Automatic Scheduling

Any Tasks in MS Project can be either in Manual Scheduling mode or in Automatic Scheduling mode. This is an important setting, which affects how the Task is scheduled and handled by MS Project in general. That is:

• The Manual scheduling mode means that users has complete control over how the tasks are scheduled: the user can set any durations, dates, dependencies manually which are respected by MS Project and will not be updated automatically by MS Project.
• The Automatic scheduling mode means that MS Project will automatically calculate and update the dates,durations and generally the schedule from the dependencies between Tasks, calendars and many other factors may appear.

For more information see: Manual vs automatic schedule: How scheduling works in Project?

In Codebeamer MS Project export and import you can control how the exported Task will be scheduled on the UI. The options are shown in this screenshot:

So either:

• Set Manual schedule for all tasks
• Set Automatic schedula for all tasks
• Choose Smart mode where Codebeamer export will determine the schedule mode for each Task

The "Smart" mode means that the Task will have Automatic schedule if:

• The work item has an special tag "MS-PROJECT AUTO_SCHEDULED" added to that, or
• The start or end date of the work item is empty

The "MS-PROJECT AUTO_SCHEDULED" or "MS-PROJECT MANUALLY_SCHEDULED" tags are added during import from MS Project in order to keep this information for the future updates. You can also add these tags manually if you want to control how each task is exported.

That is how the tag looks like on the a work-item:

Converting Between MS-Project Durations and Hours in Codebeamer

In MS-Project the task durations can be set to values like "1 day" or "1 week" or "1 month", however these has different meaning from the traditional day or weeks, as they mean actually one work day or one work week.

One work day is typically 8 hours, and one work week is typically 5 "work days", which mean 40 work hours. Even these numbers may vary depending on the calendar, yet during export/import to Codebeamer such differences are ignored.

However to get an approximately correct conversion from the MS-Project durations like "1 day" during the import we take account of these differences. The meaning of each duration is automatically picked up from the settings in the MS-Project file. These can be found by using the "File->Options" menu inside MS-Project, and then click on "Schedule". There you will see these settings:

These values can be configured here, and their defaults are:

The same values are also configurable during MS-Project export, which puts these values to the export file:

User/Role Assignments to Resources

As the items (tasks/bugs etc...) can be assigned to several users and roles in Codebeamer these assignments are exported to as Resources and Resource assignments to MS-Project.

The rules are:

• For any unique user appears in "Assigned To" field in Codebeamer one corresponding Resource is created, and that Resource is assigned to the appropriate Task
• For any roles appearing in the "Assigned To" field also an MS-Project Resource created. As naming convention the "Role" text is added to roles' names, for example the "Tester" Codebeamer role is becomes an "Tester Role" Resource.
• For easier navigation these Resources get a hyperlink back to Codebeamer similar to the issues.

Here is an example Resource:

Here is how the AssignedTo converted to Resources:

Exporting Dependencies

In MS Project you can define dependencies between Tasks, and such dependencies will drive and greatly determine the schedule calculations done by MS Project.

The MS Project export of Codebeamer will also create the appropriate dependencies. If there is a "depends on" association between two exported work-items (bugs/tasks) then the generated MS-Project export file will also contain these.

For example the "Secure project sponsorship" contains a dependency to "Determine project scope" task. When exporting that will appear in the Predecessor field as dependency in MS Project.

Following screenshot illustrates how a "depends on" association in CB will create an Predecessor dependency in MS Project.

What is happening here:

• All "depends on" association between two item will be exported to MS Project.
• Any other kind of association (like "related to" or "copy of") will not be exported to MS Project.
• When importing back a new MS-Project dependency into Codebeamer that will create a new "depends on" association in Codebeamer. Such associations are recognizable by the "MS-Project dependency" comment text. For more details see dependency importing later.

See: Linking Project tasks tutorial

Priority Mapping

This is how the CB priorities are mapped during export:

The Requirements trackers has different priority levels which are actually stored in the Business Value field. These are mapped as:

During the import the mapping is reversed. However because the priorities in MS-Project can be an arbitary number between 0-1000, the mapping is performed to the closest priority number in the mapping table above.

For example the 310 priority is mapped to "Low", while the 490 priority number is mapped to "Normal".

Exporting Story Points

During export the Story points assigned to Codebeamer work items can be converted to the "Duration" hours in MS Project. This conversion is only happening if you enter a ratio of how many hours does a Story point worth. That is appearing on the export page:

For example if you enter the number 4 there that means that every Story Point worths 4 hours. So a 2 Story Point value will become 8 hours in the Duration of the appropriate MS Project task.

Note however that Story Points are only converted if the exported work item's Estimate Hours field is empty. This won't overwrite the value there!

Export of Other Codebeamer Properties as Custom Fields

Any other Codebeamer fields which are not mapped to a specific MS-Project field are exported as custom fields. Such fields get a "cb_" prefix, for example "Submitted by" appeas as "cb_Submitted by" in MS-Project. Such fields can be displayed and used in various views of MS-Project:

These are available for convenience and for analysis, yet these are not used nor imported back later from MS-Project during roundtrip.

Known Limitations of Exporting

• Custom fields (Text1...Text30 in Microsoft Project) can not contain new lines and must be less than 4000 chars. See Custom fields Therefore these custom fields are abbreviated (cut to 1000 chars), and all new lines are removed from them
• The "Note" field contains the "Description" of the exported task, but that is abbreviated to 1000 chars.

Missing pieces:

• Assignment of Roles is not clear how to do it, now just a Resource called like "Developer Role" is created and assigned to the Tasks
• When an issue has multiple assigned-to users/roles it is not always appearing as "selected" resource in MS-Project
• When importing a Task with Resource and that resource/user is not found in Codebeamer then that information is "lost"
• Idea: add a custom field -like cb_User- to Resources which allows mapping any MS-Project resource to CB users or roles. Handle this field during export and import.

Importing from Microsoft Project

You can also importing planning information and Tasks from Microsoft Project into a tracker. That way:

• You can import a new MS Project (.mpp) file to Codebeamer and create new work items from Tasks
• You can import a previous MS Project export file of Codebeamer back, which will update the work items in Codebeamer.
• While updating the Codebeamer work items you can also create new CB work-items at the same time.

The import will:

• Automatically detect MS Project Tasks which already exists in Codebeamer and update them
• Update the parent-child hierarchy in Codebeamer to reflect the possibly changed hierarchy of Tasks in MS Project
• Update the Predecessor-successor dependencies in Codebeamer to reflect such changes in MS Project

Initiating an Import in Codebeamer

The MS Project import can be found on the same location where other kind of imports (Word and Excel) are: in the tracker's "more" menu by clickin on the import will start the import wizard where MS Project files can be imported too.

You can upload an *.mpp or a *.xml file (the latter is the XML export compatible with Microsoft Project) here.

After an MS Project file is uploaded the importer wizard will behave similarly to how it works with Excel import or Excel roundtrip. Each Tasks from the MS Project data will in a new line, with the importable Task properties are in one column. You can also adjust mapping and do conversion if necessary the same way as you would do with an Excel import there.

Mapping Fields During Import

Mapping during import is automatic, and it is essentially same of what happens during export: Microsoft Project Export-Import and Roundtrip

The key differences are listed in the followings.

Finding and Updating Existing Work Items: Hyperlinks to ID Field

When importing a MS Project to Codebeamer the importer will look for the "Hyperlink" field of MS Project tasks, and if that hyperlink points to a Codebeamer work item (bug/task etc...) then the import will update that found work item.

This is how you can synchronize the existing work items with the MS Project tasks. The ID-s of the work item's found are appearing on the import UI as following screenshot shows:

Creating New Work Items from Tasks

You can also add new Tasks in MS Project to an export document originating from Codebeamer. When adding a new Task Codebeamer will automatically recognize that this is a new Task (because it has no hyperlink to a Codebeamer work item).

The importer GUI will indicate that this is a new data, and a new work item (but/task etc...) will be created from this.

As consequence you can use following tips:

• By removing the "Hyperlink" from any Task in MS Project will force creation of a new work item in Codebeamer.
• If you remove the "ID" mapping during import completely you can import all MS Project tasks to any tracker as new work items.

Importing to Description

As mentioned during the export the "Description" field of CB work items are exported to the "Notes" field in MS Project. You can potentially modify this information by editing the Nodes field, however you can not modify the Description field this way, because the import ignores this field for existing work items. (It is ignored because this field is typically abbreviated to 1000 characters)

However for new work items being created the Notes field will be used, and will be inserted as Description field.

Scheduling Fields: Start and End Date, Planned Effort and Spent Effort

The Start/End date and Planned Effort/Spent Effort fields are imported from the appropriate Task fields of MS Project. The Durations like "1day" is automatically converted to the appropriate number of hours (8h) according to the Duration conversion rules described earlier.

These fields are typically calculated or updated by MS Project during the work in MS Project, therefore these are likely will be changed.

Importing Parent-Child Hierarchy

The import will also update the parent-child hierarchy of existing work-items. So if you would for example move a Task in MS Project to a different parent then this change can be reflected in Codebeamer too.

So the import will automatically update the parents from MS Project, except if you turn this feature off on the UI. Turning off means that parent-child hierarchy won't be imported, and the hierarchy of work-items in CB will remain unchanged.

Importing Dependencies

When importing from MS Project the importer will also take care of importing Predecessor-Successor dependencies between Tasks. That is performed by creating "depends on" associations between the imported work items. See also: exporting dependencies.

The importer creates a special "depends on" association for such imported dependencies. That is recognized from its comment as shown below. The comment also contains the Predecessor dependency type.

The MS Project importer and exporter automatically synchronizes dependencies such a way that:

• The special "MS-Project" import dependency will be removed by the importer if reimporting the same Task/work-item, but that dependency is removed in "MS-Project".
• The special "MS-Project" import dependency is created automatically to preserve this information during imports if necessary.
• Both export and import preserves the type information like Finish-to-start type if possible.
• If a manual dependency (manual means not created by importer) already exists that is always kept and never removed.

Assignments

The Resource assignments are imported too from Tasks to "Assigned to" fields.

The mapping rule is simple - same as during export -: it tries to find the same account by name as the Resource's name. Roles are found/mapped using the "XXX Role" naming convention, for example "Tester Role" resource is mapped to "Tester" role.

However if you want to bind a certain Resource in MS Project to a certain user-account or role in Codebeamer that can be done by:

• add a new custom field called "cb_account" to Resources
• set the "cb_account" field 's value to the Codebeamer user account name or the role name.

For example: you have a "Management" resource in MS Project. By setting the "cb_account" custom field to "bond" will make assign the Tasks of this Resource to "bond" user when importing to Codebeamer.

These screenshots show how to do this: