Wednesday 7 July 2010

Solved! Integrating Bugzilla with MS project 2007

I have been battling for weeks trying to integrate Bugzilla items with Microsoft Project, and finally I’ve got something working.  Truth be said, I had it working a long time ago, but suddenly it stopped working – so it somehow got broken.  I couldn’t figure out what had changed and it was driving me crazy – even my colleagues around me were starting to feel the pain and could see my hair getting pulled out!

Let’s start at the beginning, and explain what I needed and how I got it done:
BugzillaWe use Bugzilla as a bug tracking tool and with each release we need to figure out which features and fixes will make it into the next release.  While Bugzilla is a great fault tracking tool, it doesn’t have the ability (AFAIK) to show when all targeted bugs will be fixed.  In other words, it shows what needs to be done, but doesn’t show when they will be fixed by the assigned developers.

 
image We use Microsoft Project to show load on resources (levelling) and for release planning ie: which features and bug fixes will be put into the next release.  So, it’s obvious that importing Bugzilla’s bugs into the project plan will allow us to see what can be done, by who, and by when.

Entering the constantly changing list of bugs into MS Project manually is possible, but extremely tedious, so it becomes an impossible task.  I am still looking for an automated way of doing this… but, we’ve found the next best solution:

Importing Bugzilla into MS Project:
Instead of doing the manual update thing… the next best solution is to import the list of bugs into the project plan as follows:
*) In Bugzilla: change the bugs that need to be fixed in the next release to have a common TargetMilestone.
*) In each bug, update the “Hours Left” field to the estimated number of hours that the fix will take to resolve.  Note: the “Hours Left” field is only visible if the user is a member of the Bugzilla “timetrackinggroup”.  Add peoples’ logins to the group using the “Edit groups” page: eg: @yourcompany.com$|@gmail.com$image*) create a view that lists all bugs that need to be imported into the plan ie: all bugs with a TargetMilestone set to “EndJulyRelease” for component “Widget” that have been modified since 2010-06-01 (the last time the bug import was done).
*) Add columns to the results screen (use the “Change columns” link) to add columns that you want to import into the project plan.
*) Export the resulting list by clicking on the “CSV” link (at the bottom of Bugzilla’s results page).  Save the .csv file to your computer – this is the file that will be imported (actually, merged) into the project plan.

*) In MS Project: open your .mpp file, click File->Open, select the .csv file that was exported from Bugzilla; the “Import Wizard” will open.  Click “Next”, then “New map” and define how the fields (in the .csv file) map to the MS project fields as follows:  On the following screens select the following:  “Merge the data into the active project”, then “Tasks”, tick “Import includes headers”, then (in the “Task mapping” screen): Task mappingClick on the pull down arrows and map each field in the text file to each of the MS project fields.  The key to this import (and subsequent imports that update already imported bugs) is the “MERGE KEY”.  Make the bug_id field the merge key by clicking on the “Set merge key” button.  This will ensure that the next imports of the updated .csv file will not insert duplicate tasks, but rather update the project tasks as identified by the Bugzilla number.
*) Ensure that the “remaining_time” field from the .csv file is mapped to MS project’s “Duration” field, and “short_desc” to “Name”.
image *) Save the map so that subsequent imports will be faster since the saved map can be used to import the data.  Click “Finish” and the data from the .csv file will be imported into MS project.  Any existing tasks with a Bugzilla number in the “Text1” field will be updated, while new tasks will be add to the end of the MS project file.
*) Add columns “Text 1” thru to “Text 14” to your Gant chart, and by showing the Bugzilla priority field next (eg: Text5) to the MS Project’s “Priority” field one can see which tasks should have a high MS Project priority that can be used for levelling.

OK, so the above description shows the mechanics for integrating Bugzilla with MS Project, but what got broken?  What changed that was driving me crazy and making me pull my hair out?  The merging the .csv file stopped working – it just imported the Bugzilla tasks over the other project tasks – ie: somehow it was ignoring the merge key! 
I managed to isolate the problem by creating a new, empty project, then pasting the original MS Project’s tasks into the new Gant chart, then the merge of the csv file worked!
This led me to the cause of the problem: I found that by deleting some of the columns in the Gant chart fixed the problem and the merge worked again…

So, if your import of .csv file stops working all of a sudden, then you can try the two solutions above (create a new .mpp file or delete some unused columns).

Update on 16Jan2011: I have now upgraded my MS Project to MS Project 2010 (version 14.0.4751.1000) and the merge from the Bugzilla CSV file imports fine and there are no problems – so no need for me to use a new, empty project :)  Hoooray!
image

Update 23 March 2011: If you prefer to import Bugzilla bugs into a spreadsheet directly without having to do the following:
1) go to the Bugzilla web page,
2) open (or create) the view of the bugs you are interested in,
3) export the file to a .csv file or copy and paste it into an Excel spreadsheet.
In other words, if you don’t want to do the above each and every time you want an update, then this can be automated by using some Visual Basic for Applications (VBA) script in Excel that will download the content of a view via its CSV file, and display its content in Excel (almost like an API).  Omar BELKHODJA explains what to do – thanks for writing the blog post and for sharing!: http://blog.sos-company.com/?p=3

3 comments:

  1. The mentioned link http://blog.sos-company.com/?p=3 is not accessible. Can you please provide a working link, or explain the same on how to automate it.
    BTW, thanks for sharing it.

    ReplyDelete
  2. Hi, the link " http://blog.sos-company.com/?p=3" is not working. Would be most thankful if you provide some info regarding this.

    ReplyDelete
    Replies
    1. Hi Sam, the VBcode inside the Excel file is pasted below - a button can be created and linked to a macro called: "SheetUpdate":

      ''''
      ' S.O.S. Company
      ' Simple and Original Solutions
      '
      ' Example of interaction between Bugzilla and Excel using VBA
      '
      ' Script written by : Omar BELKHODJA (omar.belkhodja@sos-company.com)
      ''''

      ''''
      ' Function HTTPGet()
      '
      ' Sends a Get qurey to HTTP server and returns the server's answer
      ''''
      Public Function HTTPGet(ByVal URL As String) As String
      Dim xmlhttp As Object
      Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

      xmlhttp.Open "GET", URL

      ' Indicate the header length
      xmlhttp.setRequestHeader "User-Agent", "mozilla/4.0 (compatible; MSIE 6.0)"
      xmlhttp.Send

      Dim counter As Double

      ' Wait 1 minute for the server's answer
      TimeOut = Date + Time + TimeSerial(0, 0, 60)
      While (Not xmlhttp.readyState = 4) And (Date + Time < TimeOut)
      DoEvents
      Wend

      If xmlhttp.readyState = 4 Then
      If xmlhttp.Status = 200 Then
      HTTPGet = xmlhttp.ResponseText
      ElseIf xmlhttp.Status <> 200 Then
      MsgBox "Server error: " & xmlhttp.Status
      End If
      ElseIf xmlhttp.readyState <> 4 Then
      MsgBox "Ready State error: " & xmlhttp.readyState
      End If
      End Function

      ''''
      ' Function SheetUpdate()
      '
      ' Updates the sheet cells with data from the Bugzilla web server
      ''''
      Sub SheetUpdate()
      Dim sQueryString As String
      ' This is the URL to the CSV file
      sQueryString = "http://bugzilla.YourDomain.com/buglist.cgi?classification=YourDomain&query_format=advanced&ctype=csv&human=1"
      ' Send a HTTP Query to the bugzilla server
      sAnswer = HTTPGet(sQueryString)
      ' The answer is a CSV text, split it into lines, and then into cells
      aLines = Split(sAnswer, vbLf)
      ' Loop on the lines
      For i = LBound(aLines) To UBound(aLines)
      aElements = Split(aLines(i), ",")
      ' Loop on the elements of each line
      For j = LBound(aElements) To UBound(aElements)
      ActiveSheet.Range("B10").Offset((i - LBound(aLines)), (j - LBound(aElements))).Formula = aElements(j)
      Next j
      Next i
      End Sub

      Delete

Live Traffic Feed

 

Be notified of
page updates
it's private
powered by
ChangeDetection

Copyright © 2008 HandyTechTipper. All articles are released under the Creative Commons Attribution 2.5 South Africa license, unless where otherwise stated.