Use Microsoft SRS To Automatically Send Open Tasks To TFS Users
I have already posted the SQL code you can use to get a list of all open work items across all projects within Team Foundation Server 2008 (TFS). Now I will walk you through one way to create a SQL Reporting Services Report using Microsoft SQL Server 2005’s SQL Server Business Intelligence Development Studio. Then we will see how to schedule it. Note: We will not cover how to configure SRS. This is assuming that SRS is already setup with email settings.
This sample was created directly on the Microsoft Team Foundation Server.
1. Open SQL Server Business Intelligence Development Studio
2. Click Create Project. There are several ways to create the report and in this example choose the template named Report Server Project Wizard. Provide a name of TFSItems and save the project anywhere.
3. The Welcome to the Report Wizard screen is displayed. Click Next.
4. Create a new data source and name it dsTFSItems. Click Next.
5. Click Edit and provide connection information to your SQL server. Set the database to TfsWorkItemTracking. Click OK.
6. The data source connection string is now filled in. Click Next.
7. Copy and paste the SQL statement from my earlier post. Note: you will probably have to correct the single quote characters because they will probably not paste accurately and you will get syntax errors when clicking on Next. Click Next.
8. Select the Tabular report type and click Next.
9. Move all of the fields into the Details section. Click Next.
10. Choose a table style. In this case, I chose Slate. Click Next.
11. Make sure the report server field is correct. Leave the deployment folder with the default value. Click Next.
12. The final screen of the wizard is displayed showing a summary of what the wizard will do. Click Finish.
13. You now have a TFS report in Visual Studio.
14. We want to be able to select a specific person and see just the tasks that are assigned to them. To do that we need to have a second dataset. Click on the Data tab and then under Dataset choose <New Dataset…>
15. Name the dataset dsAssignedTo and paste the following SQL into the Query String field. Note: You may have to adjust the single quotes after pasting.
SELECT Person, Email FROM TFSWarehouse.dbo.Person WHERE Email IS NOT NULL AND EMAIL <> ” AND EMAIL LIKE ‘%@%’ ORDER BY Person
16. Now we need to add a report parameter. To do so, click on Report, Report Parameters in the menu.
17. Create a report parameter named AssignedTo and select from the dsAssignedTodataset as shown below.
18. Now we need to use this report parameter in our main dataset. Select the dsTFSItemsdataset and add one more WHERE clause: “AND w1.[Assigned To] IN (@AssignedTo)”
19. The report is done. We now need to deploy it to the SRS server. To do so, right click on the project name in the Solution Explorer and choose Deploy.
20. Likely near the bottom of your screen will be the Output window and it should indicate the deployment was successful.
21. Now, open Internet Explorer and browse to the report server at http://localhost/Reports. You will notice a folder named TFSItems which was created when we did the deploy.
22. Open the TFSItems report folder and you will see the report we created, TFSDailyItems.
23. Click on the report and you will see that we have a drop down list of various people in TFS.
24. Select a name and then click on View Report and you will see their open items.
25. To schedule this report, click on the New Subscription button. Fill out who you want the report sent to and how often. Then select their name from the AssignedTo report parameter.
26. To see which subscriptions have been setup, click on the Subscriptions tab of the report.
That’s it. Now every day at the time you specified an email will be sent with their open TFS items from all projects.