Hi, I'm Heather O'Cull, another program manager on the Project team. In case you're wondering what happened to Treb, he is still here but has decided to pass the blog on to give more variety to it. Big thanks to Treb for all the great posts and hopefully he'll still be up for doing some guest posts.
I work more on the client and client reporting so I'll be giving the blog more of a spin in that direction. If there is anything you would like to see posts about, please let me know.
At the Project Conference I presented on client reporting and promised to post instructions on creating a burndown report through Visual Reports. Something like:
And here are the steps...
1. Have the project you want to report on open.
1. Go to Reports - Visual Reports
2. Select New Template, Excel, Assignment Usage, and click OK.
Your report is now being created in Excel. Switch over to Excel.
3. Add Time Weekly Calendar to the Row Labels section.
4. Check Cumulative Work, Actual Work and Baseline Work.
5. Move Values to the Column Labels box. Your fields should be setup like this:
6. Expand the time dimension out to the weekly level (you can really choose to any time level you'd like).
7. Make sure subtotals aren't showing (to remove in Excel 2007 go to the Design tab, Subtotals dropdown).
8. In the cell to the right of Baseline Work, type Remaining Actual Work, to the right of that type Remaining Planned Work, then Cumulative Actual Work, and Cumulative Baseline Work so you have the picture below. You now need to calculate all of these values.
10. For Cumulative Baseline Work, in J3 type =Sum($F$3:F3) and fill down the column for the number of weeks in your Project. You nave now calculated Cumulative Baseline Work for your project.
11. For Cumulative Actual Work, in I3 type =Sum($E$3:E3) and fill down the column like you did in the last step.
12. For Remaining Planned Work, in H3 type =(x-J3) where x is the total for the Cumulative Baseline Work column. Fill down the column.
13. For Remaining Actual Work, in G3 type =(y-I3) where y is the total from the Cumulative Actual Work column. Fill down the column. You've now calculate all the data that you need. If I switch to show formulas, you should have something that looks like this:
14. You're almost there. You now just need to graph your data. To do this, insert a column to the left of remaining actual work. This is the week column. Now paste the week numbers there so you'll have them in your graph.
15. Now just select the week column you just added, remaining actual work, remaining planned work, and choose to graph them as a line graph. You should have something like the picture at the top of the entry.
To make this look even better you can draw a status line to help demonstrate where you are in the plan. I also prefer to delete the values in Remaining Actual Work that are in the future to make the graph more compelling.
Mine showed up as 0 too...until I assigned resources to each task. Then, my baseline values had hours in both Project and my export to Excel.
Selecting any of the standard reports in Visual Reports results in a error "Excel is busy. This may be bacause excel is currently waiting for user input (for example, a dialog may bee open. Swith to excel to correth the problem, then try again." Tried with Excel closed; Excel open; saved blank Excel file open; no difference. Running Project 2007 and Excel 2007 SP2.
I've been trying to generate Visual Reports with the new Project 2010 Beta and it doesn't work. Everytime I have an error saying "Excel is busy" but when I look into the Task Manager there is no Excel activity at all. I tried to install all the latest updates but I still have the problem.
I'm running Project 2010 Beta (14.0.4514.1004) MSO (14.0.4536.1000) on Windows 7 Professional (32-bits) with Office 2007
Please help me ;-)
How can I make a button in Excel Sheet and Give Some
I've gone through the above steps and created the excel sheet. However all values show 0. All.
What aren't I doing right? Please help!
If all the values are showing up as 0, first try inserting the work column into your project plan - does it have all 0's in it? If so, that explains why it is showing up that way in Excel.
To get values in the Work column you either need to assign resources to the tasks or manually set the work value for each task.
I get the same problem as Mr. Pat - whenever I try to print out a Visual report it says "excel is busy" but excel isn't even on!
The formula in step 13 is wrong. It should use x again, as in previous step, instead of Y. Both lines should start from the same 100% point.
That's an interesting comment about whether remaining actual work should be calculated off the total cumulative work (which is actual plus remaining work) or total baseline work (what you originally planned to do).
If you use the former, then you know when you hit zero that you are done but you are starting from the amount of work that you currently planned to do and have done as opposed to what your originally planned to do. If you use the latter, then you are burnind down from the original work value but when you hit zero your project probably won't be done since odds are work has been added over time.
I will give this a try. This looks exactly like what I am trying to do.......stay tuned.
This worked great as I stated in an earlier reply. When I tried to use it on a Master Project I wasn't able to include a field for "project" so I could filter the pivot table to a specific project. Also it errored out when useing the available data in the pivot report. Any ideas?
Unfortunately, the project field isn't available for Visual Reports so your best bet is to open the project separately when you want to report on it.
Also, what do you mean it errored out when using the available data?
Thanks for the great post. I have implemented the chart, and things were working great, but I ran into an issue. I am importing my tasks from Team Foundation Server. Developers enter their actual hours in TFS, and this is pulled into MS Project.
Developer 1 entered the following actual hour numbers on a particular task:
Day 1 - 3
Day 2 - 3
Day 3 - 5
But when this is pulled into MS Project and reported on the burndown chart, it reads:
Day 1 - 4
Day 2 - 4
Day 3 - 3
It looks like MS Project is trying to evenly distribute my actuals, rather than apply them on the date they were entered into TFS. Have you ever seen anything like this?
Thanks in advance for your time.
Is there a way to show the Burn Rate by day instead of week?
Mark - yes, you can do this at the day level. When you are initially in the Visual Reports dialog, set "Select level of usage data to include" to days and then in step 6 expand to the day level.
If you don't work on weekends you will get some flat lines in your chart.