In this topic we cover editing an existing template and add an additional standard field to the data for top level reporting in Excel. This field is then used for reviewing costs by critical and non-critical tasks within the project.
- [Voiceover] Now that we've used some existing reports to come out of the box, let's take a look at editing some of those templates to further enhance our visual reporting options. I've gone to the Report ribbon and selected Visual Reports. I'm going to select a couple of those reports that we've seen before in a previous video so I'm going to use the Baseline Report and in this case, I'm going to select Edit Template. Since I want to modify this template that comes out of the box the existing fields on the right-hand side are already there for me to select from.
However I want another field called Tasks that are Critical. So I'm going to scroll down on the left-hand side since I am interested in adding that dimension to this particular report. I'm trying to build a report that gives me the ability to see critical task summaries, dollar wise and non-criticals. So I'm going to click on that dimension and add it to my right-hand side. I'm now going to select Edit Template which is going to go off and collect the data it needs for that particular report. Visio launches and I now can see the additional items in my report.
So I'll zoom in a little bit so we can get a better look at this. And maybe once more. There we go. And now I have my baseline report. And I've added the dimension over to the left-hand side which shows up in the category list. I want to collapse this since I'm interested in separating out the total cost of the project by critical or non-critical. So I'll right click on that quarter option and select collapse. I want to add in the critical piece now to this particular report.
So now that I have that selected, I can select Is Critical and will expand out to the values of yes and no. So it takes the total quarter amount, since that's what my report is in, and breaks it out into non-critical tasks and critical tasks. Now, right now in this particular report they show up with a indicator flag saying either yes or no. Or expand on that and actually have it be color related just like it would be in a project file. So I'm going to right-click on the quarter again, and select Data, Edit Data Graphic.
In here is a list of all of the Data Graphics that currently show in the report. In the fields here I can add in additional ones. So since I've created an Is Critical flag I select a new item and I'd like to add that flag to my report so I'm going to select Choose a Field and scroll down and find more fields and now in the Shape Data area, so I select that, I can now add in my critical field that I want to add in for this particular report. So I'll press Okay now that I've selected it.
And I want to show it as a color so in the Display As I'll select Color by Value and as you can see it's dropped in the two values that are associated in the report that we've already seen, no and yes. I want to change that fill color to a light blue for the no values and a red color for the yes values. I can then press Okay and Okay again. And now I have my color coded yes, for critical and no, for non-critical and as you can see it's broken those values up into their current total values.
This is an editing of a Visio type template. Now we're return back to Microsoft Project here on my task ribbon and in the task ribbon I've gone back to my visual reports dialog box and I'm going to select the Excel version of the same type of report that's called a baseline cost report. So I'll edit this template this time and I'll add in my Task is Critical function here, for comparison points and I'll add that into this pivot table list of fields.
And again select Edit Template. This time Excel launches, my new data set is in here and I'm going to go over to the Assignment Usage value. As you can see, it's currently shows the project at Task Level one which is a little bit more detailed than I had in Visio diagram so I'm going to collapse that using the minus sign next to the task list. Now also in here it's got my pivot table over on the far right where I can add in my additional fields or dimensions depending on which type of items I want.
And since I'm mostly interested in this Is Critical dimension I'm going to select that checkbox. Now Project has added it into the data set in the middle which is not currently where I really want it. I really want a filter on that data set but I do see both of the values, no was 24240 and of course, yes is 22600. But I want to be able to filter on it so I'm going to move that Is Critical field up into the filter section and now I can filter that report using the drop down next to Is Critical for the values of yes or no and I'll select the multiple items which gives me that capability to see both, yes or no items.
So here I now have that option to either roll this up or just select a single value. So I've deselected the no value so that I get critical tasks and of course now it filters for critical activities. Now in either case you may want to save these as a template so to do that you would go to the File Menu and select Save. It's going to put it into the current folder which is your current folder options and I'll just call this my Cost Baseline modified.
And again I can select the type of template that I want to use. So I can use Excel 2013 and I'll press Save. And I now have my Excel template saved.
NOTE: This course was designed in collaboration with Bonnie Biafore. The techniques shown work equally well with Microsoft Project 2010, 2013, and 2016.
- Explain how to edit a report template.
- Recall how to insert a graph into a visual Excel template.
- Recognize the steps to take in order to add additional functionality into a Visio WBS diagram.
- Determine the steps to expand on a data set in an Excel report.
- Identify three PivotChart tools.