Create Reports Using Microsoft Access
If you have Access installed on your computer you have all the tools necessary to create sophisticated reports from FogBugz data. You must have some familiarity with SQL queries and how to use Access for this tutorial.
Step 1: Opening the FogBugz database in Access
If you are not using SQL server, then simply launch Access and open the Bugz.mdb inside the Accessories folder of your installation (usually C:\Program Files\FogBugz\Accessories\Bugz.mdb). Skip to Step 2.
If you are using SQL server, you will need to create a new Access database and link it to the SQL server tables.
Choose File->New from the menu and create a blank database. After creating the database, choose “Get External Data… Link Tables…” from the File menu.
In the Link window, choose ODBC Databases from the "Files of type:" dropdown.
Select the Machine Data Source tab and choose “fogbugz”.
Select all of the tables.
If Access prompts you to select a unique identifier for any of the tables, just select the first item in the list.
If you accomplished this task successfully you should have an Access Database which looks like so:
Step 2: Creating a sample report
For our sample report, we will try to answer the question:
“Which bugz were resolved between 7/1/01 and 8/1/01 in the SampleProject project?”
On the left hand side under “Objects” choose the button “Reports”. Select “Create Report using wizard.”
The Report Wizard asks which fields you would like included on the report. Select the table “Bug” (if you are using SQL server, the tables names may be preceded by “dbo_”). Add ixBug and sTitle. Click Next. Click Next again when asked if you want to create groupings. Click Next when asked if you want to set a sort order. Click Next to accept the default layout for the report. Click Next to accept the default style for the report. Name your report “SampleProject Resolved Report”. Click Finish.
You should now see a sample Report of all bugz in your database. We will now go and edit the query that this report uses and add the date and project constraints from our question.
Select “Design View” from the dropdown in the upper left hand part of the Access window.
Choose View->Properties from the menu. (Note: Make sure the entire report is selected by clicking in the little box in the upper left corner of the report window. If it is not, you will only get the properties for the selected item and not for the entire report.)
Notice the Record Source field under the All tab. This is where the Report is getting all of its data from. Since we haven’t set any constraints yet, the Record Source is simply the entire Bug table. Click the ellipse next to the Record Source field.
It may ask you if you want to create a Query using the Query Builder. Select “Yes”.
The first step is to make sure our query has the elements it needs just to draw the report, namely ixBug and sTitle. So drag and drop the ixBug field and the sTitle field from the Bug table onto the Field: field.
Now lets add the project constraint so that our report only shows us bugz from the SampleProject project.
Right click on the Query Builder window and select “Show Table…” and select the Project table. Access should draw a relationship line between the ixProject field in the Bug table and the ixProject field in the Project table. If it doesn’t, you can simply drag the ixProject field from the Project table onto the ixProject field from the Bug table. You need to do this so Access knows how to join the tables together when it performs its query.
Drag and drop sProject from the Project table onto the Fields. In the “Criteria:” field, type:
= “Sample Project”
(Note: If you want the results of this sample to be at all meaningful, replace Sample Project with the name of one of your projects.)
Select “Show Table…” again and add the BugEvent table. Again, Access should draw a relationship line between the ixBug field in the Bug table and the ixBug field in the BugEvent table. You can add it if it doesn’t.
Drag and drop the sVerb field onto the Fields and set the Criteria to be:
LIKE “RESOLVED*”
Drag and drop the dt field onto the Fields and set the Criteria to be:
> 7/1/01 AND < 8/1/01
Close the Query Builder and save your changes. Close the Report Properties window and the Report Design Window. Click “Yes” to save changes if prompted.
Step 3: View Report
You should see your report in the Reports listing of your database.
Double click on your report and it will list all bugz which were resolved in July, 2001 from the SampleProject project. (If you used the text Sample Project in your criteria or did not resolve any bugz in July,2001 then this report might be empty.)
Step 4: Suggestions
If you are familiar with constructing SQL queries, you can always switch from the Query Builder wizard straight into the SQL text and edit it yourself.
If you want to supply the Criteria arguments at the time you run the report (say you wanted to create a general report for all bugz resolved during a given month), then in the Criteria field for the dates you could write:
> [beginDate] AND < [endDate]
The square brackets tell Access you want these to be variables whose values are prompted for when the report is run. Later when you run the report it will ask you for the beginDate and endDate and you can supply them then.
Step 5: FogBugz schema
Even if you are a database guru, it might help to understand the way the bug data is related in the tables so that you can generate meaningful queries.
The entire schema can be found here.
Most of the field names in the tables match closely to their English counterpart on the FogBugz screen, so I’ll assume you can parse variable names without vowels into English.
You’ll notice most of the relationships are fairly obvious. Most of the primary key/foreign key pairs have the same name in both tables.
For example, the ixProject field in the Bug table will have an id which corresponds to the ixProject field in the Project table. If you join both of those tables on that field (WHERE Bug.ixProject = Project.ixProject) you can get to the sProject (name of the project), ixPersonOwner (which you can further join on the Person table to get Person info), and fDeleted (whether the Project is still active or not).
