Primavera P6: Time Distributed Reporting and Microsoft Excel

by TheP6Pro

It’s common practice to use Primavera P6 Professional’s Time Distributed reports feature to create time-phased tables of data that are then exported to Microsoft Excel. However, if you’ve ever done this you may have noticed that in many cases the Subtotals row can be one column too far to the left.

This misalignment invariably requires you to go through the entire spreadsheet deleing cells to bring the data back into alignment.

However there’s a better way to permanently solve this problem and good old Ten Six Consulting will show you how right now.

In the Reports view, right-click on your Time Distributed report and choose the Modify button.

When you see the following prompt, click the Yes button.

Clicking Yes will open the Report editor as seen below. This is the underlying canvas that gets created by the wizard when you initially build a report. While it is possible to build a report from scratch using this feature, we do not recommend it as it can be a little time consuming: rather you should always create your reports first using the Report Wizard.

The Report editor allows you to add or remove fields used in the population of the final report output in P6. In this case we’re going to add a field to the sub-total row in order to solve our issue with the misaligned spreadsheet data. The Subtotal field is spanning the space of the Activity ID and Activity Description fields in the data row above. Therefore the subtotals get written on a row that has one less column.

Adding a new field to the Report

First we need to make some room for a new field, so we click on the right-hand end of the Subtotals field and drag it right to about half its original size.

Now we right-click on the row behind the Subtotal field and from the menu choose Add Text Cell.

This places a text cell on the same row, at the far right end and opens the cell’s Properties dialog.

In the Cell type field, select Custom Text and then close the properties dialog using the small x button at the top right.

The next step is to drag the new cell over to the left of the row. This will add a column to the CSV file that is opened by Excel in the final stage of this process.

Without further modification this cell placement won’t look very pretty in the report preview; however as it’s primarily for creating spreadsheets that doesn’t matter.

Finally click the OK button to close the report editor and then run the report, saving it to an ASCII text file using the example settings below.

In the final spreadsheet, you can see that the addition of a new text field has caused the data to come out correctly in line with the headers.

Thanks Ten Six.