Capturing Custom Timescaled Data in Project Server (Part IV)

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….

image

Changing the cost fields to a sum, and the status date to a maximum value, we end up with something like this….

image

Convert the cost fields to a running total, and we get this….

image

From here, it should be a relatively easy exercise to add a Pivot Chart (shown here with the Status Date removed)….

image

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.

image

The end result is something like this….

image

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….

Credits

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.

About these ads

About Andrew Lavinsky

I am a consultant with the UMT Consulting Group, one of the premiere project portfolio management consulting companies in the world, galaxy, or universe for all I know.
This entry was posted in BI, VBA and tagged . Bookmark the permalink.

One Response to Capturing Custom Timescaled Data in Project Server (Part IV)

  1. Pingback: Project Server Business Intelligence Resources (Part 3) | Project Epistemology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s