With this post, we’re pretty much at the finish line. Now that we have the macro, the baseline mechanics, and the query all figured out, it’s time to flex those Excel muscles and see if we can’t generate a report that looks the way we want it to.
First, off we’ll start with the Pivot Table generated from the query in the last post. That table should look something like this….
Changing the cost fields to a sum, and the status date to a maximum value, we end up with something like this….
Convert the cost fields to a running total, and we get this….
From here, it should be a relatively easy exercise to add a Pivot Chart (shown here with the Status Date removed)….
That would be easy. But remember, in the last post I mentioned that I am taking the Gilyaks of Murakami’s 1Q84 as my inspiration. They would never take the easy route. No, they would look at the easy route with incomprehension, then continue the painful slog along the slow route right next to it….for no apparent reason. And that, I think is an excellent description of this series of posts.
So my issue with the chart above is that it doesn’t display the status date. I want the ACWP and BCWP lines to truncate at the status date. That being said, I don’t feel quite up to the challenge of figuring that bit of functionality out.
So for now, I’ll settle for just adding a vertical line where the status date should be. As it turns out, that gets a bit complicated, because to do so, we need to add a second chart type, which is not allowed when creating a chart based on a Pivot Table.
What to do when stumped in Excel? Why turn to Jon Peltier’s excellent blog on Excel tips and tricks. In this case, this post on developing a non-Pivot Chart chart on a Pivot Table did the trick.
After creating the Pivot Table, I added the series one by one back onto a blank line chart. When it came time to add the Status Date, I added it, then converted it to an X, Y scatter chart where the X coordinate is the Status Date and the Y coordinate is the BCWS value.
The end result is something like this….
In a real implementation, I might recommend leaving it as a Pivot Chart though – as that’s a lot more dynamic and easy to change up for different projects. On the other hand, once the data is available, it’s not too hard to generate a chart like this.
And there we are. From 0 to enterprise timescaled data in four easy posts….
Finally the credits….thanks to a whole slew of folks for providing the support to figure this out and allowing me to bounce ideas off of them. This effort truly was an international one, with fellow MVPs Barbara Henhapl, Vadim Gerya, Vladimir Ivanov and Rod Gill weighing in from Austria, Ukraine, Russia, and New Zealand respectively – with kind contributions from the fine folks in Redmond as well. As usual, anything that works is credited to them…and anything that doesn’t work is solely my responsibility.