TFS Reports using SSRS
| This article is in a draft stage.|
This article is designed to do a walk-through of viewing a TFS report using SQL Server Reporting Services (SSRS). For a video explanation and demo of this same material and other related TFS Reporting information, see the article on TFS Test Tools.
There are two ways to get started using SSRS for viewing TFS Reports. One is in Visual Studio 2010 and the other is on the http://icstfsweb site.
Viewing TFS Reports using Visual Studio 2010
The first way of viewing TFS Reports using SSRS is to start in Visual Studio 2010 and use Team Explorer. To open the Team Explorer window, click on the View tab in Visual Studio 2010, and then select Team Explorer. You can also open the Team Explorer window by pressing the keys Ctrl + W and then pressing M. When Team Explorer opens it will typically show a pane on the right-hand side of the display (see Figure 1).
|Open Team Explorer in Visual Studio 2010 (Figure 1).|
In the example above, the project that is displayed is the MSStack project. If there is no project listed when you open Team Explorer, you will need to click on the Connect to Team Project icon in the upper left-hand corner of the Team Explorer pane (see Figure 2).
|Click on the "Connect to Team Project" icon (Figure 2).|
When the Connect to Team Project icon is clicked, it will display a dialog similar to the one displayed in Figure 3. Only projects that you have rights to will be displayed.
|Connect to a Team Project (Figure 3).|
If no server is selected, then no projects will be displayed. To select a server, click on the Servers... button in the upper right-hand corner. In the Add/Remove Team Foundation Server dialog, click on the Add... button if no servers are displayed in the list (see Figure 4).
|Add a TFS Server (Figure 4).|
In the Add Team Foundation Server dialog that shows up, enter the ICS TFS Server that contains all Church projects in the Name or URL of Team Foundation Server: field, which is
icstfs.ldschurch.org (see Figure 5). Then click on the OK button.
|Type in the ICS TFS Server name (Figure 5).|
That will display the Add/Remove Team Foundation Server dialog again and the ICS TFS Server should be highlighted. Click the Close button to set this as the default Team Foundation Server that will be used from now on. This will return focus to the Connect to Team Project dialog. The default Project Collection in the left pane will be
upgradeFromICSTFS2008 and all projects in this collection that you have rights to will be displayed in the right pane. In the list of projects, select the check boxes of the projects to which you would like to connect and be displayed in Team Explorer. To close the dialog and connect to the selected projects, click on the Connect button.
In Team Explorer, expand the project for which you want to generate TFS reports. This will display several containers below it including a Reports area. Expand the Reports area to see the categories under which many reports exist (see Figure 6). If there are no reports under the Reports category, send an e-mail to TFS@ldschurch.org. There are two reasons why this might happen. Either you do not have rights or the reports do not exist for your project. In the e-mail, include the name of your project and ask that these reports be generated for your project or that you are granted rights to them.
|Show the Reports Categories (Figure 6).|
Expand the category that contains the topic for which you wish to view a report. If you do not see the category, or the report, that you want, later in this document it will show how to create new reports using SSRS.
To view one of the reports at this point is as simple as clicking on it. For example, if the desired report contains information about Bugs, then clicking on the Bugs folder would reveal the reports related to Bugs. If the desired report shows the Number of Bugs in each of the different Bug States, then the report to click on would be Bug Status (see Figure 7).
|SSRS Bug Status Report viewed in Visual Studio 2010 (Figure 7).|
All of these reports can be added to your Dashboard or re-created in Excel.
Viewing TFS Reports using the ICS TFS website
The second way to view TFS Reports using SSRS is to start in a browser and go to the URL: http://icstfsweb (which redirects to http://code.ldschurch.org/tfs/web/). This brings up the ICS TFS web client showing project status of the last project you were viewing (see Figure 8).
|ICS TFS web client (Figure 8).|
If you have never been to this site, then a dialog is displayed listing all the projects that you have rights to so you can choose which one will show up by default when opening the web client (see Figure 9).
|Choose the project to connect to (Figure 9).|
When a project is displayed, click on the Reports link (see Figure 10).
|Click on the "Reports" link (Figure 10).|
On the next webpage, all of the Reports categories are displayed (see Figure 11). Click on the category that contains the topic for which you wish to view a report. If you do not see the category, or the report, that you want, the next section of this article will show how to create new reports using SSRS.
|Team System database (Figure 11).|
To view one of the reports at this point is as simple as clicking on a link. For example, if the desired report contains information about Bugs, then clicking on the Bugs link would reveal the reports related to Bugs (see Figure 12). If the desired report shows the Number of Bugs in each of the different Bug States, then the report to click on would be Bug Status (see Figure 13).
|Available Reports under the Bugs category (Figure 12).|
|Bug Status Report (Figure 13).|
Any report has parameters that can be customized to change Area, Iteration, Date Range, or several other values. Changing these parameter values and then clicking on the View Report button in the upper right-hand corner will slightly modify the report that is generated.
All of these reports can be added to your project Dashboard or re-created in Excel.
If there are no reports under the Reports categories, send an e-mail to TFS@ldschurch.org to make a request for these reports to be generated for your project.
Creating New Reports in SSRS
The first step to creating new reports that are similar to the out-of-the-box SSRS reports is to download and install SQL Server Report Builder 2.0 (SSRB).
Running Report Builder 2.0 will display the SSRB GUI. Start by specifying a Data Source. Click on the New button in the Report Data pane and from the drop down menu select Data Source... (see Figure 14).
|Specify a Data Source in Report Builder (Figure 14).|
This will display the Data Source Properties dialog. Type in a name for the Data Source connection with something like Tfs2010OlapReportDS. Click the radio button to select the option Use a connection embedded in my report and then click on the Build... button (see Figure 15).
|Data Source Properties dialog (Figure 15).|
This will bring up the Connection Properties dialog. Click the Change... button to specify the type of Data Source this is (see Figure 16). When the Change Data Source dialog pops up, select the Microsoft SQL Server Analysis Services Data source and then click on the OK button (see Figure 17).
|Connection Properties dialog (Figure 16).|
|Change Data Source dialog (Figure 17).|
This will display the Connection Properties dialog again. In the Server name: field, enter the name of the TFS Analysis Server which is
chqpvuw1517\AS02. From the Connect to a database drop down list, select the database named Tfs_Analysis. Then click on the Test Connection button (see Figure 18).
|Connection Properties dialog (Figure 18).|
A Test results dialog appears (see Figure 19) if the connection was successful. Click OK. If an error message is displayed, it may be because you do not have rights to the TFS Analysis Server. If this is the case, then send a request e-mail to TFS@ldschurch.org and request access to the TFS Analysis Server.
|Test Connection dialog (Figure 19).|
Click OK to close the Connection Properties dialog. The Data Source Properties dialog will be displayed again with the Connection string filled in. Click OK to finish adding the Data Source and now reports can be generated using the data in the TFS OLAP Cube (see Figure 20).
|A Data Source was added successfully (Figure 20).|
At this point, a report can be created by typing in a Title and then clicking on either the Table or Matrix icon or the Chart icon (depending on which you want to add to your report). The following is an example of a table that can be added to a report. The steps for adding a chart are very similar.
Click on the Table or Matrix icon which will start the New Table or Matrix wizard (see Figure 21). Click on the desired Data Source Connection (there will likely only be one called Tfs2010OlapReportDS) and then click Next.
|New Table or Matrix Wizard (Figure 21).|
|New Table or Matrix query designer (Figure 22).|
The Design a query dialog will be displayed. This is where the filters, rows, columns, and values can be specified that will make up the report. The first step for almost any report will be to narrow the data down to just your Team Project. To do this, start by scrolling down through the list of Measure Groups in the left pane and expanding the Team Project group. Drag the Project Node Name item to the Query Builder area so that it looks like Figure 23 below.
|Filter by Team Project (Figure 23).|
In the Filter Expression column, you will need to specify the name of your project by clicking in the cell and then looking through the drop down list of all Team Projects. Expand and search in the Filter Expression cell drop down menu until you find your project and select the associated check box. Then click on the OK button.
This next section shows an example of how to create a report showing the count of Active Bugs on each day of the month in March 2011. This can be accomplished by doing the following five steps:
- Filter Work Item Type equal to Bug:
- To add a filter that will only get the Work Items of which Type is equal to Bug, search through the Measure Groups in the left pane and expand the Work Item group. You can then drag the item Work Item.System_WorkItemType to the Query Builder area that says <Select dimension>. Another way to do this is to click in the Query Builder on the cell that says <Select dimension> and choose Work Item from the drop down list, then click the next cell that says <Select hierarchy> and choose Work Item.System_WorkItemType. Set the operator to Equal. Then in the Filter Expression cell, open the drop down dialog and expand the list to select the check box next to Bug. Click on the OK button.
- Set the Count as the value to measure:
- To add Count of bugs as the value to measure, scroll to the top of the Measure Groups list and expand the Measures item. Expand the sub-group called Work Item and drag the item Work Item Count to the large box that says Drag levels or measures here to add to the query. This will immediately give a value of the total bugs in the project.
- Show only Active Bugs:
- To show only Active Bugs, scroll through the Measure Groups and expand the Work Item group. Drag Work Item.System_State into the Query Builder and set the Filter Expressions to Active. This should make the Work Item Count value in the report go down to a lower number.
- Specify a date range:
- Specify a date range by expanding the Date group in the Measure Groups list and dragging Date to the Query Builder. Under Hierarchy, select Month and specify that it should equal March 2011.
- Show the Active bugs per day:
- Finally, to show more than just the current number of Active bugs, drag the Date item under the Date group into the large box where the Work Item Count is displayed.
If all goes well, these steps generate a report that looks something like the one show in Figure 24. Now that the desired data is specified, click on Next to format the report.
|Bug report generated in SSRB (Figure 24).|
The Arrange fields dialog is displayed. Depending on preferences, there are several different layouts that could be chosen for this report. One way would be to drag Work_Item_Count in the Available fields pane (on the left) to the Values pane (on the lower right) and then drag Date to the Row groups pane. Then click on the Next button (see Figure 25).
|Arranging fields to format report (Figure 25).|
The Choose the layout dialog appears and for this example, you can just click Next. The Choose a style dialog appears, which is the final dialog of the New Table or Matrix wizard. Click Finish. The "design" of the report is complete and is displayed (see Figure 26).
|Finished Report Design (Figure 26).|
To see what the actual report looks like, it must be Run. Click on the Run button in the upper left-hand corner of the SSRB GUI. It will make all the necessary calculations and database queries to gather the data and format it into the desired report (see Figure 27).
|New created Table Report (Figure 27).|
The last step is to Save this report. Click on the icon in the upper left-hand corner and select Save from the drop down menu. Give the report a meaningful name, for example March Daily Active Bug Count 2011, and then click OK (see Figure 28). This will save the report as a .RDL file. This type of file is really easy to upload to your SharePoint Dashboard (which is discussed in the next section).
|Save the report (Figure 28).|
You can now close the SSRB GUI.
Post a report to the Reporting Site
To post a report to your Team Project SharePoint Dashboard, go to your Team Project's SSRS Reporting site. There are several ways to get to your Team's Reporting site. You could go to http://icstfsweb and click on the Reports icon on your project's TFS webpage. Alternatively, you could look in the Team Explorer pane in Visual Studio and right click on the Reports icon and select Show Report Site... (see Figure 29).
|Team Project's SSRS site (Figure 29).|
On your SSRS Team Project site, you should see the folders that categorize the out-of-the-box reports which include Bugs, Builds, Dashboards, and so on. Click on the Category that best fits with the report that you want to upload. In the previous section, it was shown how to create a customized Bug report. It fits best under the Bugs category. The Bugs folder contains three pre-canned reports. If this is the folder in which you want to upload your report, then click on the Upload File button (see Figure 30). If the Upload File option is not display, send an e-mail to TFS@ldschurch.org and request permissions.
|SSRS Bugs reports (Figure 30).|
The Upload File page will be displayed. Click on the Browse button and find your report (.RDL) file. You can optionally change the name that will be displayed. Click OK (see Figure 31).
|Upload a report file (Figure 31).|
This will add your report to the list of already existing reports in that web folder. You can change the properties of the report by clicking on the Properties tab (see Figure 32).
|New report added to SSRS (Figure 32).|
At this point it is possible that the Data Source will have a problem resolving (see Figure 33).
|Problem resolving the Data Source (Figure 33).|
To resolve this issue, click on the Properties tab and then click on the Data Sources link. This will display the Data Source Properties page. Select the option A shared data source and then click on the Browse button (see Figure 34).
|Data Source Properties page (Figure 34).|
A hierarchy of folders and Data Sources will appear. Click on the Tfs2010OlapReportDS Data Source and then click on the OK button (see Figure 35). If no Data Sources appear, then you do not have permissions and need to send an e-mail to TFS@ldschurch.org to make a request for "My Report" rights.
|List of Data Sources available (Figure 35).|
The Data Source Properties page will be displayed again showing the selected Data Source. Click Apply. The changes will take effect when the Apply button is clicked, but it will still display the Data Source Properties page. To view your report, click on the View tab (see Figure 36).
|Data Source Properties (Figure 36).|
You should now see your customized report and it will be viewable by anyone that has rights to your Reporting Site (see Figure 37). If you want to post this report to your Team Project's SharePoint Dashboard, then you can follow the instructions on how to "Add SSRS report as Dashboard to SharePoint".
|Final report (Figure 37).|
For more information about TFS Reporting see the links under the Reporting sections of this article:
For more information about using SQL Server Report Builder see the following article:
For information about how to add your SSRS report on the SharePoint Dashboard go to this website: