by Ed Merriman
| September 26, 2014
TruePlanning provides a powerful and highly customizable reporting environment. Project data can be viewed from many different perspectives and those perspectives can be saved and reused across all projects. Results can be exported to Excel and Word as custom reports. There are, however, some instances where there is a need to get beyond the two axes used in TruePlanning’s reporting engine. This need is sometimes expressed by users preparing TruePlanning cost estimating project data for use in a bid or proposal. Perhaps the data needs to be split by phase and labor/non-labor over fiscal years.
In cases where TruePlanning needs to be viewed in a multi-dimensional format TPPivot provides a solution. TPPivot is an Excel plug-in that will take the data from a TruePlanning project and bring it into Excel in format that easily feeds the built-in pivot table functionality of Excel. Pivot tables are simple to set up with data in the proper shape, and provide great reporting power in an environment familiar to many people.
Once TPPivot has been installed (TPPivot requires an installation because it’s an Excel plug-in), launch the PRICE provided Excel file that provides access to the plug-in provided functionality. The Excel file will contain a TruePlanning ribbon:
The ribbon looks like:
Click on the “Process File” button and then selected the desired project:
The selected project will be interrogated and data from the project will be pulled into Excel. The data will be formatted in a way that easily feeds a pivot table and as such will be very detailed. Each row will contain the hours and cost for a single Fiscal Year of a Resource.
The pivot table uses the columns of target data as the axes. The data from the project will be formatted with the following columns, which will become axes:
Name of the immediate parent folder. Top parent folder will be the System Folder. This is used to provide sorting in the pivot table by folders. Parent folders can be multiple levels of indenture less than the Cost Object represented in the row.
The ordinal position in the PBS of the Cost Object represented in the row.
The name of the Cost Object represented in the row.
Cost Object Type
The definition type (System, Assembly, Software Component) of the Cost Object represented in the row.
The phase (Development, Production, Operation and Support) of the Activity represented in the row.
The order of the Activity in the PBS in terms of its position below the Cost Object.
The name of the Activity represented in the row.
The order of the Resource in the PBS in terms of its position below the Cost Object.
Name of the Resource represented in the row.
The type of Resource (Labor, Material, Other Cost) represented in the row.
The Fiscal Year represented in the row.
The number of hours for the Fiscal Year of the Resource represented in the row.
The monetary cost of the Fiscal Year of the Resource represented in the row.
The data loaded from the selected TruePlanning project is then selected in its entirety and inserted into a pivot table.
The above settings will result in a new sheet being added to the Excel file where the new sheet contains the pivot table environment.
From here users can set up the pivot using each column of the data as a unique axis. A typical report might be one where the “Values” axis is Cost, the ”Column Labels” axis is Resource Type, and the “Row Labels” axes are Phase, Order and Cost Object.
An examination of the resulting pivot table shows the multi-dimensional nature of the report. The data is organized by Phase and then PBS. In the above figure, the Development and Operation and Support phase are collapsed to highlight the multi-dimensional aspect of the data.
An astute reader will notice the need for the Order column of data. Excel’s pivot table will, by default, order data alphabetically so the order column is required to keep the PBS elements in the same order as they occur in the PBS. To compensate for this, the data can be copied from the pivot table to a new sheet in the Excel file, and the “Remove Order Numbers” button in the TruePlanning ribbon can then be used to remove the numbers to clean up the data, as seen in the next figure.