Originally posted in confluence.cornell.edu

Start in JIRA

Go to the NIHVIVO project page
Click on Versions
Select the current version being prepared
On the right hand side, choose Outstanding from the Filters dropdown
Sort by Component by clicking on that column header
Move to the end of the report and assign any issues without components to a component
Look for any issues miss-assigned to components in the next version and reassign those issues themselves to the next version
Verify that the field "Remaining Estimate" is included rather than "Σ Remaining Estimate."
When you are satisfied with what shows up on the list, select Excel (current fields) from the Views drop-down just to the left of the Filters drop-down
Save the Excel file wherever you can easily find it again

Move to Excel

These instructions were written using Microsoft Excel for Mac 2011 (see italics comments for Windows Excel 2010 differences)

Find and double click on the file just saved from Jira to start Excel
If you only want to include "Blocker" issues, remove the others from the spreadsheet now
Label the column immediately to the right of the Remaining Estimate column Days, in the same row as the other column headers
Enter the formula "=cell to left/21600" in first row of data in that column
Select the entire column from that cell down to the last row of data
Select Edit -> Fill Down to copy the formula for the entire column
Select the full range of data from left to right, including the row of column headers down through the last row of data
Go to the Data -> Pivot Table command (Windows: Insert -> PivotTable)
Accept the indicated selected data range and leave the default radio button selected to place the output in a new worksheet; click OK
In the black (Windows: white) Pivot Table widget, make three changes, leaving the Filter area blank:
- Drag "Component" to the Row Labels area
- Drag "Assignee" to the Column Labels area
- Drag the newly created "Days" to the Values area
Close the black (Windows: white) Pivot Table widget. You should see the basic layout of the report, with components listed in the first column of each row and the names of developers in the leading row of each column of data
Select all the numeric values in the whole table and use Format -> Cells (Windows: Home -> Format -> Format Cells) to change their numeric display to a single digit after the decimal point
Select all the columns headed by an assignee and make the columns narrower
Center all the values in the column (text and numbers)
Select all the cells with assignee developer names and Format -> Cells -> Alignment -> wrap text (Windows: Home -> Format -> Format Cells -> Alignment -> wrap text) – then adjust any individual columns so each name is readable but the full set of columns fits in your window
Select all the individual cell data values (not the totals at the bottom or right)
Select Format -> Conditional Formatting (Windows: Home -> Conditional Formatting) to see the Manage Rules dialog box
Add a new rule with the + sign (Windows: "New Rule" button)
Leave the type of rule a 2-Color Scale
Change the lowest value to white
Change the highest value to bright red in the Standard Colors
Click OK to return to the Manage Rules dialog box
Click OK to see the formatting
If desired, set up additional formatting rules for the totals across the bottom and the totals in the rightmost column, using your choice of color - don't combine the values in one rule and leave out the bottom rightmost total or the visual scales will be skewed
Hide any assignees not relevant to the current report
Adjust any other formatting
Make the appropriate page setup for printing
Save the spreadsheet with an appropriate name, including the date and version