RyanDev.com

It is about time.  Visual Studio finally supports multiple monitors.  Most developers I know use multiple monitors as we switch between Visual Studio, email, SQL Management Studio, the Internet, etc. but now we can split code up between multiple monitors using Visual Studio 2010 and floating windows.  http://msdn.microsoft.com/en-us/library/z4y0hsax(VS.100).aspx

As a new user of jQuery I find the syntax very hard to follow.  From some of the samples I have worked with it is extremely powerful.  Microsoft has announced plans to support and contribute to the jQuery effort.  That is great news and I believe that will mean better integration into Visual Studio and IntelliSense making jQuery much easier to use.

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.

I have been playing around with a way to have Microsoft SQL Server Reporting Services (SRS) send a list of open items from Microsoft Team Foundation Server 2008 (TFS) to anyone who has open assigned TFS tasks within all projects.  The database structure is not entirely clear nor does it appear that there are stored procedures to retrieve this information.

While doing SQL traces I discovered that TFS uses in-line SQL statements and builds dynamic SQL statements instead of executing stored procedures.  As such, I have tested the SQL statement below and it appears to work correctly.  One thing I am struggling with is any project created using the eScrum template.  It does not store Areas and Iterations of Sprint Backlog Items the same way.  If you have figured it out, please post a comment, and I’ll take a look at it some more later.

Often when writing XML files to disk as text files developers do not include any formatting.  As such, if you need to open the file and examine it in Notepad, for example, and make changes it may be very hard to read.  There is an easy way to fix this.  You can download the executable file hereor just use the following code in Visual Studio.  NOTE: This is a console application expecting a file name as a command line argument.  You can drag your text file onto the executable file and a new file will be created with the same name as the file name with “-formatted.xml” appended.

Database auditing tools are extremely valuable when you need to be able to determine who made a certain change or when trying to figure out how a particular value was set.  Microsoft Dynamics CRM does not have any native auditing beyond storing who created a record and who modified it.  There is no way to see which fields have changed and what their previous values were.

However, we can easily create our own auditing plugin tool and easily audit all fields or just specific fields for specific entities.  Note: All code samples and documentation contained in this post assume that you have access to the CRM server directly, that the user account running the CRM Async Service has permissions to read the registry on the CRM server, and that the Prefix in the Customization tab in System Settings is the default “new.”

First of all, we need an entity to store the audit records.  We could store the audting records in their own SQL database; however, we have chosen to store the records in CRM so that we can use native features of CRM to find data, such as Advanced Find and CRM Reports.  Download the custom audit entity here.  Refer to CRM documentation on how to import and publish the custom entity.

Secondly, we will store a few configuration settings in the registry on the CRM server.  Download the .reg file here and make the necessary changes to it before you import it into your registry.

Lastly, we need to register the plugin. We have used the free plugin developer tool that comes with the CRM SDK and it can also be downloaded in its compiled version at the end of this post. You can download the source code for the plug-in here and screen shots are below.

Steps to register the plugin

Step 2: Browse to the .dll file and then register the AuditPlugin class into the database.  Note, it is best to do this directly on the CRM 4.0 Application server.

Step 3: Plugin should register successfully.  If you get any errors, refer to CRM support forums for troubleshooting suggestions.

Step 4: Register a Create step.  Fill in the form as shown in the screen shot below.  As you are typing you will notice that there is some auto-suggest or intellisense built-in.  As shown in this example, we are registering the Audit Plugin to run anytime an account is created in CRM and the audit plugin will have access to all attributes on the account record that is created.

 

Step 5: After creating the Create step your screen should look like the following.

Step 6: Register the Update event.  As shown in the screen shot below we are telling CRM we want our plugin to run any time any attribute on an account record changes.  What will happen is each time a user clicks Save or Save and Close on an account in CRM, if any of the attributes have changed they will be passed to our audit plugin which will create audit records in CRM.

Step 7: This steps shows how you need to register Post and Pre Images for each Step as well.  This allows the audit plugin to know what the value was before it changed and what it is after the change.

Step 7b: A sample of creating a Pre image.  Note, you will need a Pre and Post image for all Update steps and only a Post image for any Create steps.

You can see that it is pretty simple to register the plugin to be able to audit any entity or even any attribute that you want. 

Resources:

CRM Plugin Registration Tool

CRM Audit Plugin Package (includes everything mentioned)