Test Cases Statistics with SSRS and TFS Data Warehouse

Test Cases Statistics with SSRS and TFS Data Warehouse

Test Cases Statistics with SSRS and TFS Data Warehouse

I’m proud to present to you the first guest blog post on Automate The Planet. Its theme will be how to setup beautiful and useful test cases statistics using SSRS and TFS Data Warehouse. The author of the article is my colleague- Kristina Bankova, who is a Quality Assurance Engineer.

About TFS data warehouse

Team Foundation Server includes a data warehouse where data about work items and builds is stored.Team Foundation uses this data for its built-in reporting functionality. The warehouse includes both a relational and an OLAP database. The relational database is organized in an approximate star schema, and the OLAP database is a data cube derived from the relational database.
Tfs_Warehouse is the relational warehouse that is used in the statistics with SSRS explained below.
Due to performance reasons, the OLTP database Tfs_DefaultCollection should not be used for reporting purposes. Tfs_Warehouse data is populated from the OLTP database, so if you make changes to your test cases in Microsoft Test Manager (MTM), there may be a delay up to 2 hours in order the changes to be reflected in your reports.
For more information about the Team Foundation Databases and  Tfs_Warehouse schema.

What is SSRS?

Reporting services (SSRS) is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting services are fully integrated with SQL Server tools and components.

Test Cases Statistics Report Examples

Various reports can be prepared, according to the needs, we choose this approach because of the flexibility of the reports. We needed test cases statistics for their automation status and distribution by priority.
Status “Not automated” means that the test case has no associated automation in Microsoft Test Manager (MTM). “Planned” status is for test cases that should be automated but are not automated. “Automated” are those with associated automation test case in MTM. You could also have this information in the form of a table.
Instead of priority 1,2,3,4 we wanted to have “Very High”, “High”, “Medium” and “Low” respectively, so that is the reason behind the CASE statement

Deleted test cases in Microsoft Test Manager are still present in the Tfs_Warehouse, but they do not have a test suite and filtering should be made in such a way to exclude them:

Below you can find a couple of test cases statistics reports, created using Tfs_Warehouse and SSRS:


Test Cases Statistics By Automation Status

Test Cases Statistics By Priority


Automation Status By Test Suite

You have to setup a data source and data set in SSRS with data from that data source in order to prepare your reports.

How to Setup a Data Source in SSRS?

1. Open existing or create a new report in Report Builder
2. Right Click and choose “Add Data Source

Add Data Source

3. Type data source name. For example, Tfs2010ReportDS (could be added as shared data source also).
4. Choose “Use a connection embedded in my report
5. Select connection type: “Microsoft SQL Server
6. Build data source connection string:  Data Source=YOURSQLSERVERNAME;Initial Catalog=Tfs_Warehouse;Integrated Security=SSPI;
7. Choose data source credentials: Username: myuser Password: mypassword. The checkbox “Use as Windows credentials” should be checked.

Enter Credentials

8. Test the connection.

Test Connection


How to Setup a Dataset?


Add Dataset

1. Select Tfs2010ReportDS data source.
2. Use the sample query below.
The data for the dataset used in the reports was obtained via querying TestResultView and CurrentworkItemView. We choose to use the views, because it is the recommended approach, as the table structure could be a subject to frequent changes.

In order to execute the query, you need to have valid credentials for accessing Tfs_Warehouse.

Data Set Properties

Next from the series SSRS SQL Server Reporting Services- Subscriptions for Reports