In my last post, SQL Is Still Your Friend,  I wrote about using SQL to query TestTrack data.  As a continuation of that thought, I wanted to bring a query tool to your attention: Microsoft Excel.  There are plenty of alternatives to using Excel for queries but it has two major advantages: 1) almost everyone uses MS Office so your spreadsheet is portable and 2) after you retrieve the data from the database, you can use the extensive library of Excel functions to work with the data.

Here’s the process I use to query TestTrack data into MS Excel 2007 (other methods are possible and these steps may change if you are using a different version of Excel):

1. Unless you are using SQL Server as a database program, you will need to create an ODBC connection for your database.
2. From a new spreadsheet, select the Data tab and from the ribbon, select From Other Sources -> From Microsoft Query (Microsoft Query is a tool usually installed with Excel).  Select your TestTrack database connection.
3. In Microsoft Query, you can build a query by adding tables and linking fields but I find that the complexity of the TestTrack database makes this a long process. Instead,  I prefer the View -> SQL option so I can type or paste SQL queries. When the query is complete, save the query then select File -> Return data to Microsoft Excel.
4. Excel will prompt you for how to manage the data and what cell to use as the upper left corner cell for the results. I use the option Table and put the data into cell B2.
5. Unfortunately, column headers will have to be relabeled but everything else is returned as it is in TestTrack.

Now that you’ve made the connection to TestTrack, you can refresh the data to see updates in TestTrack by selecting the Data tab and Refresh All. You can also break the connection to use the data “as is”. Here is an example of a spreadsheet I created:

At this point, you can create charts, use Excel’s functions, etc. as you would any other spreadsheet. Take advantage of the power of Excel over static report data.

Related posts:

1. SQL Is Still Your Friend
2. Surround SCM Charts
3. Surround SCM Web Part
4. Surround SCM MS Project Add-In Toolbar
5. Importing Data into TestTrack