Displaying Microsoft CRM Business Data in SharePoint Web Parts

Microsoft Corporation

February 2006

Applies to:
   Microsoft® CRM 3.0

Requires:
   Microsoft Windows® Server 2003
   Microsoft Windows SharePoint Services 2.0
   SharePoint Products and Technologies 2003 SDK
   SharePoint Products and Technologies Templates
   Microsoft Visual Studio® .NET 2003
   Microsoft SQL Server 2000 or SQL Server 2000 Desktop Engine (MSDE)
   The Microsoft Office System 2003
   Office 2003 Add-in: Web Parts and Components

Summary

Learn how to build portals and dashboards in SharePoint sites by using Web Parts that display Microsoft CRM data. This article accompanies a sample Visual Studio .NET solution that contains sample Web Parts for a Customer Service Portal. This article includes information about how to install and use Web Parts, and explains how to retrieve and display Microsoft CRM data in Web Parts.

The information in this article also applies to Microsoft Office SharePoint Portal Server 2003, which is built on the Windows SharePoint Services platform. The code samples can be used on sites created with SharePoint Portal Server.

It is assumed that you have basic development experience with Windows SharePoint services and the Web Part infrastructure. The article, A Developer's Introduction to Web Parts, explains the fundamentals of creating, deploying, and connecting Web Parts. In order to understand the Web Part programming examples in this article, you should have existing knowledge of programming Microsoft CRM through the Microsoft CRM SDK.

Download the sample code for this article: Sample Web Parts.

The download contains both a Microsoft Visual C#® and a Microsoft Visual Basic® .NET version of the sample application.

Contents

Overview
Building and Installing the Sample Web Parts
Adding the Web Parts to a Web Part Page
Connecting the Web Parts to your Microsoft CRM Server
Connecting the Longest Open Cases Web Part to the Case Details Web Part
Building Chart Web Parts for Microsoft CRM
Inside the Web Parts
Conclusion
Additional Information

Overview

This article describes how to use Windows SharePoint Services and Web Part technology to build portals and dashboards that display Microsoft CRM data in a single, unified interface. Managers can stay better informed about the health of their business unit and understand how they are performing with instant access to key performance indicators and critical metrics. Executives can keep their fingers on the pulse of the business with a single corporate scorecard that covers sales, marketing, and service. Users can save time by embedding frequently used charts or report segments on a single page. With up-to-the minute information, users can spot potential problems and identify opportunities early on.

Windows SharePoint Services and the Web Part infrastructure provide a simple and powerful framework for building portals and dashboards. With Windows SharePoint Services, you can build individual and team sites that have dashboards personalized for specific individuals or teams. Portals and dashboards can be securely shared between select people in your organization by setting appropriate site permissions. Row-level database security enables different users of the same dashboard to view only the records that they have view permissions on.

This article also explains how to use Microsoft Office Web Parts to chart Microsoft CRM data in SharePoint sites. Specifically, the article describes how to build a pie chart of open cases by product.

Figure 1. A Customer Service Portal with the sample Web Parts

Figure 1 depicts a Customer Service Portal page that contains the sample Web Parts as well as chart Web Parts built using the techniques described in this article.

These Web Parts display key pieces of information that target specific scenarios for a Customer Service department. The following table highlights the business questions that are answered by these Web Parts.

Building and Installing the Sample Web Parts

In order to install and deploy the sample Web Parts, you need administrator privileges on the server that is running Windows SharePoint Services. The Web Parts require network access to a Microsoft CRM 3.0 server.

The next sections talk about building and installing the sample Web Parts followed by a discussion of Code Access Security and how it relates to the Web Parts.

Building the Web Parts

The download contains a solution created in Microsoft Visual Studio .NET 2003. The sample solution, named ServicePortal, contains two projects. The first project, also named ServicePortal, outputs a Web control library named ServicePortal.dll, which includes the three Web Part controls. The second project, named ServicePortalCab, outputs a .cab file named ServicePortalCab.cab. You can use this .cab file to install the sample Web Parts on a server that is running Microsoft Windows Server 2003 and Windows SharePoint Services.

To build the ServicePortal solution, follow these steps:

1. In Visual Studio .NET, load the ServicePortal.sln solution file that is found in the download.
2. In Solution Explorer, right-click the ServicePortal project and select Add Web Reference from the menu.
3. Enter the URL of the Microsoft CRM Web service. For example:
4. http://<servername>/mscrmservices/2006/crmservice.asmx. Click Go.
5. Enter a name of "CrmSdk" for the Web reference and then click Add Reference.
6. In the Project menu, click Properties.
7. On the left column of the dialog box, click Configuration Properties, and then click Build.
8. In the right pane of the dialog box, under the Outputs category, enter the path of the SharePoint Web site's bin folder into the Output Path field. For the default Web site, this value would be as follows: C:\Inetpub\wwwroot\bin\.
9. Click OK.
10. On the Build menu, click Build Solution.

Installing the Web Parts

A file named ServicePortalCab.cab that was built in the previous section of this article is located in the ServicePortal\Debug folder of the sample. To install the Web Parts from this .cab file, use the stsadm.exe command-line tool located in the following directory on your SharePoint server: \Program Files\Common Files\Microsoft Shared\web server extensions\60\bin.

To install the Web Parts, follow these steps.

1. If you are not currently logged into a SharePoint server, log on to a SharePoint server using an account that has administrative rights.

2. Copy the ServicePortalCab.cab file to that server if it is not already there.

3. Open a Command Prompt window and execute the following command:

   stsadm.exe –o addwppack –url http://<server:port>/ –filename <path_ to_ServicePortalCab.cab>
   

   Where server is the SharePoint server name and port is the TCP port that the SharePoint virtual Web site is using. You can find the port number by running the Internet Information Services (IIS) Manager tool in the Administrative Tools menu or Control Panel.

When you have finished testing the sample, you can remove the sample Web Parts from SharePoint by executing the following command:

stsadm.exe -o deletewppack -name ServicePortalCab.cab -url http://<server:port>/

Code Access Security

You may have to adjust the security policy settings on your server that is running Windows SharePoint Services to enable the sample Microsoft CRM Web Parts to retrieve data from the Microsoft CRM server. For more information about how to do this, see the Code Access Security section in A Developer's Introduction to Web Parts.

If you have installed the sample Web Parts on a non-production SharePoint server and have administrative privileges on that server, you can temporarily grant full security access for testing purposes to the sample's Web Parts, and all other Web Part assemblies in the virtual Web site's bin folder, under ASP.NET. Full security access is also required to debug a Web Part in Visual Studio .NET. To grant full security access, follow these steps:

1. Log on to the SharePoint server using an account that has administrator privileges.

2. Edit the Web.config file for the virtual Web site where the sample Web Part assembly is to be installed. In the case of a default Web site, the Web.config file would be located in the <drive>:\Inetpub\wwwroot folder.

3. Locate the <system.web> XML tag in the file.

4. Below the <system.web> tag, find the following tag:

   <trust level="WSS_Minimal" originUrl="" />
   

5. Change the trust tag to be:

   <trust level="Full" originUrl="" />
   

6. Save the Web.config file.

7. Reset IIS by executing 'iisreset' at a command prompt.

When you are finished testing or debugging the sample, you can change the trust level back to the 'WSS_Minimal' setting.

Adding the Web Parts to a Web Part Page

The accompanying sample code solution contains three sample Web Parts for a Customer Service portal as described in the following table.

To add the sample Web Parts from the Virtual Server Gallery to a SharePoint page, follow these steps:

1. In Internet Explorer, open your SharePoint Web site page.
2. In the Modify Shared Page menu on the top of the page, point to Add Web Parts, and then click Browse.
3. The Task Pane opens up on the right side of the screen. Click Virtual Server Gallery to see the sample Web Parts you just installed, named Longest Open Cases, Case Details**,** and Expiring Contracts.
4. Drag the Web Part that you want to add from the Task Pane into a Web Part zone on the Web Part page. You can then close the Task Pane or select more Web Parts.

Figure 2 shows the sample Expiring Contracts Web Part being dragged onto a SharePoint page from the Virtual Server Gallery.

Figure 2. Adding the Expiring Contracts Web Part to a Web Part page

Connecting the Web Parts to your Microsoft CRM Server

After dropping the Web Parts onto a Web Part page, you have to connect them to your Microsoft CRM Server. You must be logged into a network account that has a license to access Microsoft CRM. To connect the Web Parts to Microsoft CRM, follow these steps:

1. On the SharePoint Web page, click the arrow in the title bar of the Expiring Contracts Web Part, and then click Modify Shared Web Part.
2. On the right side of the page, the Task Pane will open up and display the Web Part properties. Expand the Server category by clicking the plus sign (+) and enter your Microsoft CRM server name. Click Apply.
3. Repeat steps 1 and 2 for the other two Web Parts.

Figure 3 shows the task pane with the Microsoft CRM Server property.

Figure 3. Setting the Microsoft CRM Server property

Connecting the Longest Open Cases Web Part to the Case Details Web Part

To connect the Case Details Web Part to the Longest Open Cases Web Part, follow these steps:

1. Make sure that you have dragged and dropped both the Longest Open Cases Web Part as well as the Case Details Web Part into Web Part zone on the page.
2. Click the arrow in the title bar of the Case Details Web Part and then click Modify Shared Web Part.
3. The page will refresh in Design View. Again, click the arrow in the title bar of the Case Details Web Part. Point to Connections, point to Consumes a cell from, and then click Longest Open Cases.
4. Test the connection by selecting a case in the Longest Open Cases Web Part. The case details should be displayed in the Case Details Web Part.

Building Chart Web Parts for Microsoft CRM

Charts and pivot tables can be quickly created by using the Microsoft Office Web Parts. You can download these parts from here: Office 2003 Add-in: Web Parts and Components. In order to use these Web Parts, Microsoft Office 2003 must be installed on the server that is running Windows SharePoint Services as well as on the client machines. Follow the instructions to install and deploy these Web Parts on your server. You are then able to drag these Web Parts from your Virtual Server Gallery onto a Web Part page.

Next, you have to connect these Web Parts to your Microsoft CRM database.

1. After you drag the Web Part onto a Web Part page, click Connect to an external data source.

2. Select New SQL Server Connection and then click Open.

3. In the Data Connection Wizard, select Microsoft SQL Server and then click Next.

4. Type your Microsoft CRM server name. Leave the settings for Log on credentials with Use Windows Authentication selected. Click Next.

5. Select your Microsoft CRM Database from the available database list. It will have a name of the type: <organization>_MSCRM.

6. Under Connect to a specific table, select the filtered view that you want to access. For example, to create a chart that displays active cases by subject, select the FilteredIncident view.

   Note Always access Microsoft CRM data through the Filtered Views. Never access data in the entity tables directly.

7. Add a description for your data source and then click Finish.

8. You can now access the Microsoft CRM database from the Web Part. For example, if you had dragged a Pivot Chart Web Part, a Chart Field List window is displayed which lists the available database table fields. You can drag fields from the list onto the Pivot Chart Web Part.

If you require additional functionality beyond what the Office Web Parts provide, you can build Web Parts using controls provided by the Office Web Components. The Office Web Components offer a rich programming interface that can be used to build more interactive chart, spreadsheet, or pivot table Web Parts. For more information and code samples, see the Office Web Components VBA Language Reference.

Figure 4 shows a screenshot of a Marketing Dashboard with chart Web Parts created by using the Office Web Components programming interface. The Campaign Planning Tasks, Campaign Activity, Campaign Responses, and the Campaign Costs Web Parts are connected to the Active Campaigns Web Part. When the user selects a chart from the Active Campaigns Web Part, all the charts are refreshed to display data for the selected campaign.

Figure 4. A Marketing Dashboard created by using the techniques described in this article

You can also display the contents of a Microsoft CRM report in a Windows SharePoint Services site using the SQL Reporting Services Web Parts. These Web Parts were released as part of SQL Reporting Services Service Pack 2, which can be downloaded here. For instructions on how to install these Web Parts, ways to use them, and features that they support, see Using Reporting Services SharePoint Web Parts.

Inside the Web Parts

The next sections describe how to obtain business information from Microsoft CRM programmatically for display in the sample Web Parts. See the Microsoft CRM SDK for more information about the technologies mentioned in the following sections.

Accessing the Microsoft CRM Web Service

The Microsoft CRM SDK provides strongly typed access to all entities and their attributes in Microsoft CRM including custom entities. In order to access the Microsoft CRM Web service through the SDK, you must add a Web Reference to your project in the Visual Studio .NET Solution Explorer. This was done in the 'Building the Web Parts' section of this article.

Web methods can be invoked after you create an instance of the Web service. To access the Microsoft CRM Web service you must specify its URL.

CrmService service = new CrmService();
service.Url = "http://<servername>/mscrmservices/2006/crmservice.asmx";

In order to retrieve data from the Microsoft CRM Web service, you have to pass the credentials of the logged-in user to it.

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

The Microsoft CRM SDK documentation has a detailed explanation of the Microsoft CRM security model.

Retrieving Data from Microsoft CRM

There are three ways to retrieve Microsoft CRM data: QueryExpression, FetchXML, and filtered views. Some of the primary considerations when deciding which method to use are as follows: how the query is constructed, how the values are returned, and what functionality is supported.

The following table compares the features of QueryExpression, FetchXML, and filtered views.

Each of these ways of retrieving Microsoft CRM data is described briefly in the next sections.

QueryExpression

QueryExpression provides a strongly typed (object-oriented) approach to building queries. A QueryExpression object is used to build a database query that contains data filters programmatically. The QueryExpression is then used in a Microsoft CRM database search. The main limitation of QueryExpression is that it can return values from only one entity. A QueryExpression is always bound to a single business entity, and returns a collection of business entities of that type. The following code sample from the Expiring Contracts Web Part uses QueryExpression to retrieve all contracts that expire in the next 30 days.

// Create a column set that contains the names of the columns to be
// retrieved.
ColumnSet cols = new ColumnSet();
cols.Attributes = new string [] {"title", "customerid", "expireson"};

// Define search conditions to retrieve those contracts whose
// expiration date is within the next 30 days.
ConditionExpression condition = new ConditionExpression();
condition.AttributeName = "expireson";
condition.Operator = ConditionOperator.OnOrBefore;
condition.Values = new string []
           {DateTime.Now.AddDays(30).ToShortDateString()};

// Build the search filter based on the condition.
FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = new ConditionExpression[] {condition};

// Create a database query object that returns contracts.
QueryExpression query = new QueryExpression();
query.EntityName = EntityName.contract.ToString();
query.ColumnSet = cols;
query.Criteria = filter;

// Create the Web service request object.
RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();
retrieve.Query = query;

// Execute the Web service request.
RetrieveMultipleResponse retrieved = 
     (RetrieveMultipleResponse)service.Execute(retrieve);
entities = retrieved.BusinessEntityCollection;

FetchXML

FetchXML is an XML-based query language, which can be used to construct custom queries for Microsoft CRM. Unlike QueryExpression, FetchXML can be used to retrieve data from multiple entities. An important advantage of FetchXML is that FetchXML queries can be saved in Microsoft CRM and reused later. The query results are returned as XML, and therefore are not strongly typed. QueryExpression can be serialized into FetchXML, and FetchXML can be deserialized into a QueryExpression.

Filtered Views

You can also write queries in SQL directly against the filtered views in the Microsoft CRM database. Filtered views are fully compliant with the Microsoft CRM security model, so that users can only view data for which they have the appropriate permissions. Querying in SQL against the filtered views can be very powerful, but may require some knowledge about the Microsoft CRM database schema. Moreover, the filtered views do not incorporate the business logic in the platform. When retrieving data from the filtered views, you do not have to use the Microsoft CRM Web services.

For more information about QueryExpression, FetchXML, or filtered views, read the Microsoft CRM SDK documentation.

Creating Connectable Web Parts for Microsoft CRM

The Web Part infrastructure provides rich support for communication between Web Parts. Read A Developer's Introduction to Web Parts to learn the fundamentals of creating connectable Web Parts. If you know the type of Microsoft CRM entity that has to be exchanged between two Microsoft CRM Web Parts, the simplest way to connect the two Web Parts is to pass the GUID of the entity that has to be exchanged. This is the method adopted to exchange the case identifier between the Longest Open Cases Web Part and the Case Details Web Part. The Longest Open Cases Web Part implements the ICellProvider interface. It contains a DataGrid control that is bound to a DataView that contains the following attributes of the case: Title, Priority, and Date of Creation. This data grid also contains a hidden column that contains the incident ID of the case. The incident ID of the selected case is passed on to the Case Details Web Part in the PartCommunicationMain method.

// Pass the IncidentId of the selected case (column 4).
cellReadyArgs.Cell = CasesGrid.SelectedItem.Cells[4].Text;

The Case Details Web Part implements the ICellConsumer interface. It has a custom property called IncidentId. In the CellReady event handler, the IncidentId property is set to the value passed from the ICellProvider interface of the Longest Open Cases Web Part. The Case Details Web Part then uses a QueryExpression object to retrieve the details of the selected case.

public void CellReady(object sender, CellReadyEventArgs cellReadyArgs)
{
   if(cellReadyArgs.Cell != null)
   {
      // Set the part's IncidentId property.
      IncidentId = cellReadyArgs.Cell.ToString();

      // Call the RetrieveCrmData method, which uses the new 
            // incident ID to display the case details.
      RetrieveCrmData();
      }
      }
}

Conclusion

Windows SharePoint services and the Web Part infrastructure provide a platform that can be used to build compelling SharePoint portals and dashboards with Web Parts that display Microsoft CRM data. The sample Web Parts provided with this article answer key business questions for a Customer Service department.

This article explained how to connect to the Microsoft CRM Web services and securely retrieve data using three different methods: QueryExpression, FetchXML, and filtered views. In order to chart Microsoft CRM data in Web Parts, you can either configure the Microsoft Office Web Parts or build your own Web Parts using the Office Web Components programming interface. The SQL Reporting Services Web parts let you display Microsoft CRM reports in SharePoint sites. By using the sample Web Parts and the techniques covered in this article, you can build portals and dashboards that capture the key metrics and information vital to your organization.

Additional Information

This section lists sources of additional information about topics discussed in this article.

• For more information about programming Microsoft CRM 3.0, see the online SDK.
• For more information about developing SharePoint Web Parts, see these online articles:
  • A Developer's Introduction to Web Parts
  • Packaging and Deploying Web Parts
  • Using Reporting Services SharePoint Web Parts
  • Using Code Access Security with ASP.NET
  • Microsoft Windows SharePoint Services and Code Access Security