TFS custom report

1. Team Foundation ServerStart

Team Foundation server (TFS) is a server product that needs to be deployed in a software development environment so that developers can use the services it provides. Because TFs is designed for large-scale teams, there are two topologies available: Dual-server and single-server.

In a single server deployment, TFS is installed on windous 2003 Server, and SQL Server 2005 database server, web server IIS, and Windows SharePoint Services are installed on this server. This type of installation can meet the needs of a large number of users and is suitable for most of the conditions.

The dual-server deployment separates the database engine and Analysis Service Components of SQL Server 2005 and installs them on different machines, in this way, scalability can be achieved (by increasing the space for a large number of user registration operations and installing different data warehouses for processing loads on different machines, this machine can be up to 64 bits .)

After the TFS server is installed, the client can access the server by installing team explorer. Team Explorer is a group of components, including the simple version of Visual Studio 2005 (if you have installed Visual Studio 2005 on a machine, simply add more features) and a large number of plug-ins for Microsoft Excel and project. Using Excel and project, you can access data stored in the team Foundation Server database.

Team Explorer can be used to access the following features of team Foundation server:

• Process Guidance
• Work Item tracking
• Version Control
• Automatic Generation
• Report

PS: The authors understand that TFs is something similar to SVN, that is, real-time data control and synchronization.

2.UnderstandingTFSReport Server on the platform

You can customize reports on the TFS (Team Foundation server). No matter what technology you need to know before preparing a report, you must also have a strong mind. Only the people who do this know, it is not easy to get more data.

Reports in TFs are generated based on Microsoft SQL Server reporting services and Microsoft SQL Server Analysis Services. In addition to Visual Studio, you also need some other tools, such as business intelligence development studio.

2.1tfs Database

First, let's take a look at how team Foundation server (TFS) stores information you may want to use in reports. The TFS database can be divided into three types of storage, where data is always flowing from left to right, as shown in 1. Each storage is optimized for a specific type of use, which is described below.

 

The three databases are: online transaction processing database, data warehouse, and Online Analytical Processing database. The functions of each database are as follows:

2.1.1 online transaction processing database: stores data in real time, which is the place where data is stored at ordinary times;

2.1.2 data warehouse: There is a relational warehouse named tfswarehouse in the warehouse. This is the database we use when making reports. The data source of this warehouse is OLTP, the database can be placed on a completely different server to avoid query affecting the TFS speed.

2.1.3 The Last database is not a relational database, but an Online Analytical Processing (OLAP) database. You can access this database through Microsoft SQL Server Analysis Services.

2.2 dimension, fact, Star, and multi-dimensional dataset

Remember this star image, which will be used for reporting. This is the dimension view of the report.

 

2.3 install required tools

First, make sure that all required software is installed on your computer. This document assumes that you have fully installed TFs on an accessible server. Therefore, this section only describes the tools required to install and communicate With TFS data storage and create reports. You need the following tools:

• Visual Studio 2008 Professional edition or Visual Studio 2008 team system
• SQL Server Client tool:
• Management tools (optional)
• Business Intelligence Studio
• SQL Server books online (optional)
• Visual Studio 2008 SP1
• Visual Studio 2008 SP1 Update for Windows Vista (If Windows Vista OS is used)
• Web SQL Server 2005 sp2

Business Intelligence Development Studio (part of the SQL Server Client tool) installs the required tools into Visual Studio to create and customize TFs reports.

Create a Report Server Project

After all the tools are installed"Create a project"The "Business Intelligence Project" dialog box shows a group of new project types, as shown in. After specifying the project name and selecting the location, click"Template"Under"Report Server Project"And then click"OK".

 

After correctly installing all the tools,"Business Intelligence Project"The project type will appear inVisual StudioMedium

The new project should contain only two empty folders ,.

 

Next, I will compare SQL sever2008 and SQL sever2005 report services to design reports.

Both 2008 and 2005 are the same steps to create a data source.
The next step is to add two data sources: one connected to the relational warehouse and the other connected to the Multidimensional Dataset. To add a relational data source, perform the following operations:

1. Right-click the shared data source folder and click"Add a new data source".
2. In"General"Tab"Name"In the text box, typeTfsreportds. This data source name is required for many reports in TFs. Therefore, this name is especially important when you write reports for TFs.
3. In"Type"In the combo box, clickMicrosoft SQL Server.
4. Create a connection string to connect to the SQL server instance that hosts the data warehouse. Click"Edit"Button, and enter the appropriate information in the field. Select the tfs_warehouse database and make sure that the Administrator has granted you the permission to access the database.
5. Click"OK".

Next, you need to create a data source to connect to the cube. To do this, perform the following operations:

1. Right-click the shared data source folder and click"Add a new data source".
2. In"General"Tab"Name"In the text box, typeTfsolapreportds. This data source name is required for many reports in TFs. Therefore, this name is especially important when you write reports for TFs.
3. In the type combo box, click Microsoft SQL Server Analysis Services ".

4. Create a connection string to connect to the SQL server instance that hosts the data warehouse. Click"Edit"And enter the appropriate information in the field. You need to selectTfs_analysisDatabase, and make sure that the Administrator has granted you the permission to access the database.

5. Click"OK".

 

Shared data source generated in the 2008 report

 

Shared data source generated in the 2005 report

Add Report

If you add a report as usual, a wizard is displayed to guide you through report creation. This article will ignore this wizard, so we can jump directly to use the query. To add a report, perform the following operations:

1. Right-click the reports folder and point"Add"And then click"Create a project".
2. In"Add new item"In the dialog box, click"Reports", Specify the required name, and then click"Add". The example in this article uses the name "test report ".

Now, you should see the report in the Reports folder in Solution Explorer. When you open a report (it is usually automatically opened when you add a new report), you will see a dialog box that contains three tabs:"Data","Layout"And"Preview".

Generate simple query

In this section, we use"Data"Tab. To create a new dataset that is connected to a cube, perform the following operations:

1. In"Dataset"In the combo box, click"Create a dataset". This will open"Dataset"Dialog box.
2. In"Name"Enter a name in the text box. The example in this article uses the name dstest.
3. In"Data Source"In the combo box, click"Tfsolapreportds(SHARE)". This operation connects the query to a multi-dimensional dataset instead of a relational warehouse.
4. Click"OK"To create this dataset.

The result of running on the 2005 Report Server. We will focus on the results of the report Presentation in SQL sever2008 R2:
When you click dataset properties,

 

It will have the same effect as SQL sever2005, and 2005 will appear directly in the design window during design, in 2008, the desired data set (operation data) will appear in the query designer. The effect after clicking the query designer is as follows:

Click here to see if it is the same as what I said. There is no problem! In this way, we can process and extract the data we want, that is, the dimension we mentioned above.

 

When you click "…", When this sign is used, we are selecting a dataset. Are the letters in the pop-up window very familiar ~ It is the word that we remember when explaining the dimensions for everyone. OK. We can select any dimension dataset. For example, shows the work item:

This may be the set you want ~ Then, confirm the set we want, and click OK. Check whether the data set has changed as follows:

Now that we have the data we want, we can present it in the way we want, and we can extract the way we want to present the data in the toolbox, for example, I chose to use the tables Table for display. The layout is as follows:

We can click "run". Is it a little fun before clicking "run ~

Although ugly, isn't it the same as what we want? If we have prepared a satisfactory report, what should we do next? It is very simple, that is, publishing can be used by more people:

Publish a report

For a deployment project, it may be the best time to view how to deploy the Report to the TFS reporting services server. This is actually very simple, but not necessarily very obvious. After Visual Studio is started, you can directly deploy the Report to the project you selected. To do this, perform the following operations:

1. In Solution Explorer, right-click a report project and click"Attribute". The "properties" window is displayed.
2. Make sure thatOverwritedatasourcesSetFalse. In this way, the data source created for your project is retained on the Report Server.
3. ForTargetperformancefolder, Enter the name of your TFs project because it is the name of the folder created by TFs for the project report on the Report Server.
4. ForTargetreportfolder, Enter the name of your TFs project, followed by any subfolders (if you use folders to organize reports ).
5. ForTargetserverurl, The content should be similar to http ://<Server Name>// Reportserver,

 

1. After setting the deployment options, right-click the report in Solution Explorer, and then click"Deployment"Add a Report to the server (or update it ).

So far, we have successfully customized reports on the TFS platform ~~ Give me a round of applause!