We're Back (and with instructions on creating a Burndown Report)!
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...
How to Create a Burndown Report:
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.
Comments
Anonymous
November 14, 2007
Ok, this is more about views than reports. If I set a lookup table in a tree structure like: a-cat1 -cat2 -cat3 b-catx -caty -catz How do I make a view in project center that only shows items from "a" and not from "b"? The filter will only allow "equals" and "does not equal". Kind of defeats the purpose of hierarchical lookup tables. Also, why do the views in project center not "roll up" to each depth of the hierarchy?Anonymous
January 21, 2008
This looks great but I can't follow your instructions on Excel 2003, can you post instructions for 2003?Anonymous
April 28, 2008
Hi, I just don´t understand how to save the template. When I save it and try reopening it does not work. It just loses the conection to project. Can you please help me? Regards, MacielAnonymous
May 05, 2008
For some reaons, running Visual Reports on my computer consistently gives me an unknown error. I have tried toggling the Tools > Options security settings, reinstalling Office, reinstalling Project, etc....to no avail. Posted on the Newsgroups, and nobody could think of anything. Figured I would ask you if you have any suggestions. Am running MPP 2007 on a Vista machine w/ Excel and Visio 2007 installed. To my knowledge, Visual Reports have never worked on this machine.Anonymous
May 06, 2008
Eduardo - are you re-opening the template through Visual Reports or just through Excel? Andrew - On the Visual Reports dialog, can you try going to Save Data and letting me know if you can save a database and save a cube? Also, when you installed Office did you do a custom install or go with the default settings?Anonymous
May 13, 2008
Hi... I´ve tried reopening through Visual Reports. Then it happens the data source is not available(I can see when I open the xlt) and excel would open with a dialog (expecting me to press "ok") so Project doesn´t know how to deal with it and excel crashes. I tried to create a more elaborated template because I also want a "Size tracking" chart in the same report of the Burndown... But I always end up with the same problem.... thx.Anonymous
May 13, 2008
By the way... Do you have any paper or experience you could share about tracking "Size" using MS Project? (despite of all other variables such as effort, duration, cost, etc, of course) I´ve been doing this by inserting a "number 1" column (which I name as "Size"), and then, for each feature in the schedule I set a milestone linked to the summary task of that feature. For this milestone I set a value for the Size column. In the report I want to be able to compute the size for all summary tasks that are 100% completed. :) This way I can control how much of the software has already been delivered vs the effort vs duration. Regards,Anonymous
June 10, 2008
Thanks for the help on my Visual Reports issue. Turned out to be a bad pc image. Reinstalled everything, and it all worked. Next question. Am trying to do a Pivot Chart comparing Cumulative Baseline Work and Cumulative Work. Do the Visual Report, export to Excel. I know I can do the sum feature like you have demonstrated above, but I would like to do the summation of the Cumulative Baseline Work w/in the Pivot Table. In theory, I should be able to select the field properties for Baseline Work, and set it to show a running total. Yet every time I do that, I get an #N/A error in the Baseline Work field. Why does the Running Total In feature not work for this PivotChart, and is there a way to get it to work? Thanks in advance....Anonymous
September 23, 2008
Good conversation. Has anybody had a chance to alook into Andrew's question? I am getting the same #N/A error and my chart doesn't do the trick I need. I am badly in need to have a solution. Thanks in advance.Anonymous
September 23, 2008
Are you trying to do the running total calculation in the pivotTable or to the outside of it? As far as I know, you can't update the pivotTable so you'd have to do this outside of it. Normally when I get #n/a I have a typo in my formulas. For an easy way to verify this, go to the Formuals tab and select Show Formulas.Anonymous
February 17, 2009
Hello all, I also receive the #N/A error. I want to get a cumulated value of the actual cost and the baseline cost. The Pivot table features "the running total in" setting for values, which can be set in the field properties. Theoretically this should cumulate the values, but it doesn't and I receive the #N/A error mentioned by Andrew and Daniel. At least is there a possibility to add calculated measures to the local cube generated by the project client? Thanks in advance, TrutzAnonymous
May 07, 2009
Hi All, I did this sample and everything works pretty good, but now I have a question regarding how to show the progress daily instead weekly. Make a great day!!! Fernando.Anonymous
May 08, 2009
To see everything at the day level instead of week level, you need to set this in the main visual reports dialog. When you first go to Visual Reports - in the Create Report dialog, set "Select level of usage data to include in the report" to Days instead of Weeks. For people having trouble getting this work, in Excel, go to the Formulas tab and try some of the commands in Formula Auditing. I'm betting your formulas are pointing to the wrong fields. Thanks.Anonymous
May 12, 2009
For some reason the visual report is not showing baseline work values. There is a baseline saved for the project and when I go back to project and tell it do display the baseline work values there are numbers there. Any idea why it shows up as 0?Anonymous
June 22, 2009
Hi Heather, Is this report possible using SSRS? Pivot tables are not needed if that makes it easier. Just looking to run a canned report on a schedule with this data along with other data I’ve been able to query using SSRS. Thanks for any help, MarcoAnonymous
June 26, 2009
Joe... 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.Anonymous
June 29, 2009
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.Anonymous
February 17, 2010
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 ;-)Anonymous
March 10, 2010
How can I make a button in Excel Sheet and Give Some code.Anonymous
May 11, 2010
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!Anonymous
May 14, 2010
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. Thanks.Anonymous
June 01, 2010
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!Anonymous
August 26, 2010
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.Anonymous
August 27, 2010
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.Anonymous
April 28, 2011
I will give this a try. This looks exactly like what I am trying to do.......stay tuned.Anonymous
April 28, 2011
Heather, 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?Anonymous
May 02, 2011
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?Anonymous
August 18, 2011
Heather, 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.Anonymous
February 17, 2012
Hi Heather, Is there a way to show the Burn Rate by day instead of week? Thank you.Anonymous
February 23, 2012
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.