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