RyanDev.com

Most all items in Microsoft Dynamics CRM 4.0 are URL addressable.  If you run a report, copy the URL, and then send the URL to someone when they click it the report will open on the Report Filter page and they will still have to click Run Report to see the report.

To have the report automatically run when the URL is clicked change the query string parameter action from filter to run.

For example, instead of:

https://crm.ryandev.com/crmreports/viewer/viewer.aspx?id={DD6204B8-1514-DD31-9ED5-050C25C4FE07}&helpID=Top%20Knowledge%20Base%20Articles.rdl&action=filter

do:

https://crm.ryandev.com/crmreports/viewer/viewer.aspx?id={DD6204B8-1514-DD31-9ED5-050C25C4FE07}&helpID=Top%20Knowledge%20Base%20Articles.rdl&action=run

Adding any view, including custom views, into the Site Map in Microsoft Dynamics CRM is very easy to do.  Perhaps you have created a view that you want people to have easy access to or maybe you are trying to setup the navigation in a way that your users will best understand.   

Since everything in Microsoft CRM is URL addressable the first step is to determine the URL of the view you want to add to the Site Map.  To do this, navigate to the view in CRM, click on More Actions, Copy Shortcut, Of Current View as shown in the screen shot below.

Depending on your browser version and settings, you will likely get a prompt warning you that CRM will be accessing your clipboard.  Click Allow Access.

The URL of the CRM view is now on your clipboard.  Paste the URL into NotePad for use in a few moments.

Now you need to export the Site Map from Microsoft CRM so you can make the change and then re-import the Site Map. 

Click Settings, Customization, Export Customizations.

Select the Site Map from the list and click Export Selected Customizations.

Click OKto Microsoft CRM’s message.

You will then be prompted to save a zip file that contains the Site Map.  Click Save.

Name the file SiteMap.zip and save it somewhere on your computer such as your Desktop.

Unzip SiteMap.zip and open customizations.xml in NotePad.  Now, either add the URL you copied earlier to an existing entry in your site map or add a new sub area wherever you want it to be displayed in your Site Map.  The SubArea will look like the following:

---

<SubArea Id=”UniqueID” Url=”/../_root/homepage.aspx?etc=1&viewid=%46b5CC8A38-2374-DD11-955C-000C29ECA931%7d” Title=”My Custom View” />

---

Note: The URL that was copied from CRM earlier will need slightly modified.  After the “?” in the url you will see the ampersand “&” right before viewid.  You will get errors trying to import the customizations back into CRM because of the incorrect XML syntax.  You will need to replace the “&” with “&” to be able to import.Second Note: You will want to use a relative url meaning that you remove the http://servername/ portion from the url that was copied earlier.  This is important so that Microsoft CRM in offline mode will still work. Third Note: Please refer to Microsoft documentation for help with specific syntax within the Site Map XML file.Now, all you have to do is import the customizations back in to Microsoft CRM.  Go back to Settings, Customization, and choose Import Customizations. 

Select the file you saved.

Click Upload and Microsoft CRM will read in the XML file and will display a list of all entities available to upload.  Select Site Map and click Import Selected Customizations.

Click OK to CRM’s prompt.

Note: Unlike importing most other customizations, there is nothing to Publish after importing the Site Map.  To see your changes you may need to refresh IE by pressing Ctrl+F5 or sometimes you even have to clear out your history to see the changes take effect.

When possible you will want to use relative URLs instead of absolute URLs in your SiteMap.  For example, if you want to link to a custom view from within your SiteMap you would select the View as normal, click on More Actions and choose Copy Shortcut, Of Current View.  However, this will copy into your clipboard the entire URL including http://crm.somesite.com/. 

The problem with including the entire URL is that the Outlook client will not work when you go into offline mode even though the URL is actually pointing to a view within CRM.   So, instead of doing something like http://crm.somesite.com/_root/homepage.aspx?etc=1&amp;viewid=%7b3B3A7E9E-653E-DE11-A323-000C29ECA927%7d you would instead use /../_root/homepage.aspx?etc=1&amp;viewid=%7b3B3A7E9E-653E-DE11-A323-000C29ECA927%7d. 

If you have the IE Developer Toolbar installed I imagine you have found the annoying bug in it.  The toolbar can be activated using the shortcut keys Ctrl+Shift+R; however, at seemingly random times the toolbar will activate with just a Shift+R.  So, as you are typing into a field on a form the toolbar may suddenly popup when you try to type a capital “R”.

As annoying as it is, you can either disable the toolbar or simply turn it back off with Ctrl+Shift+R.  Shift+R will then work again.

Microsoft CRM 4.0 Quick Find is a great feature.  It allows you to quickly search for records without the need to go through Advanced Find.  Where Quick Find lacks is in the ability to modify the filter.  For example, Quick Find will only search for active records.  For some entities this may be OK but often when searching for Accounts or Contacts you may want to include inactive records.

Unfortunately Microsoft CRM 4.0 does not have a supported method for including the inactive records.  The customization window does not allow for modifying the filter criteria of the view.

There is; however, an easy unsupported way of including inactive records. 

1. Click on Settings, Customization, Export Customizations.

2. Select Account (or whichever entity you want to modify) and click on Export Selected Customizations.

3. Click OK to the warning window.

4. Click Save on the File Download window.  Save the file somewhere on your desktop.

5. Extract the Customizations.zip file and then open the Customizations.xml file in NotePad.

6. Search for “Quick Find Active.”

7. Remove the filter condition as shown in the screen shot below.

8. Save the XML file.  Back in CRM, click on Settings, Customization, Import Customizations.  Browse to the modified XML file and click on Upload.  This will read the XML file to determine what customizations are contained in it.  Make sure Account is selected and choose Import Selected Customizations.

9. Lastly, publish the changes.  Click on Settings, Customization, Customize Entities.  Select Account and click on Publish.

Note: This is an unsupported method; however, it will work on any of the entities including custom entities. 

Everyone has heard of offshore development by now.  For better or for worse many jobs have been moved offshore to places such as India or China.  In America we can either be upset by off-shoring or we can take advantage of it. 

Rightshore development is offshore development done right.  I will not recommend any specific offshore development companies; however, I will provide some tips as to what to look for and how to turn offshore into rightshore with respect to software development.

Find a company who practices Scrum- Why is this important?  The foundation of Scrum insists that the development team is self-managing.  Since your offshore team will be in a different country and likely a different continent you need a team that does not need handholding.  You need to feel confident handing over a set of requirements or tasks to your rightshore team that they will complete them successfully and accurately without requiring too much of your own time. 

See the Scrum Alliance for more information on Scrum.

Low Turnover-Select a company who has a low turnover rate of software developers.  You want to have a core group of developers which learn to understand your business and your company culture and if you are having new developers rolled in and out of your rightshore team you will have to continually battle the learning curve.  A rightshore development team should be an extension of your own core team within your company.

Version Control- Version control is critical when transforming an offshore team into a rightshore one.  It is imperative that you always have the latest copy of source code your rightshore team is working on.  You need to review their code and make sure it adheres to your standards and policies.  It does not matter if you have a single source code repository or if you manage 2 different repositories as long as your processes ensure collaboration between both teams.  It can work very well if there is a single source code repository from which both teams checkout their code to work on.  An alternative is to allow the Scrum, self-managing, rightshore team to have their own version control from which they do their own QA and when they send updates you can check them into your own source control and perform your own QA. 

Microsoft Team Foundation Server- It is essential that you have a single location to store your backlog of to-do tasks.  I recommend Microsoft Team Foundation Serverand the Agile project template.  Here you will record your to-do tasks, referred to as sprint backlog items and product backlog items.  You will assign tasks to the appropriate team and even team member if desired.  You will track ETAs, bugs, and progress.  Microsoft TFS provides a central location to store discrete tasks from which developers can work effectively.  You can also have a list of assigned tasks sent to each developer each day.  Go here for more information.

Communication Skills- It is imperative that the company you chose assigns a project manager who can communicate in English and has a lead developer who can effectively communicate in English.  It is not necessary for all team members of your rightshore team to speak or even understand English.  It is critical; however, that the lead developer can effectively communicate, both orally and in written format, to ensure that your business needs and the design goals are being fulfilled.  You should have frequent (daily) phone calls to discuss open issues and allow for a dialogue to resolve questions and concerns.  You will need to make sure information is repeated to ensure both parties understand each other accurately.  Some offshore companies charge more for developers who can speak English and you must be willing to make the investment.  Poor communication between teams will kill any project.

Consistency- For an offshore team to become a rightshore team they must provide consistent and reliable code.  It is important to maintain the same members as is feasible so that you can rely on consistency.  You have to be able to pass on projected completion dates of tasks to your client based on the ETAs the rightshore team provides you.  You need to have confidence that their estimates are accurate so you can accurately estimate to your client or know how much to buffer your estimate.

Identical Environments- You do not want to spend time hunting down bugs after your rightshore team has provided working code only to find out that a difference in your environment is the root cause of the bug.  You need your environment and the environment the rightshore team is developing in to be identical, at least as much as is possible.  The best way to do this is by using virtual machines.  You create a development environment on virtual machines in which you will work and then you send your VMs to the rightshore team.  This way, both teams are working off of essentially the same systems.  If possible, the rightshore team should remote into the same environment which your team uses but often due to geographical distances and locations that may not work well.

Some have tried to go with the cheapest outsourcing companies and doing so will not result in a solid, useful, and successful project.  As the saying goes you have to spend money to make money.  If you follow my suggestions you will find there are companies who can deliver on each of these points and still at a price point which allows you to realize desired profits.

In a rain forest, in particular, trees can grow to enormous heights and sizes.  As they become the dominant plant life they shade everything below them which prevents any other vegetation from growing very large.  When a tree dies and falls in the rain forest a hole is left in the canopy which allows the sun to penetrate to the rain forest floor.  When this happens there is a race by various plants and vegetation to become the next biggest item. 

The same analogy can be used to examine one of the great benefits from our current down economy.  We have many “tall trees” that are dying and falling down.  Some people, including our government, believe we cannot let a dead tree fall.  They believe we should spend billions of dollars to keep a dead tree standing.  Unfortunately, there is little we can do with our current government.

What we can do is take advantage of the “holes opening up in the canopy.”  Now is a great time for small business to fine-tune their processes and offerings to the market as there will naturally be a race to fill the large gap that large companies are leaving behind.  America is run by small businesses but little attention is often thrown their way.  Now is a great time for small companies to take advantage of large company failures, to prove to the market that their products and solutions will satisfy market needs.

There is no reason to have a negative attitude during a down economy, for those who move quickly and take advantage of the gaps will reap rewards for many years to come.

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.

---

SELECT w1.ID, w1.Title, w2.Iteration, w2.[Iteration Path], w1.[Work Item Type], w1.State, w3.Email
FROMWorkItemsAre w1
LEFT JOINTFSWarehouse.dbo.Iteration w2 ON w1.IterationID = w2.__ID
LEFT JOINTFSWarehouse.dbo.Person w3 ON w1.[Assigned TO] = w3.Person
WHERE 
w1.State <> ‘Done’
AND w1.State <> ‘Deleted’
AND w1.State <> ‘Deferred’
AND w1.State <> ‘Complete’
AND w1.Title IS NOT NULL
ORDER BY w2.Iteration, w1.Title

---

In a follow up post,  Use Microsoft SRS To Automatically Send Open Tasks To TFS Users, I describe how to create the report using Microsoft SQL Server 2005’s SQL Server Business Intelligence Development Studio and how to schedule it.

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.

---

Download the executable

---

Imports System.IO
ImportsSystem.Xml
Imports System.TextModuleFormatXML    
     Sub Main(ByValArgs() As String)         
          DimstrFile As String
          DimstrFileNew As String          Try
          ‘ read in the file to convert from the command line
          IfArgs.Count = 1 Then
               strFile = Args(0)
               IfFile.Exists(strFile) Then
                    ‘ parse the file and create a new one
                    strFileNew = strFile & “-formatted.xml”
                    DimstrAlldData() As String= File.ReadAllLines(strFile)
                    DimstrData As String = String.Empty
                    DimstrNewData AsStringBuilder = NewStringBuilder                   
                     For EachstrData InstrAlldData
                         ‘ load the whole file into a single stringbuilder
                        strNewData.Append(strData)
                   Next                     ‘ creat an XML document
                    DimxmlDoc As NewXml.XmlDocument()
                     ‘ load the string
                    xmlDoc.LoadXml(strNewData.ToString)
                    ‘ normalize it
                    xmlDoc.Normalize()
                     ’save it back out
                     xmlDoc.Save(strFileNew)                    
                    Console.WriteLine(“Done. File saved as “& strFileNew)
               Else
                     Console.WriteLine(“Either the file “& strFile & ” does not exist or I do not have permissions to access it.”)
               End If
         Else
              Console.WriteLine(“To use this, please pass in the name of the file you want to parse.”)
         End If         Catch ex As Exception
            Console.WriteLine(“Error: “ & ex.Message)
        End Try
     End Sub
End Module

---

Using this code, an xml file that looked like “<TopLevel><MiddleLevel1><BottomLevel></BottomLevel></MiddleLevel1><MiddleLevel2></MiddleLevel2></TopLevel>” will be converted into

<TopLevel>
  <MiddleLevel1>
    <BottomLevel>
    </BottomLevel>
  </MiddleLevel1>
  <MiddleLevel2>
  </MiddleLevel2>
</TopLevel>

---

There are many useful features you could add to this code and make a nice product.  For example,

• Make a UI that allows the user to select a file for input and a file for output.
• Allow for multiple input files
• Provide progress feedback to the user.
• Allow scheduling
• Quickly email the results
• Have a preview window in the UI