by John Swaren
| September 25, 2014
With Customize View, TruePlanning™ offers great flexibility in editing favorites as row by column grids. Typical examples are Object by Activity, Object by Resource and Activity by Resource. But what if you wanted to view any of these grids also by year? In other words, how can we add a third dimension?
Last October, in my “Work Breakdown Structures are Workable!” blog, we discussed the use of Excel’s Data-Sort and Data-Group options to build up a WBS, for a MIL-STD 881 style view of Activity by Object as a linear list. Results were singular totals, which suited the customer for AoA comparisons of multiple project estimates, by column. But the customer later suggested extending the WBS list format, this time by expanding a particular project’s total by column years. After another customer asked essentially for the same (but for a Resource by Activity list by year), the requirement became to allow any selection/ prioritization of Object, Activity, and Resource as row-label delimiters (with yearly costs in columns). Visually, the challenge is shown by these 6 permutations:
The solution is a new Excel Add-In script to accommodate pivot-tables. We will launch this capability soon as another companion application, documented with step-by-step instructions. For now, I will overview here the overall utility, using the template above. Outputs 1&2 represent using Object as the primary row delimiter. Its application is typical for ROM or budgetary estimates, where costs represent product/service line-items (again, displayed by year in columns). For the following four object example, compare the views from a standard TruePlanning™ Object by Year output, to the Excel Pivot table’s:
Quite a difference! Note that only selected activities were expanded, to show the resource costs, as in Output #1. But what if we wish to re-order to produce a WBS view, with Activity as the primary delimiter followed by Object then by Resource, as in Output #3? It’s simply a matter of moving the row label order in the pivot table controller (seen again in the bottom-right corner here), as follows.
For a final example, to find my second customer’s budget-planning view of organizing first by Resource, then by Activity, then by Object as in Output #6 above, we again simply switch the order of row labels:
In fact, both customers needed their results grouped by Phase, as was accomplished in all three examples above. However, as you can see in the pivot-table controller, that delimiter does not necessarily have to take first precedence (on top). Perhaps a staffing plan would like to view (again with years as columns) the Resources first, followed by Activity, then by Object, then for completeness, by Phase. Just simply order the row labels correspondingly in the pivot table! Since Phase is also chosen, we essentially have four dimensions against yearly cost. In fact, in subsequent versions, we will add fiscal year as well as estimated hours, for this fifth dimension viewed. Any other metrics you can think of? Would you like me to send you an advance copy of this new Excel-based companion application? These tools are developed here because of your needs. We here at PRICE Systems are market-leaders, responsive and resourceful in finding solutions for you.