by John Swaren
| August 27, 2015
Original Post Date: April 27, 2015
An Excel-based Pivot Table consolidates and summarizes data, with the flexibility of changing the order of organization – typically for sorting/aggregating subtotals by multiple designated dimensions. For example, the below shows two different orderings. The Left output sorts by Activity, then Object then Resource; the Right output sorts by Resource, then Activity then Object—
But what about aligning the objects displayed in the same order as originally in the TruePlanning PBS? In this blog, we will learn how to control and re-arrange output ordering. As we learned in the first Pivot Table blog, begin by launching the companion application, then click the Process File button on the TruePlanning ribbon to open a project file within in Excel. For example, note below how the initial default ordering is by PBS object, then Activities and then by Resources.
Again, per our first blog, in order to create the Pivot Table, a few steps are needed. First, select all the rows then under the Insert ribbon, click the Pivot table button.
The Pivot Table is created in a new sheet, with three key panels
As an example of creating orderings, on the top right side panel of checkboxes, select Cost Object, then Activity Name, then Resource Name, then Fiscal Year then Cost (or Hours). Note the bottom left Row Labels panel reflects these in-order selections (for any subsequent re-arranging by drag & drop). Note also that Cost is in the Values Panel controlling output and Fiscal Year is in the panel controlling the Column display
Here are the Pivot Table cost-sum results, in the order selected. Note the rows are, by default, alphabetical!
This blog asks how to align order with a TruePlanning PBS. The original order of objects can be produced by returning to the pivot table panel, clicking “Order” then ensuring it is the first Row Label below, i.e., above Object, via drag & drop—
Here are the corresponding re-ordered cost-sum outputs:
Note how Activities and Resources are still alphabetical. If desired, use the Activity & Resource order labels as well to align to the original PBS format—
Success can be accelerated by the PRICE® Cost Analytics (PCA) integration of business, engineering, and program management objectives in order to produce competitive solutions.
TruePlanning immediately facilitates objective quantitative assessment of customer needs/
requirements, winning-business solutions, and project lifecycle cost management.
Contact me for a demonstration, either in person or via telecon.
We’ve had many success stories here. You can be next!