Demystifying Custom Reports (Part 2)

As we found in Part 1 Demystifying Custom Reports, of learning to design custom reports in MS Project is a bit like falling down a rabbit hole, akin to what Alice experienced in the Lewis Carroll-written adventures, but as we are about to find out, learning to produce custom charts in MS Project is more like falling into a black hole—unless you are careful during your first time out…

While users of Excel or Visio (or any other MS Office charting tool) may find comfort in their accumulated knowledge or experience; like Alice, you may be surprised to find that the most common of things can be deceptively complex. This is certainly the case when you click on Report ribbon > New Report > Chart.

Of course, giving the new report a name (as prompted) is not big deal, but the initial chart generated may be a bit shocking, with a few fields selected that makes the chart nonsensical. Note that any chart created here is dependent on the state of the project that you have open.

See Figure 1 for what you are most likely to find before you can begin building your custom chart. If you don’t see the Field List sidebar on the right-hand side of your MS Project window, click anywhere within the charting area to view it. Alternatively, find the click labeled Chart Data Show/Hide on the Report ribbon > Chart Tools.

Figure 1. Default Setup for a new chart (Report ribbon > New Report > Chart)

As mentioned in Part 1, depending on where you click within a table or a chart, MS Project brings up the appropriate tool set. For example, double-clicking on the gridlines of a chart brings up the Format Major Gridlines sidebar. What’s important to focus on now is the Field List sidebar, as that’s where you can select the project data to be included in your new chart.

Let’s build an S-curve chart showing the curves for both Cumulative Actual Work and Baseline Cumulative Work. S-curves are a great way to visualize project progress over time, and to see how a particular aspect of project progress is performing like actual work performed vs. the work planned. In this example, we’ll start by selecting the Time Category and then adding the Cumulative Actual Work field by ticking off the correct box in the Field List sidebar. By default, this creates what Microsoft calls a “Clustered Column” chart in the system’s default color (that can be changed later). If you then also select the Baseline Cumulative Work field you will get two clustered column charts laid over each other. Select the Baseline Cumulative Work field under Work > Baseline. See Figure 2 for the chart generated when these two fields are selected.

Figure 2.Two clustered column charts with two project data fields used

I hope you managed that without too much hair-pulling and hand-wringing. It’s important to note that you can’t chart data that does not exist. For example, in the chart we just generated, the open project had a baseline set and some work actually planned. If you don’t have such a project file to play with, a sample file is available for download (see SERVE-Nepal Project Plan.mpp).

To create the actual S-curves, we now have to change the chart type from clustered column to line. To do that, we’ll click on any field name in the X-axis of the chart, and then go back up to the ribbon and select Change Chart Type. You’ll see that you are then presented with the Change Chart Type dialog box where you can change the two chart types from Clustered Column to Line (see Figure 3). Now you have two S-curves (one for actual work and one for the baseline work)! To make the chart even more relevant, go back and add the Actual Work field from the Field List sidebar, and then come back to the Change Chart Type dialog box and mark that field’s display as a “Secondary Axis.” Ensure that the chart type for the newly added field is set to Clustered Column (if it wasn’t already).

Figure 3. Using the Change Chart Type dialog box

Figure 4 shows the completed S-Curve chart for work done over the first year of our sample project. Now that you have a chart created, you can customize the presentation and format of the chart to your heart’s desire. Use the Chart Tools > Design or Chart Tools > Format ribbon to change the overall look and feel of the chart, and even define the look and feel for each and every chart element, such as the font size and color of any of the labels. You can also add additional labels, legends, or trend lines (as well as many other chart elements) to what you have created. Go ahead and explore the tools as presented. Don’t worry! There is nothing you can break here.

Figure 4. The finished chart, ready to distribute…Your finished report can now be distributed by printing from within MS Project. Don’t forget to turn on Report Tools > Design > Page Breaks. You can also select the desired report elements and copy/paste those elements into any other MS Office product, such as MS Word or PowerPoint. Once there, if you see something that still needed editing, you can continue formatting of the report elements, but you cannot change the actual data without returning to MS Project.

Return from the Black Rabbit Hole

Now that we have ventured down the rabbit hole of custom reporting and returned back in one piece, let’s step back for a moment and recap what we have accomplished so far. In Part 1 of this tutorial, we picked apart the major components of a default report (title, text boxes, bounding boxes, page boundaries, tables stuffed with data fields, and charts created using specific data fields) in order to understand how to make new ones.

In Part 2, we took the most complicated aspect of custom reporting, Charts, and have demystified those, as well, by building our own double S-Curve. Any other custom chart is created likewise. In summary, the steps are:

1. Pick the data you want to chart (and the category of the chart)
2. Identify what is to go on the chart axis (either a single chart, or a combination of charts overlaid onto eachother)
3. Format the chart to suit using colors, fonts, additional elements,

Not mentioned in Part 1 of this tutorial was how to save custom reports for future use across all of your projects,  but that’s trivial if you are familiar with MS Project’s Organizer (See Figure 5). In case you’re not, just type “Organizer” in the “Tell me what you want to do…” box on the ribbon. Viola! You can now copy a custom report from your current project into the Global.MPT (the place that stores all of your common bits and nibbles for later use). For every project that you create going forward, the custom reports containing all of your wonderful work will be available to you under Report ribbon > Custom > report names.

Figure 5. Using the Organizer to put your custom reports into any new (or existing) project

I’ll leave you with these words from Lewis Carroll, “The reason they’re called lessons is because they lesson from day to day.” Don’t forget to practice your new custom reporting skills (a.k.a. your rabbit hole adventure), and if you get stuck, ask for help from a “mad hatter” by using the comment box below.

Related Content

Webinars (watch for free now!):
Report Basics: Build an Agile Kanban Board in Microsoft Project
It’s All About… Reports!

Articles:
Create a Monthly Cash Flow Report in Microsoft Project 2013
Creating Milestone Reports in Microsoft Project
Creating a Custom Report in Project 2013: This Week’s Tasks