Question

How can I use FogBugz data to make reports in Excel?

Answer 1

There's a blog post about using the API and Excel 2007's data import functionality here:

http://www.fogcreek.com/FogBugz/blog/post/An-API-Tutorial-Plus-Excel-2007-Export-Plus-Zombies!.aspx

But FogBugz 7 has an Excel export functionality straight out of the grid.f

Answer 2

Is there a version for people that only speak English ?

I lost the will to live at the point when the instructions refer to "API" What is API ? where can I get one ?

Us administrators are just that, administrators, not programmers, so we do not have the rights to download Google Chrome or FireFox, nor I suspect API. The instructions need to be on a key press by key press basis.

All I want to do is dump a list of Fogbugz cases to Excel. Cut and Paste would do nicely

I finally managed by using Internet Explorer as follows: Get List of Cases in Fogbugz Click 'Edit' (top line) Click Select all Open Excel Paste (Ctrl V)

The top 100 lines of the spreadsheet are complete rubbish, but delete it and you have a usable spreadsheet.

So my method is 3 clicks, or you can spend hours with API and Pivot tables (Programmers only)

Answer 3

Accessing the FogBugz database using Excel should be as easy as getting to it from Access.  If the FogBugz database is not already visible as an ODBC connection, Arthur Purves suggests the following steps:

1. The database administrator has to give you access to SQL Server and to the fogbugz database on the server.
2. The database administrator has to give you the hostname that SQL Server runs on, and the name of the FogBugz database.
3. Test that this information works by opening a console and entering (this assumes you are using Windows authentication):
   
         sqlcmd -S <server's hostname> -d <name of fogbugz database>
         1>SELECT name FROM sysobjects WHERE type="U" ORDER BY name
         2>go
   This returns the names of the 43 FogBugz tables, e.g., Bug, Person, etc.
   
4. Now you can set up the ODBC source for Excel.   Open Excel->Data->Import External Data->Import Data->+New SQL Server Connection.odc. This opens the "Data Connection Wizard."  
5. For "Server name", enter the hostname that SQL Server runs on.  If you're not using Windows authentication here, enter the username and password that the database administrator gave you. Click "Next."
6. Click the down arrow to select the FogBugz database.  Uncheck "Connect to a specific table." Click Next and Finish.  Now you have a menu for the tables you need.
7. When you NEXT open Excel, choose Data->Import External Data->New Database Query:  This produces "Choose Data Source."  Select the name you gave the FogBugz ODBC source.
8. Now you have the "Query Wizard - Choose Columns"  IMPORTANT:  Click the "options" button and unclick everything except "Tables".  Or you can click the down arrow, scroll up, and select "dbo".

Now you can set up a query within the Wizard, or click Cancel and set up the query using MS Access.

Pivot Tables

Pivot tables are super powerful (if you know what you're doing).  They are hard as heck to fully understand (for me anyway), but hopefully this example will demonstrate what they can do.

Excel's documentation says:

"A PivotTable report is an interactive table that you can use to quickly summarize large amounts of data. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest.

Use a PivotTable report when you want to compare related totals, especially when you have a long list of figures to summarize and you want to compare several facts about each figure. Use PivotTable reports when you want Microsoft Excel to do the sorting, subtotaling, and totaling for you."

Recently I needed to figure out how long it was going to take us to fix all of our bugs for the next release (by priority).  Summing up hrsCurrEst for each Project, sorted by Priority was the perfect example for a sample Pivot Table.

I. Inserting the Pivot Table

Choose "Pivot Table" from the "Data" menu in Excel.  When prompted for "Where is the data you want to analyze?", check the button for "External Data Source".  Leave the "Kind of Report" as PivotTable.  Click Next.

Click the "Get Data" button and choose "fogbugz" from the Databases tab.

The "Query Wizard" should now pop up:

Hit "Cancel" on the Query Wizard and it will ask you if you want to "Continue editing this query in Microsoft Query?" Choose YES!

The "Add Tables" dialog will come up and you should add the following tables to the query: Bug, Project, Priority, Person, FixFor, and Area.

Most of the relationships will be defined for you (the lines connecting the tables show the relationships).  You will need to add a relationship between ixPersonAssignedTo in the Bug table and ixPerson in the Person table.  Drag ixPersonAssignedTo from the Bug table onto ixPerson in the Person table.

Drag
• hrsCurrEst from Bug
• sFullName from Person
• sProject from Project
• sPriority from Priority
• sFixFor from FixFor
• and sArea from Area

onto the blank white box below the tables.

Delete the line between the Area and Person tables, and the line between the Project and FixFor tables. (Double click on the line, then click Remove)

Choose "File->Return Data to Microsoft Excel".  Hit "Next" and "Finish".

II. Experimenting with the Pivot Table

Now you should see a dialog pop up with all your fields on it (hrsCurrEst, sFullName, sProject, sPriority, sFixFor, and sArea).

First let's get a run down of estimated hours for each priority.

1. Drop sProject onto the box labeled "Drop Page Fields Here".
2. Drop sFullName onto the box labeled "Drop Row Fields Here".
3. Drop sPriority onto the box labeled "Drop Column Fields Here".
4. Drop hrsCurrEst onto the box labeled "Drop Data Items Here".

You should see a estimate broken down by each person in the database for each priority.  Select a specific project from the drop down at the top of the pivot table next to sProject.

You can drop multiple columns onto the rows or the columns and Excel will break it down even further.  Just remember that we're looking at the current hours estimated to fix the bugs broken down in whatever way you like.  Play around with what you drop on the row and column areas and leave the hrsCurrEst field on the data area and you will get lots of interesting data.