Creating or customizing a new report for the project
is always based on the project reporting requirement. In some cases, the
existing reports may not be suitable or will not provide enough
information required for the project. In TFS, all the information is
stored in MS SQL Server, and it uses the SQL Reporting Service and
Analysis Services for reporting purposes. With the existing installation
of Visual Studio, we cannot create a new report or customize the
existing report. It requires an additional tool called the Business
Intelligence Development Studio, which comes along with the SQL Server
2005. There is another option in MS EXCEL, which uses the pivot
tables to get data from the SQL Server databases and present it in
spreadsheet. However, the following are possible if we are planning to
use Visual Studio for creating reports:
Write your own SQL queries to get the data
Publish and share the report through Team Explorer
User can customize the report
High performance report
Creating a new
report involves understanding the database structure and designing.
Before getting into the actual design of the new report, let's look at
the different databases and how the SQL Server databases are structured
by TFS for storing the data. It is divided into three different stores
each having its own purpose:
OLTP Database:
The Online Transaction Processing store contains multiple databases to
maintain all the transactions. It has got sets of databases for
maintaining the build, version control, work item tracking, and activity
logging. These are the databases tuned for high performance to save and
retrieve the transactions online.
Relational Warehouse:
This store is built for queries instead of online transactions. This
database is optimized for queries and reporting instead of transactions.
Data is transferred into these databases periodically using adapters
collecting data from the tools like work item tracking, build, and other
tools.
OLAP Cube: The
third one is the Online Analysis Processing Database, which stores the
historical data for future reference. It has its own query language.
This can be maintained and accessed using the SQL Server Analysis
Services.
We will be using the above
database for building and designing our new report. The warehouse
database is broken down into dimensions and facts, which is more
important for the reports. The dimensions are the parameters, and facts
are the actual values. Parameters are used for controlling the data to
be fetched from the store while the facts are the actual values like the
count of defects or build number or work item ID. You can explore more
on these databases and tables using the SQL Server Management Studio.
The TfsWareHouse is the name of the warehouse database in SQL Server.
We will start designing a new
report, so let's not go into the details of the database. To get started
with reports, we have to make sure we have the required tools. The
following are the list of tools required:
The TFS installed and is available for the clients
SQL Server client tools containing the Business Intelligence Development Studio, which is mandatory
VSTS
Report server project
After installing the required tools, Visual Studio will have the Report Server Project in the Templates of the Project types list. Otherwise, we can also launch the Visual Studio using the SQL Server Business Intelligence Development Studio menu option available under the Windows Programs menu.
Select the Report Server Project, name it and select OK. The solution and project get created and the project contains two folders, Shared Data Sources and Reports.
For creating the report, we need to connect to the OLTP and warehouse
databases. Create or add the data sources by following these steps:
1. Select the option Add New Data Source by right-clicking the Shared Data Sources.
2. Name the data source as TfsReportDS. The name is important here as most of the reports in Team Foundation Server are using this data source name.
3. Select Microsoft SQL Server as the Type of the data source.
4. Click on the Edit... option next to the Connection String box and in the new window, select the server name from the list or enter the server name used by the TFS.
5. Select the TfsWareHouse database from the list and click on OK.
Now select the same New Data Source option for adding another data source for Online Analytical Processing (OLAP). Name the data source as TfsOlapReportDS and select the Microsoft SQL Server Analysis Services as the type of the data source. Select the same database TfsWareHouse and click OK. Now there should be two data sources, TfsReportDS.rds and TfsOlapReportDS.rds, under the Shared Data Sources folder in the solution explorer.
We have created the
reporting project in Visual Studio and created the required data
sources. Now we can proceed towards creating our report. Follow these
steps below to create a new report:
1. Open the Solution Explorer if not opened.
2. Right click the Reports folder and select Add | New Item.
3. Select the Report template from the available templates.
4. Enter the name for the report and click OK.
Now the template is ready in
the designer, but we have to edit the report and redesign it based on
our requirement. There are three tabs in the designer: Data, Layout, and
Preview.
Data: This tab is used for selecting the dimensions and the facts for the report. It also helps select the parameters and formats.
Layout:
This is the surface for designing the report. Using the dataset built in
the data tab, we can place the fields on the surface. This is also used
for selecting the report format such as Tabular, Matrix, or List.
Preview: This
tab is used to see the preview of the report we have designed using the
data tab and the layout tab. This is the exact report that will be
deployed or used by the user.
Let us see the steps involved in modifying the report to meet our needs in the Data tab:
1. Create a new dataset to get data from the OLAP cube. Select the<New Dataset…> option from the Dataset drop-down list.
2. Enter the name for the data set as DataSet1.
3. Select tfsOlapReportDS (shared) and then click OK.
Under the Dataset
selection option we can see the Build Cube selected by default. Select
the option in the cube box to open the cube selection window and select
the Team System Cube,
which contains all the cubes. The cube is totally based on the type of
data we need for the report design. Let us select the Team System cube.
The Data tab now contains four different sections—Metadata, Calculated members, Query Results, and Dimensions or filters. The Metadata section contains all the dimensions and facts from the cube, which we will select for the report.
Now let's try building a
simple defect report, which contains only three or four fields to show
the details such as the bug title, the bug current status, and the
status change count for each defect. We will also add parameters for
data range and bug status. Follow these steps to select the dimensions
and facts for the query:
1. In the Metadata tab, under Measures, we can see all the dimensions from the Team System Cube. Scroll down in the list and select the Work Item dimension and select the Work Item.Title and drag that onto the query results surface. Do the same thing for Work Item.State.
2.
Lets us also try to get the history of state change counts for the
defects. To do this, Expand Work Item History under Measures and select State Change Count and then drag-and-drop that onto the query results surface. Keep selecting whatever data is needed for the report.
3.
Now we have selected the data to be presented on the report but not the
filters. Let us select the date filter and the work item state and type
as filters and parameters for filtering the data:
a. Select the Team Project.Team Project attribute in the Team Project dimension from the list in the Metadata and drag-and-drop that into the Dimensions section. In the Filters area, select the Team Project dimension from the grid and select and choose the team project in the Filter Expressions drop-down. This will filter the displayed results.
b. Select the Date.Date attribute under the Date dimension and drop that into the Dimension area. In the Hierarchy drop-down, select the format for the date. To have the date range, select the option Range(Inclusive) option from the drop-down under the Operator column. To make the date to be parameterized, select the check boxes against the Date dimension under the column heading Parameters.
c. Select the Work Item.State attribute from the Work Item dimension and drop that onto the Dimension area.
d. The next
important thing is the Work Item Type. The above selections will
display all the work items irrespective of their types. But we need only
the bugs, not the all work items. To filter this, select the Work Item.Work Item Type from the Work Item dimension and drop that onto the Dimensions filter section. Under the Filter Expression, select the type as Bug.
4.
On dropping the filters and dropping the attributes on the query
results area, we can see that the query gets executed and displays the
results. This is to verify whether the selection is providing the
expected result.
The final Data tab of the report would look like the one shown here for the sample report:
