John Riopel covers in this video how to create the dataset for a Task BurnUp and format the dataset to prepare for graphing.
- [Voiceover] Now that we've learned a lot of the functions that exist under Visual Reports, we can take that to the next level by creating our own visual report templates. I'm going to create a burnup report using my Visual Reports option, so I've gone to my report ribbon and selected Visual Reports. On here I'm going to select a new template, but before I do that I wanna make sure that I have the Weeks format selected, since we're interested in it by week burn. So we'll select New Template.
On this dialogue box we'll select Excel, and we'll stick with our task usage, since that's where all our task data lives. We'll add in some custom fields in this particular project file. So we'll go to our sprint number and we'll add that in, and we'll add in our story points and lastly we'll add in another custom field called Velocity. Now these are all custom number fields within this current project template that you'll find in your exercise file folder.
We're going to say OK, and OK again. Now this is going to build our Excel template to start from, and of course it's a PivotTable like we've been looking at before. Now I'm interested in looking at this as burn, so I'm going to select my tasks first, then I'll bring over a weekly calendar, since I'm interested in this by week. And I'm interested in cumulative work and actual work. Now, I'm not interested in the data area here, where it's currently listed as B2, I'm more interested in that over in the totals section.
So you can either move those values within the PivotTable itself, or down in the lower-right-hand corner, if you wanna move them from the columns to the rows or to the values. In my case I wanna move B2 over, under my years, so I'll do that. And I'm going to move my year next to my task name. So now I have a task, year, cumulative, and actual work. Now I'm mostly interested in this at the week level. So I wanna get rid of this subtotal, so I'm gonna select it, right-click on the subtotal task item, and deselect the subtotal option.
I'll expand out my year into quarters, and then once more into weeks. I'm going to get rid of the subtotals for the quarters, and lastly right-click and get rid of the subtotal for the year. Now that I've created my template of the data set, I'd like to add in the additional attributes around our custom task data that we're looking at. So I'm going to right-click on my Task column, and in the Show Properties in Report, I'm going to add in my sprint number, I'll right-click again, select Show Properties in Report, and add in my story points, and right-click one last time and Show Properties in Report and add in my velocity.
Now you can see that as I've added them in, they become attributes of the current tasks that I have. Now, they're not filterable at this point, so I may wanna edit this and bring up some additional capability and put them into the filters. Now, I'm mostly interested in the task-level data for this particular project for the sprints that I am running within this product development project. I'm going to take the Tasks and move it into the filter area. In the filter area now, I can select what tasks I want, so I'm gonna expand that out until I see my Design area, and specifically my software development where the sprints currently live.
And I'm going to select the Select Multiple checkbox down here. Now, since I'm mostly interested in the sprints, I'm going to deselect all of the above items so I'll click on that and click on it twice to get rid of the checkboxes there. And I can now select the first three sprints', one, two, and three, checkboxes. And I'll press OK. I've now got my Tasks into my grid the way I want; however, I really would like to have them back into the grid area and not just as a filter, so I'm going to take them back from the filter area and I'm gonna drag them in to the rows just above my weekly calendar again.
And it will retain the filter that I currently have. And of course I'll get that data from those particular sets of weeks. If I do need to filter on that again, I can select the filter for the drop-down. And now I could go right back into filtering without having to move it back and forth. So this is one way to filter the data, however, you know, it's an attribute of that particular task set and because I have the sprints at a summary level, this is one way to do it. However, if I want to filter it a different way, I might wanna turn that field into a dimension.
So before I do that, since this is a singular task field, I want to save this as a template, so I go to my File menu and select Save. I'll save it into my exercise folder and I'll name it my Work BurnUp, and I'll select Save. This question is, this is a workbook that contains external data, do you wanna clear the data and then refresh it, and I'm going to select Yes. So now that I have my work burnup template created, I can start here.
But I wanna take this another step farther along, because I really would like to have that sprint number as a dimension, and as you can see it's not in my Dimension list. So I'm going to close this file and I'm gonna return to project, and this time I'm going to close the Visual Reports dialogue box and I'm going to adjust the sprint column within my project file to create that dimension. So I'm going to right-click on the Sprint Number column that's already inserted here and select Custom Fields.
Now, since it's currently a straight number field that I can type any number I want into, I'd like to change that into a lookup list which will automatically change that field into a dimension. So I'm gonna do that, select Lookup, and on the bottom section where I have my data entry options, if it's not expanded, you can press the plus sign and expand it out. And I'm gonna say "Allow additional items to be entered into the fields," and what it's going to do is find the number in the current task list for the sprint column, and it's going to populate that lookup for me automatically, so I'll press Close and then OK.
So now I have a lookup of the four values that are in here, one, two, three and four, for the various sprints. Now, I'd like to revisit that particular report because now I really wanna create a much more dynamic filter instead of the one that I previously had, so I'll return to my Report ribbon and select Visual Reports and because I saved it into my exercise folder, based on this path it now shows up in my list, so I'll rerun that report and I'll expand that out so it's full screen.
So now you can see that I have my sprint number as a dimension where I can now start to filter on that instead of using the Task filter, which I did before, so I wanna add that to my filter list, so I'll bring that in and move it up into my filters. And now I can actually filter that data here and select the multiple filters that I want, so maybe I'm interested in one and two, not so much four and zero, and I'll select OK, and in this case I'm going to get exactly the same thing, but if I clear this filter on the task list, I retain the values because I have the filter up here using the dimension.
So that you can use different ways of building these custom fields into your visual reports by understanding what type of field it is, and how you might want to use that data as you build out the report, either as a filter or as a task attribute within the middle section of your report.
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.