Exporting Linked Reports

The export to [anchor]Excel feature[/anchor] is designed to provide a more flexible way to build reports and update schedules. Custom spreadsheets can be configured based on any number and combination of objects and their data, allowing you work with as much raw data as desired in order to build custom reports and templates for updating schedules. Note that the resulting Excel files contain minimal formatting and presentation.

A few common combinations of data to export come pre-saved as templates to help get you started. Both default and user-created templates are shared at the system-level, meaning that a template created for one schedule will be available when working with any other schedule. You can choose to export data from an existing template as-is, reconfigure an existing template and then export the data, or create a new template altogether to export from. In addition, the export process can be automated and triggered directly from your desktop by specifying the desired schedule and report ahead of time. Instructions for each are provided.

If you’re exporting data to Excel to edit and update NetPoint, the fields that can be edited are identified in bold in the Configure Rows & Columns window in NetPoint and in the corresponding cells in Excel. All other columns will be protected, except for the code table, resource table, and target sheets (for more on protected sheets, see the notes at the end of this section). For more info on updating, see [bookmark postid=”14848″ anchor=”transfer”]Updating from Linked Reports[/bookmark].

WARNING: When a template is exported to Excel, the new Excel file will automatically overwrite the last-exported Excel file (for that schedule) unless the last one was renamed or moved. To avoid losing the previous Excel file, rename it or move it to a different location before doing the next export.

NOTE: The schedule will be saved each time a template is exported. To avoid having changes permanently saved, save the schedule as a separate document first, or use Undo to roll back the changes before closing the schedule.

Steps

Creating a New Template

  1. With NetPoint open, click File > Import/Export from the menu bar and choose Export to Linked Excel File.
  2. Click the New button.
  3. Make sure the circle is checked next to New Configuration.
    1. To remove or add object, check the boxes next to their labels.
    2. To include a field, select it in the Available list on the left and click the >> button.
    3. To remove a field, select it in the Included list on the right and click the << button.
  4. Once configured, click the Save button.
  5. Enter a description and click Save.
  6. To finish the export process, click the Export button. Excel will open with the exported data, and the file will be automatically saved in the schedule’s project folder.
  7. If you plan on exporting additional templates in the future, then we suggest renaming the file or moving it to a different location to avoid having it being overwritten. Click File > Save As from the menu bar, enter a name, and click SaveNOTE: If you DO rename or move the file, you can ONLY initiate an update from it from within Excel. For more info, see [bookmark postid=”14848″ anchor=”transfer”]Updating from Linked Reports[/bookmark].

NOTE: When adding/removing fields, fields within Object Data, Links Data, Resource Data, and Target Data will re-order themselves automatically. Fields within Predecessor Data, Successor Data, Tables, and Hammocks will depend on the order that they’re added.

[anchor]Modifying[/anchor]/Exporting an Existing Template

  1. With NetPoint open, click File > Import/Export from the menu bar and choose Export to Linked Excel File.
  2. Choose a desired template from the list.
  3. To export right away, click the Export button and skip to step 4. To modify first, click the Modify button. NOTE: Only user-created templates can be modified. If you reconfigure a default template, you will not be able to export it unless you first save it as a new template.
    1. To remove or add objects, check the boxes next to their labels.
    2. To include a field, select it in the Available list on the left and click the >> button.
    3. To remove a field, select it in the Included list on the right and click the << button.
    4. Once configured, click Save and continue to step 2e. To save it as a new template, click Save As.
      1. Enter a description and click the Save button.
    5. To finish the export process, click the Export button. Excel will open with the exported data, and the file will be automatically saved in the schedule’s project folder.
  4. If you plan on exporting additional templates in the future, then we suggest renaming the file or moving it to a different location to avoid having it being overwritten. Click File > Save As from the menu bar, enter a name, and click SaveNOTE: If you DO rename or move the file, you can ONLY initiate an update from it from within Excel. For more info, see [bookmark postid=”14848″ anchor=”transfer”]Updating from Linked Reports[/bookmark].

NOTE: When adding/removing fields, fields within Object Data, Links Data, Resource Data, and Target Data will re-order themselves automatically. Fields within Predecessor Data, Successor Data, Tables, and Hammocks will depend on the order that they’re added.

Automating the Export Process

  1. In windows explorer, navigate to the location where NetPoint has been installed
  2. Right-click on “NetPoint.exe” and click Create Shortcut.
  3. In the notification, click Yes.
  4. On the desktop, find the new shortcut and right-click and choose Properties.
  5. Next to Target, add the following to the end, without the quotation marks: (space) “path” (space) “/” “Report” (space) “template$1” (space) “template$2” (space) “etc.”, where (space) shouldn’t be spelled out but just a single space from your spacebar, “path” is equal to the filepath for your schedule, “Report” is just a required variable, and “template$n” is equal the name of the template(s) you want to export. Be sure to substitute any spaces in your template name with dollar signs ($), and don’t forget to remove the quotation marks above. For example, if you want to export the templates called “Default – All Objects” and “Default – Targets” for the schedule “Tech Transfer”, the target would look like the following (with the bold being what you would add):”C:\Program Files\NetPoint\NetPoint.exe” C:\Users\ Administrator\Documents\NetPoint Projects\Tech Transfer\NetPointFile.np4 /Report Default$-$All$Objects Default$-$Targets
  6. When finished, click OK.
  7. To initialize the export, double-click the shortcut or right-click and choose Open. The schedule will open in NetPoint and the report will open in Excel.
  8. If you plan on exporting additional templates on the same day, then we suggest renaming the file or moving it to a different location to avoid having it being overwritten. Click File > Save As from the menu bar, enter a name, and click Save.
  9. If desired, you may rename the shortcut by right-clicking on it and choosing Rename.
  10. Enter a name and click outside to finish.

NOTE: Upon export, the Excel file will be automatically saved in the schedule’s project folder and named after the template, with the month and day appended to the end. Excel files that are generated automatically can only have their changes pushed from within Excel. For more info, see [bookmark postid=”14848″ anchor=”transfer”]Updating from Linked Reports[/bookmark].

Notes

Working with Multiple Schedules

Only one linked Excel file can be open at a time. This means that when using File > Import/Export > Export to Linked Excel File, or Open Linked Excel File, any Excel files that were open for other schedules will be closed, and any unsaved changes will be lost. To have linked Excel files open for more than one schedule at a time, open them directly from their project folders or through Excel instead of through NetPoint.

Filename Incompatibility

Once exported, the Excel file will be automatically named using the same name as the project. However, Excel does not allow filepaths over 218 characters or which contain any of the following symbols: < > ? [ ] : | *. Any templates that are exported from NetPoint projects that do not satisfy these criteria will have to be renamed in Excel. As a result, you’ll only be able to initialize an update from such files from within Excel.

Tips for Excel

Field-to-Column Mapping

Each field that is exported will become a column in Excel, organized horizontally in the order that they appear in the list on the right of the Configure Rows & Columns window. At this time, fields/columns cannot be reordered after being added. For example, to move a desired field higher, remove the fields above it and re-add them back below.

Column Descriptions

The names of the column headers in Excel will be derived directly from how they appear in the list on the right of the Configure Rows & Columns window.

Sheet Structure

Depending on which information is chosen for exporting, the resulting Excel file may contain multiple sheets. Object data, including predecessor and successors, will be located on a sheet with the same name as the template itself. For target data, each target will get its own sheet. For resource data, each resource will also get its own sheet (or pair of sheets, if consumption and/or cost is exported per day or per month, the second sheet being reserved for a graph).

By default, all fields under the Object Data node in the Configure Rows and Columns window will be repeated on all other eligible sheets. This can be customized by right-clicking and choosing accordingly.

Unprotecting Excel Sheets

Any column that cannot be edited will be protected in Excel, except for the code table, resource table, and target sheets. When protected, some features in Excel, such as Find and Replace, may not be available. To access these features, unprotect the sheet in the Review tab or menu option using “NetPoint” as the password.

Cut/Copy/Paste

Only cells which can be edited will allow cut, copy, or paste, unless the sheet has been unprotected, as described above. In any case, cut and paste should be strictly avoided, as it will alter the underlying formula of the cells. As an alternative, you can right-click on a cell and choose clear contents.  Copy and paste may be used; however, when pasting, it is recommend to paste only values. This can be accessed via the paste options.

Project Info and Spreadsheet Key

The last sheet of every report contains information specific to the project:

  • Project filepath
  • Date exported
  • Name of the template
  • Time unit of the schedule
  • Project Start Date
  • Project Completion Date
  • Data Date
  • Asterisk Key

In addition, at the top of every sheet is a key explaining various aspects of the report.

Available Fields

[anchor]Object [/anchor]Data

If exported, each object that is included (activities, links, milestones, benchmarks, delays, hammocks) will become a row in Excel, organized in the order they were added to the network (system ID). The system ID will always automatically be exported, and will be the first column on the left, after the change log. After exporting, these fields will be located on a sheet with the same name as the template itself.

Field Definition
Object Type The type of object, whether activity, link, milestone, benchmark, delay, or hammock.
Description The object’s description. The description is for describing the object.
Abbr. Description The object’s abbreviated description. This abbreviated description is better for displaying on the canvas and is limited to 48 characters.
Start Date The object’s start date.
Finish Date The object’s finish date.
Duration The object’s duration.
Progressed Duration For in-progress activities, the amount of duration actualized up to the data date.
Remaining Duration For in-progress activities, the amount of duration unactualized after the data date.
Calculated Complete For actualized activities, the amount of progressed duration divided by total duration. If an activity or hammock contains an un-actualized portion before the data date, the calculated complete will export as NA.
Estimated Complete A user-entered estimate of work completed.
Calendar The object’s calendar. This calendar dictates on which days work may take place on the object.
ID The object’s ID. The ID is a unique identifier.
Start Constraint If applied, a constraint on the start date that prevents the object from starting earlier.
Finish Constraint If applied, a constraint on the finish date that prevents the object from finishing later.
Float* The float of the object. The float is the amount of time an object can be delayed before delaying the completion of the project. *An asterisk denotes that the activity is actualized, and that the value is forensic.
Total Float* The total float of the object. Total float is equal to float + drift. *An asterisk denotes that the activity is actualized, and that the value is forensic.
Buffer* The buffer of the object. The buffer is the amount of time an object can be delayed before delaying the start of its nearest successor. *An asterisk denotes that the activity is actualized, and that the value is forensic.
Drift* The drift of the object. The drift is the amount of time an object can gain before advancing the start of the project. *An asterisk denotes that the activity is actualized, and that the value is forensic.
Drift Buffer* The drift of the object. The drift buffer is the amount of time an object can gain before advancing the finish of its nearest predecessor. *An asterisk denotes that the activity is actualized, and that the value is forensic.
Grid The gridline or row number that the object is drawn on.
Codes The codes and values assigned to the object. Where an assignment exists, the cell underneath the value’s column will get an “x”. Export this field for modifying the codes assigned to an activity or doing global assignments.
Code Summary A comma-separated list of all values (if any) of a particular code assigned to the object. Where an assignment exists, the cell underneath the code’s column will contain the value.
[anchor]Predecessor[/anchor] and Successor Data

If chosen for exporting, predecessor and successor data will be added to the main sheet (the sheet with the same name as the template itself). Note that for every object, all predecessors and all successors are grouped together in one cell for each type. For example, if “Activity A” is a predecessor to “Activity B” and “Activity C,” then both “Activity B” and “Activity C” will show up under the “Successor Description(s)” column, separated by commas. The same goes for their IDs and/or finish date(s).

Field Definition
Predecessor(s) Description-Relationship All, if any, of its predecessors’ descriptions follow by their relationships. Relationships are displayed as either FS (finish-to-start), SS (start-to-start), or FF (finish-to-finish) followed by the link gap, e.g. “Activity1-SS0”.
Predecessor(s) ID-Relationship All, if any, of its predecessors’ given IDs followed by their relationships. Relationships are displayed as either FS (finish-to-start), SS (start-to-start), or FF (finish-to-finish) followed by the link gap, e.g. “12345-SS0.”
Predecessor System ID(s) All, if any, of its predecessors’ system IDs.
Predecessor ID(s) All, if any, of its predecessors’ given IDs.
Predecessor(s) Description All, if any, of its predecessors’ descriptions.
Successor(s) Description-Relationship All, if any, of its successor’s descriptions follow by their relationships. Relationships are displayed as either FS (finish-to-start), SS (start-to-start), or FF (finish-to-finish) followed by the link gap, e.g. “Activity1-SS0”.
Successor(s) ID-Relationship All, if any, of its successor’s given IDs followed by their relationships. Relationships are displayed as either FS (finish-to-start), SS (start-to-start), or FF (finish-to-finish) followed by the link gap, e.g. “12345-SS0.”
Successor System ID All, if any, of its successors’ system IDs.
Successor ID(s) All, if any, of its successors’ given IDs.
Successor(s) Description All, if any, of its successors’ descriptions.
Link Data

If exported, link data will show up in its own sheet called “Links”.

Field Definition
Predecessor The beginning object in the relationship.
Successor The ending object in the relationship.
Type The type of relationship of the link. Relationships can be either FS (finish-to-start), SS (start-to-start), or FF (finish-to-finish).
Lag The lag, if any, of the relationship. The lag is the amount of time after the start of a predecessor and the start of its successor or the finish of a predecessor and the finish of its successor in a start-to-start relationship or finish-to-finish relationship respectively.
Gap The gap value of the link. The gap is the duration of a link.
Logic Type The logic type of the link. For more info, see [bookmark postid=”5568″ anchor=”link”]Creating FS Links[/bookmark].
Shape The geometry of the link. For more info, see [bookmark postid=”5572″ anchor=”scoring”]Optimizing Link Geometries[/bookmark].
Resource Data

When exporting resource data, most of the fields will become columns in Excel, organized horizontally in the order that they appear in the list on the right of the Configure Rows & Columns window. Specifically, Rate/Dur, Rate/Day, Total Consumption, Total Cost, Consumption, and Cost are repeated for every resource and put on their own sheet with Consumption and Cost actually spanning several columns, one column for every unit of time during which the project takes place. If multiple units of time are chosen (e.g. days and months), then all of the sheets for the first time unit will show up followed by all of the sheets for the next time unit. A resource’s sheet can be identified by its name, which follows as “Res-[description]-[internal ID]-[time unit]”. For example, if resources are exported by day and the schedule contains a resource named “Plumber” with an internal ID of “5,” then the sheet would be titled “Res-Plumber-5-day”. At the end of the Excel file, a resource “summary” sheet will be added for each time unit exported.

Graph Consumption and Graph Cost are also repeated for every resource and put on their own sheet at the beginning of the Excel file, with all of the consumption sheets showing up first followed by all of the cost sheets. If multiple time units are chosen (e.g. days and months), then all of the sheets for the first time unit will show up followed by all of the sheets for the next time unit. For example, if Graph Consumption and Graph Cost were exported by days and months for a schedule with 3 resources, there would be 3 sheets for consumption/day and 3 sheets for consumption/month followed by 3 sheets for cost/day and 3 sheets for cost/month. These sheets follow a similar naming convention, which follows as “type-description-ID-time unit”. For example, if consumption and cost are exported by day, then the consumption and cost graphs for the same Plumber resource would be located on the sheets titled “Cons-Plumber-5-by-day” and “Cost-Plumber-5-by-day”, respectively. Before each of the consumption and cost sheets, a “total” graph will be added for each time unit exported.

NOTE: For graphs to be exported, their data must be included too. For example, for Graph Cost to be exported, Cost must be included for at least one time unit; for Graph Consumption to be exported, Consumption must be included for at least one time unit. If an attempt is made to add a Graph Cost or Graph Consumption without at least one time unit for Cost or Consumption respectively, NetPoint will choose a default time unit (year) and add it with the graph. Likewise, if Cost or Consumption are completely removed from a template, their graphs will be removed as well.

NOTE: Depending on your machine, choosing to export the graphs may add a significant amount of processing time.

Field Definition
Rate/Day The quantity of this resource assigned to that activity on a per day basis. Export this field for modifying the resources assigned to an activity or doing global assignments. To assign a resource per day, enter a value in the Rate/Day column, AND make sure there is a no walue or a “0” in the Rate/Dur column. As soon as there is a value in the Rate/Dur column, any value in the Rate/Day column will not be used. 
Rate/Dur The quantity of this resource assigned to that activity on a per duration basis. To assign a resource per duration, enter a value in the Rate/Dur column. As soon as there is a value in the Rate/Dur column, any value in the Rate/Day column will not be used. If this column contains a “0”, the value from Rate/Day will be used instead.
Total Consumption The rate/day times the duration of the activity (or the rate/dur).
Total Cost The cost/day time the duration of the activity.
Consumption A per time unit breakdown of the consumption of this resource over the duration of the schedule. Consumption will span several columns, one column for every unit of time during which the project takes place. Right-click in the list on the left to add individual time units; right-click in the list on the right to remove them. If added without choosing, years will be the default.
Cost A per time unit breakdown of the cost of this resource over the duration of the schedule. Cost will span several columns, one column for every unit of time during which the project takes place. Right-click in the list on the left to add individual time units; right-click in the list on the right to remove them.If added without choosing, years will be the default.
Graph Consumption Whether or not to include graphs of all the consumption data. The consumption field must be included for at least one time unit.
Graph Cost Whether or not to include graphs of all the cost data. The cost field must be included for at least one time unit.
[anchor]Target [/anchor]Data

When exporting target data, the reported fields will be repeated for every target, with each target getting its own sheet named after the number of the target followed by the description. For example, the first target’s sheet could be named “Target1-Baseline” followed by “Target2-Update” and so on. Like with object data, each field that is exported will become a column in Excel, organized horizontally in the order that they appear in the list on the right of the Configure Rows & Columns window.

NOTE: If no targets have been stored in NetPoint, then no sheets will actually show up in Excel for targets.

Field Definition
Target Start Date The object’s start date when the target was captured.
Start Date Variance The target minus the current, in time units (i.e. days).
Target Finish Date The object’s finish date when the target was captured.
Finish Date Variance The target minus the current, in time units (i.e. days).
Target Duration The object’s duration when the target was captured.
Duration Variance The target minus the current, in time units (i.e. days).
Target Float The object’s float when the target was captured.
Float Variance The current minus the target, in time units (i.e. days).
Target Total Float The object’s total float when the target was captured.
Total Float Variance The current minus the target, in time units (i.e. days).
Target Drift The object’s drift when the target was captured.
Drift Variance The current minus the target, in time units (i.e. days).

Tip: At this time, variances are only calculated to the current state, not between any two targets, but such reports can be exported from the Target Manager by clicking Tools > Open Targets Manager, right-clicking on the columns, and choosing Export all Rows & Columns. 

[anchor]Tables[/anchor]

Tables are another way for adding or deleting codes and resources from the schedule. When exported, they will be put in their own separate sheets. For more instructions, see [bookmark postid=”14848″ anchor=”adding-and-modifying-codes-via-excel”]Adding and Modifying Codes via Excel[/bookmark] and [bookmark postid=”14848″ anchor=”adding-and-modifying-resources-via-excel”]Adding and Modifying Resources via Excel[/bookmark].

Field Definition
Code Table An editable table for adding, modifying, and deleting codes. Export this table for managing codes and values.
Resource Table An editable table for adding, modifying, and deleting resources. Export this table for managing resources and cost.
Hammocks

Hammocks will show up on the main data sheet.

Field Definition
Min Member Drift The lowest drift value of all a hammock’s members.
Min Member Float The lowest float value of all a hammock’s members.
Min Member Buffer The lowest buffer value of all a hammock’s members.
Min Member Total Float The lowest total float value of all a hammock’s members.
Member List A list of all members of the hammock.