Posts Tagged ‘Application Integration’

Generate Excel 2007 Sheets from MSCRM Data – OpenXML Show

Office Open XML is an XML-based file format specification for electronic documents such as memos, reports, books, spreadsheets, charts, presentations and word processing documents. The specification has been developed by Microsoft as a successor of its binary office file formats and was published by Ecma International as the Ecma 376 standard in December 2006. The format specification is available for free at Ecma International. 

Let me introduce you the technique I have used to generate the Excel 2007 sheet from CRM data. I have downloaded an Excel package. ExcelPackage provides server-side generation of Excel 2007 spreadsheets. See for details. 

Specifying Output Directory and Template Directory

 Output directory is where you place the generated excel file and Template  directory is where you place the template file. Template file is simply a .xlsx file without data. Use following code to specify both directories. 

DirectoryInfo outputDir = new DirectoryInfo(@”GenerateExceloutput”);
DirectoryInfo templateDir = new DirectoryInfo(@”GenerateExceltemplates”); 
FileInfo newFile = new FileInfo(outputDir.FullName + @”File-” + strRecordID + “.xlsx”);
FileInfo template = new FileInfo(templateDir.FullName + @”TempReport.xlsx”);
if (!template.Exists)         
throw new Exception(“Template file does not exist! i.e. template.xlsx”);

Write data to Excel File 

ExcelPackage contains library functions for accessing worksheet, cells etc. For details please have a look into the Excel package code. Uses following code to write data to individual cell in excel worksheet. 

using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))     

ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[“Sheet1”];
if (worksheet != null)
worksheet.Cell(2, 9).Value = ClearText(strData);                   
catch (Exception ex)

Adding Rows to Sheet          


Return Excel File for Download 

Simply redirect the response object in ASP.NET to the excel file generated. 

Response.Redirect(“http://” + Request.Url.Host + “:” + Request.Url.Port + “/” + “GenerateExcel/output/” + newFile.Name); 

All cell styles, formates and pictures objects that you set once in Template will remain the same in the generated file. 

Please find below my sample quote geneation application. I have placed a button at Quotes toolbar named Generate Quote. It will generate quote report in excel 2007 with the Quote data from crm currently selected.


My program fetches data from MSCRM through web services and populated my Excel sheet. It then returns my generated excel sheet with options to open or download.



MSCRM and MapPoint Integration – A picture is worth a thousand words.

Microsoft Provides MapPoint Web Service to enrich application with mapping functionality. It allows you to integrate maps, driving directions, order tracking and proximity searches into a wide range of solutions.

For those of you who want to start using MapPoint for test and development purposes you can sign up for a free developer account here:


With a developer account, you get full access to the MapPoint Web Service APIs and staging environment, which you can use to build applications for trial, demonstration, and proof-of-concept purposes.



Microsoft Dynamics CRM Account entity already has longitude and latitude parameters for address. But mostly organizations don’t use those parameters. MapPoint web service provides FindAddress service to resolve addresses and returns longitude and latitude parameter as shown in the code snippet below. 

Find Address Longitude and Latitude FindServiceSoap FindService = new FindServiceSoap ();
FindService.Credentials = new NetworkCredential(“”, “”);
FindService.PreAuthenticate = true;
MyAddress mAddress = new MyAddress();

mAddress = (MyAddress)addresses[i];

FindAddressSpecification spec = new FindAddressSpecification();
spec.InputAddress = new Address();
spec.InputAddress.AddressLine = mAddress.Line1;
spec.InputAddress.CountryRegion = mAddress.Country;
spec.InputAddress.Subdivision = mAddress.StateProvince;
spec.InputAddress.PrimaryCity = mAddress.City;
spec.InputAddress.PostalCode = mAddress.Postalcode;
spec.DataSourceName = “MapPoint.NA”;
FindResults results = FindService.FindAddress(spec);

Populating Location and Pushpin Array 

Location and pushpins objects require longitude and latitude values for address. Following code is used to populate Location and Pushpin objects of MapPoint API. 

Location[] myLocation = new Location[addresses.Count];
myLocation[i] = new Location();
myLocation[i].LatLong = new LatLong();
myLocation[i].LatLong = results.Results[0].FoundLocation.LatLong;
Pushpin[] pushpins = new Pushpin [addresses.Count]; pushpins[i] = new Pushpin();
pushpins[i].PinID = “pin0”;
pushpins[i].IconName = “0”;
pushpins[i].Label = mAddress.Name;
pushpins[i].IconDataSource = “MapPoint.Icons”;
pushpins[i].LatLong = results.Results[0].FoundLocation.LatLong;
There is no simple way to provide hyperlink at Pushpins, although Pushpin label and icon can be set to custom settings. Getting image and automatic zoom based on the geography covered by the addresses

MapPoint Web Service provides Render Service API to automatic zoom based on the geography covered by addresses. Here is the code snippet to do this:

//Call MapPoint Render Web Service RenderServiceSoap RenderService = new RenderServiceSoap();
RenderService.Credentials = new NetworkCredential(“”, “”);
MapViewRepresentations mvRep = RenderService.GetBestMapView(myLocation,”MapPoint.NA”);
mviews[0] = new ViewByBoundingRectangle();
mviews[0] = mvRep.ByBoundingRectangle;
MapSpecification mspec = new MapSpecification();
mspec.Options = moptions;
mspec.Views = mviews;
mspec.Pushpins = pushpins;
mspec.DataSourceName = “MapPoint.NA”;

MapImage[] image = RenderService.GetMap(mspec);

So the final image can be displayed in some ASP.NET page to show in Microsoft Dynamics CRM. Please find below my integration of MapPoint web service with MSCRM. One can select Accounts to be plotted on MapPoint and click Show Accounts Map Button at Account Entity grid toolbar.  All selected accounts will be plotted on the MapPoint in a new web dialog with Invalid addresses at the bottom.  

Step 1 

 Here is the final Image generated in Web Dialog:
Step 2 For any comments or suggestion do let me know. Your suggestions and comments are valuable for me. Thanks!

MS CRM and SharePoint 2007 Integration

Here is a very nice article by one of my colleagues,  Rehman Gul, on MS CRM and MOSS 2007 integration. I thought to share this with all of you….you can also find the article on his blog @ 

SharePoint 2007 provides integration with quite a few Microsoft products including Dynamics, SQL Server Reporting Services, PerformancePoint, etc. Today, we will specifically talk about SharePoint’s integration with MS CRM. Here is the press release from Microsoft announcing the ability of SharePoint to provide tight integration with MS CRM environment:

Lets have a walkthrough of how can we connect with MS CRM using SharePoint’s Business Data Catalog (BDC) and Web Parts.

First of all we need an XML definition file to connect with CRM Database. Writing these files is difficult and error prone. You can easily find tools over the internet that will provide you with the ability to create Application Definitions for MOSS 2007. One such tool could be found here.

Here is the file I am going to use for the purpose of this exercise. This sample metadata XML defines various entities for MS CRM 3.0 database. If you have downloaded Dynamics Snap-ins, you can get one such file already present on your disk. If you intend to use the file that I am using, remember to change the Server and Database Names in it. They have been marked within the file…changes are required at the line 27 and 29.First of all we need to import this definition file to MOSS (Microsoft Office SharePoint Server) 2007.

Go to central Administration. It is usually created at the port 12779. The Url should look something like this:




On the left Navigation Column click “SharedServices1” link. If its not there, you can create one clicking “Shared Services Administration” link on the left navigation column. “SharedServices1” site should look like this:


Under “Business Data Catalog” click “Import application definition”. Following screen appears:


Browse and select the definition file from your disk. Keep the default settings and click “Import”. This will initiate the upload process and take some time.


After the file has been uploaded you can view the file clicking “View applications” under “Business Data Catalog”.


Now go to the site where you want to provide CRM connectivity. Click “ Site Actions” and “Edit Page”.


Click “Add a Web Part”.


From the list select “Business Data List” web part and click “Add”.


Click “open the tool pane” link in the “Business Data List” web part.


Click “Browse” icon right next to the “Type” text box displayed on the right top.


Select any “Business Data Type” and click “Ok”. 


Click “Ok” at the bottom of the options displayed in the right column. 

untitled12b.jpg Here is what you should see:

Just add the value in the empty text box that you want to fetch from the CRM Database and click “Retrieve Data”. You are connected to MS CRM through your MOSS 2007.Hope you enjoyed this post….I’ll continue on SharePoint’s integration with other Microsoft Products….hope to see you again….it’s bye for now……:)

Snap-ins for Microsoft Dynamics CRM 3.0

Snap-in applications enable you and your people to work within the familiar environments of Microsoft Office applications, such as Microsoft Office Outlook, Word, Excel, and SharePoint Server. Microsoft Dynamics Snap is a collection of products for Microsoft Office 2003/2007 users to work with certain Microsoft Dynamics applications. Snap-ins allow Microsoft Office users to interact with data and business processes from Microsoft Dynamics applications as they create documents, collaborate and manage their calendar—all without having to leave Microsoft Office.  The following Microsoft Dynamics Snap for Microsoft Dynamics CRM 3.0 applications are available today in Technical Pre-release and are distributed under the Microsoft Permissive License.  Microsoft partners can easily extend and customize the applications, and even use components within applications they’ve developed. 

  • Business Data Search
  • Business Data Lookup
  • Custom Report Generator
  • Customer Journal

For more information, please look at: 

MSCRM – External Connector License

Integrating your CRM system with external world is a tricky business.  For example if you need to gather customer information from your website and populate in your CRM system as leads you need an external connector license. This License is required to view, manipulate and query data by non-CRM user. 

If you want to share Microsoft CRM data with external users such as your customers or partners, you can purchase an External Connector License that allows you to share Microsoft CRM data with an unlimited number of third-party users and systems.  

By using the External Connector License, you do not need to purchase a user license (create an Active Directory Account) for each external user. For example, you can create and external Web Site that lets customer log on and retrieve Microsoft CRM data real time. You could also create a special Web site for your partners to enter and update Microsoft CRM data. You need and External Connector license for every server required to support external access.

For more information, please look into this post: