This article outlines how to get started with creating and deploying a dashboard against a native TestTrack project, including samples, using SQL Server 2008 Reporting Services.

Want Seapine to develop and deploy a KPI dashboard for your team? Email us for more information.

Getting Started

First you’ll need to install the native ODBC driver for the native TestTrack database.

Once you’ve installed the ODBC driver, create a DSN to connect to your TestTrack Project.

Visual Studio Shared Data Source

To create reports you’ll need to install Visual Studio 2008.

Create a shared data source, connecting to the DSN you just created.

Click Test Connection to make sure everything’s working!

Reports

Now you’re ready to start creating reports. How to do that is beyond the scope of this guide, but you can download a sample below.

Samples Download

Once you have the dashboard live, you can integrate it into TestTrack.

Queries

Following are the queries used in our native dashboard sample.

State Distribution

Select count for each state type.

 SELECT  STATES.Name,
         STATES.idRecord,
         count(DEFECTS.DEFECTNUM)
   FROM  DEFECTS,
         STATES
  WHERE  DEFECTS.Status = STATES.idRecord
 GROUP BY STATES.Name, STATES.idRecord

Priority Distribution

Select count of open issues for each priority type.

Note: If the project is modified and the Priority field is relabeled, this query will be querying against that new field (label).

 SELECT  FLDPRIOR.Descriptor,
         FLDPRIOR.idRecord,
         count(DEFECTS.DefectNum)
   FROM  DEFECTS,
         FLDPRIOR,
         STATES
  WHERE  DEFECTS.Status = STATES.idRecord
    AND  DEFECTS.idPriority = FLDPRIOR.idRecord
    AND  STATES.Attrib = 1
 GROUP BY FLDPRIOR.Descriptor, FLDPRIOR.idRecord
 ORDER BY FLDPRIOR.idRecord DESC

Severity Distribution

Select count of open issues for each severity type.

Note: If the project is modified and the Severity field is relabeled, this query will be querying against that new field (label).

 SELECT  FLDSEVER.Descriptor,
         FLDSEVER.FieldOrder,
         count(DEFECTS.DefectNum)
   FROM  DEFECTS,
         FLDSEVER,
         STATES
  WHERE  DEFECTS.Status = STATES.idRecord
    AND  DEFECTS.idSeverity = FLDSEVER.idRecord
    AND  STATES.Attrib = 1
 GROUP BY FLDSEVER.Descriptor, FLDSEVER.FieldOrder
 ORDER BY FLDSEVER.FieldOrder DESC

Product Distribution

Select count of open issues for each product type.

Note: If the project is modified and the Product field is relabeled, this query will be querying against that new field (label).

 SELECT  FLDPROD.Descriptor,
         FLDPROD.idRecord,
         count(DEFECTS.DefectNum)
   FROM  DEFECTS,
         FLDPROD,
         STATES
  WHERE  DEFECTS.Status = STATES.idRecord
    AND  DEFECTS.idProduct = FLDPROD.idRecord
    AND  STATES.Attrib = 1
 GROUP BY FLDPROD.Descriptor, FLDPROD.idRecord
 ORDER BY FLDPROD.idRecord DESC

Related posts:

1. TestTrack MySQL Dashboard
2. Common and Complex T-SQL Statements for Custom Reporting
3. SharePoint Defect Dashboard
4. Automation Rules Report
5. SQL Server Reporting Services