CRM, Performance Point and MOSS (CRM+PPS+MOSS)

Jonas Deibe - Dynamics CRM, C#, JS

---

Extending CRM with BI capacity has been on the radar for a while and with the new BI accelerators this will be an easy customization. The same applies to CRM and MOSS integrations. Since I have spent some time in this very interesting area I tough why not share some parts (screen captures). My goals was to build an application with Sales support (CRM) extend it with document management/collaboration (WSS/MOSS) and analyses, drilldown reporting plus dashboards (PPS)


The end application would be fully integrated and user navigation will be from the CRM client. Since its all installed on-premise authentication is single sign on (SSO). One very important goal is to let the end user not to know what underlying product she is using, it just doesn't matter as long it works and supports the end-users business/processes.


The first step to do is to install the software. I use two servers and a client in my lab.


• Installation domain controller and Exchange (Server1)


• Installation SQL Server, AS for OLAP's, Reporting Service (default port 80), CRM server (port 5555), MOSS Server (random port NOT default web 80), Performance Point Server (Monitoring), Visual studio (Server2)


• Installation of Client with Office package, Visual Studio (Client)


The installation process might take some time so don't expect to install it all on an afternoon.


Details on how-to configure each product is not in scope of the blogs post (might be a later post)


The end result is a very powerfull application; below you see some screen shoots





CRM Webclient, Sharepoint site and PPS webparts rendering Dashboards from OLAP cube

Sharepoint Document Library. Context menu about to open workflows on current document.

Drill down to product from opportunities, all depending how the cube has been designed

PerformancePoint Monitoring SDK


http://msdn.microsoft.com/en-us/library/bb848116.aspx

Working with Online Analytical Processing (OLAP)


http://msdn.microsoft.com/en-us/library/ms175367.aspx

MOSS Developer center


http://msdn.microsoft.com/en-us/office/aa905503.aspx


CRM 4.0 sdk


http://msdn.microsoft.com/en-us/library/aa477293.aspx

Published Tuesday, October 21, 2008 10:00 PM by Jonas Deibe

List Web Part for Microsoft Dynamics CRM 4.0 Deployment Scenarios

Kamal Sharma Published Thursday, January 29, 2009 9:53 AM

---

List Web Part for Microsoft Dynamics CRM 4.0 supports Internet Facing Deployment (IFD) and On Premise configuration for Dynamics CRM and SharePoint.

This article briefly describes the steps necessary for configuring List Web Part determined by the deployment scenarios of Dynamics CRM and SharePoint Server.

NOTE: Dynamics CRM 4.0 Server allows IFD + On Premise Authentication mechanism to be configured for Internet Facing deployments. User’s IP address determines the authentication mechanism used to access Dynamics CRM Server (On Premise or IFD). In contrast, SharePoint's Internet Facing website is based on the URL used to access it.

The List Web Part for Microsoft Dynamics CRM 4.0 is supported on following versions of Microsoft CRM and SharePoint:

• Microsoft Dynamics CRM 4.0.
• Windows SharePoint Services 3.0 SP1 or Microsoft Office SharePoint Server 2007 SP1 must be installed.

The different scenarios are represented by a matrix below:

SharePoint

CRM

On Premise

Internet-Facing

On Premise

· Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is always in the same intranet: Supported [ Scenario 1 ]

· Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is accessing via Extranet: Not Supported as Dynamics CRM 4.0 Server should directly be accessed by List Web Part User.

On Premise + IFD

· Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is always in the same intranet: Supported [ Scenario 1 ]

· Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is in the same zone as SharePoint Server: Supported

[ Scenario 4 ]

· Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is in the same Intranet or is accessing via Extranet: Supported [ Scenario 2]

· Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is in the same intranet or Extranet with respect to Dynamics CRM 4.0 Server : Supported [ Scenario 3 ]

· Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is in the same intranet or Extranet with respect to SharePoint Server: Supported

[ Scenario 4 ]

Scenarios

In this section, we describe the various scenarios for List Web Part.

1. Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is always On Premise

In this case, we typically assume an organization where the users have access to Dynamics CRM and SharePoint Servers while they are in the intranet of the company. Figure 1 shows the following setup.

Figure 1. Intranet Setup

Prerequisites:

1. Dynamics CRM 4.0 and SharePoint Servers configured as On Premise.

2. List Web Part User through his IE browser can access SharePoint and Dynamics CRM 4.0 Servers from the Intranet.

3. Ensure that Trust for Delegation is set between SharePoint Server and Dynamics CRM Servers.

Troubleshooting: User is unable to configure List Web Part to access On Premise Dynamics CRM Server, instead User's IFD Credential Screen is popped up by List Web Part as shown in Figure 2.

Figure 2. List Web Part IFD Login pane

Resolution:

In this case, the SharePoint server does not receive a valid CRM ticket from Dynamics CRM 4.0 Server and thus displays an IFD Authenticate page. This happens when Trust for Delegation is not setup between the SharePoint Server and Dynamics CRM 4.0 Server. Consequently, the Dynamics CRM 4.0 Server denies the Authentication request as it does not receive valid Active Directory Credentials from SharePoint.

For resolving this issue, Please setup Trust for Delegation between Dynamics CRM 4.0 Server and SharePoint Server as mentioned in the following blog:

http://blogs.msdn.com/crm/archive/2009/01/14/trust-for-delegation-in-list-web-part-for-microsoft-dynamics-crm-4-0.aspx

2. Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is either On Premise or is accessing via Extranet.

In this particular scenario, the List Web Part User is a traveling person who tries to access data from within the Intranet zone as well as from Extranet. The Dynamics CRM 4.0 and SharePoint Server are hosted within the organization. Figure 3 demonstrates this functionality.

Figure 3. List Web Part User accessing remotely as well as in intranet.

Prerequisites:

1. Dynamics CRM 4.0 Server configured as IFD for SharePoint Server. This is necessary because SharePoint Server has no way to determine the List Web Part user's AD credentials when he/she is in the extranet zone.

For configuring this setup, specify the subnet mask to use as 255.255.255.255 in the CRM 4.0 IFD Configuration Tool when Dynamics CRM is configured as IFD + On Premise as shown in Figure 4. For more details, please look at the Additional Configuration steps when SharePoint and Microsoft Dynamics CRM are both IFD Section of List Web Part Readme Documentation.

Figure 4. IFD Configuration Tool

2. SharePoint server setup in IFD mode. ( Expect a blog soon on this topic )

3. User must configure List Web Part using Dynamics CRM IFD URL from within the intranet and externally.

4. List Web Part User can access SharePoint Server and Dynamics CRM 4.0 Server directly from his IE browser.

Troubleshooting: Users are unable to connect to Dynamics CRM 4.0 IFD Server from List Web Part. In this case, users would see the following message being popped up on their screen shown in Figure 5.

Figure 5. Dynamics CRM Connection Error Dialog

Resolution: For resolving this issue in the scenario, please check if one can access Dynamics CRM 4.0 Server from the SharePoint Machine through an IE browser. The Dynamics CRM Server should be asking user credentials as opposed to using Active Directory Credentials to log in.

For a connection issue, please make sure that an appropriate hosts entry or DNS Setting is made.

Host entry can be made by editing the hosts file in %windir%\system32\drivers\etc and adding the Dynamics CRM IP Address and Hostname. For e.g.

2. Users are unable to see icons displayed in the List Web Part on IE Browser. Screenshot in Figure 6 shows this behaviour.

Figure 6. Images incorrectly rendered

Resolution: This issue arises when the List Web Part User is unable to access the Dynamics CRM 4.0 IFD Server Machine from his own machine. For verifying this, just try accessing the Dynamics CRM Server from IE browser directly. Also, ensure that appropriate hosts entry or DNS Settings are made from the computer which is connecting to the Dynamics CRM Machine. In order to add the hosts entry, see the details in the above step.

3. Users not able to connect to SharePoint Internet Facing site.

Resolution: The cause of this problem is most likely a connection issue to SharePoint. Please check if there is a hosts entry for SharePoint Server on the Users machine.

3. Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is either On Premise or Extranet with respect to Dynamics CRM Server.

Here, the Dynamics CRM Server and the List Web Part User are part of the same organization. The List Web Part User is a travelling person who tries to access List Web Part internally as well as externally. SharePoint is hosted as an external server. Figure 7 outlines this scenario. This is similar to the scenario 2, however, here the users may move in and out of the Dynamics CRM Server Intranet.

Figure 7. List Web Part User in same organization as Dynamics CRM Server

Prerequisites:

1. Dynamics CRM 4.0 Server and SharePoint Server configured in IFD mode.

2. List Web Part User can access SharePoint Server and Dynamics CRM Server directly from his IE browser.

3. User configures the List Web Part using Dynamics CRM IFD URL from within the domain and externally.

Troubleshooting: User is unable to connect through List Web Part when he/she is with the intranet domain of Dynamics CRM.

Resolution:

Please ensure that one is using Dynamics CRM IFD URL during configuration and not the usual On Premise URL. This is due to the fact that Dynamics CRM Server always appear as IFD to SharePoint Machine where List Web Part is installed.

Please look at Troubleshooting steps of Scenario 2, for other problems.

4. Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is either On Premise or Extranet with respect to SharePoint Server.

Figure 8 shows the illustration of this scenario. The List Web Part User is a travelling person which belongs to the same organization as the SharePoint Server. Dynamics CRM Server is hosted externally to the organization.

Figure 8. List Web Part User in same intranet as the SharePoint Server

Prerequisites:

1. Dynamics CRM 4.0 Server and SharePoint Server configured in IFD mode.

2. List Web Part User can access SharePoint Server and Dynamics CRM Server directly from his IE browser.

3. User configures the List Web Part using Dynamics CRM IFD URL.

For other issues and resolution, please check out the Troubleshooting steps mentioned in Scenario 2.

Useful Links

a) Microsoft Dynamics CRM 4.0 IFD Tool Download, http://www.microsoft.com/downloads/details.aspx?FamilyID=69089514-6e5a-47e1-928b-4e4d4a8541c0&displaylang=en

b) Using Microsoft Dynamics CRM 4.0 IFD Tool, http://support.microsoft.com/kb/948779

c) Host File Troubleshooting, http://technet.microsoft.com/en-us/library/bb727005.aspx

Cheers,

Kamal Sharma

Hooray!! PerformancePoint Server is merging with SharePoint

I just read a posting from Microsoft and in the Summer of 2009 we should see a nice addition to SharePoint called PerformancePoint Services.

 

"Microsoft's strategy is to bring BI to the masses through the familiar, widely used tools of SharePoint and Excel, while building on the highly scalable SQL Server platform. Based on an in-depth product roadmap review and customer feedback, we've decided to consolidate PerformancePoint Server into SharePoint Server, making the widely used scorecard, dashboard and analytical capabilities available to everyone in the organisation."

Trust for Delegation in List Web Part for Microsoft Dynamics CRM 4.0

Suraj Supekar wrote this

---

This article pertains to Trust for Delegation issues encountered in on-premise installations of Microsoft Dynamics CRM 4.0 (MS CRM) when CRM server and SharePoint Server exist on different physical machines. If you have List Web Part (LWP) deployed for IFD version of MS CRM, or both Microsoft Dynamics CRM and SharePoint Server are on same machine then your deployment is not affected by the trust for delegations issue.

In scenarios, where MS CRM on-premise and SharePoint are setup on separate machines, Microsoft Dynamics users of LWP face issues during authentication. If the SharePoint Server is not setup for Trust for Delegation then the user's Active Directory credentials are not passed to the MS CRM server. The LWP deployed on SharePoint does not receive the CRM authentication ticket from SharePoint and displays the sign on form used with an IFD installation. The screen below shows the configuration pane of LWP and sign on form. This form appears when a Trust For Delegation ( also known as Double-Hop impersonation ) is not present.

Figure 1 : IFD login from configuration pane

What is Double Hop issue?

In situations where SharePoint Server and MS CRM server are on different machines, the first hop is from the LWP user’s IE browser to the SharePoint server, and then from the SharePoint server to the MS CRM Server. This is the second hop. Windows credentials cannot be passed in second hop, due to security issues. To enable the SharePoint Server to pass the user credentials, the SharePoint server must be configured for Trust for Delegation.

Setting up 'Trust for Delegation'

To make it easier to understand the configuration settings, consider the following topology:

• Machine #1 Active Directory
• Machine #2 SQL Server
• Machine #3 Microsoft Dynamics CRM 4.0 Server
• Machine #4 Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007
• Machine #5 User accessing SP using IE

Figure 2: Independent CRM and SharePoint Server topology

1. First, configure IIS and IE for delegation using the steps in following KB Article http://support.microsoft.com/default.aspx?scid=kb;en-us;810572

Note: To perform remaining steps , the user must be a member of the Domain Adminstrators group or the Enterprise Adminstrators group in Active Directory, or user must have been delegated the appropriate authority.

As a security best practice, consider using Run as to perform this procedure.

2. Click Start >> Control Panel >> Administrative Tools >> Active Directory Users and Computers.

3. In the console tree, click Computers.

4. In the details pane, right-click the computer you want to trust for delegation and then click Properties. In our case its Windows SharePoint Services 3.0 server or MOSS 2007 server (machine # 4 in figure 2) .

5. On the Delegation tab, click Trust this computer for delegation to specified services only.

Figure 3 : Trust for delegation to specific service

6. Depending upon the IIS authentication type in WSS/MOSS Web application, do one of the following:

• If IIS authentication type is NTLM , Click Use any authentication protocol .

OR

• If IIS authentication Setting is Integrated Windows authentication with Negotiate (Kerberos), click Use Kerberos only ( see figure 7 ).

7. Click Add and, in Add Services, click Users and Computers.

8. In Enter the object names to select (examples), type the name of the computer that the computer will be trusted to delegate for example, Dynamics CRM 4.0 computer (Server no 3 in figure 2) , and then click OK.

Figure 4 : Select User and Computers

If the machine name does not resolve, Click Advanced

• After opening Select Users or Computer dialog , click Find Now
• Select CRM server computer from list and then click OK. In Select Users or Computer dialog , CRM server machine name will appear, Click OK.

Figure 5 : Select User and Computers using advanced dialog

9. In Add Services, click the Http service that will be trusted for delegation and click OK.

Figure 6 : Set trust for specified service

Notes

• If you cannot see the Delegation tab as shown in Figure 3, do one or both of the following:

• Register a Service Principal Name (SPN) for the computer account using the Setspn utility in the support tools that are on your CD. Delegation is only intended to be used by service accounts, which should have registered SPNs, as opposed to a regular user account which typically does not have SPNs.
• Raise the functional level of your domain to Windows Server 2003 .

• Constrained delegation, delegation of authentication for only specified services, can only be enabled on a member of the Windows Server 2003 family.

The following steps are necessary if you want to use Kerberos in WSS/MOSS.

10. In SharePoint Central administrator site, In Application Management, Select Authentication Providers

11. In Authentication Provider select Window Membership Provider from default zone and Check IIS Authentication Settings.

a. Integrated Windows authentication check box should be selected

b. Select Negotiate (Kerberos) option

Figure 7 : SharePoint Central Admin - Edit Authentication

You should now be able to login to List Web Part and view the configuration page.

Figure 8 : Successful Login in List Web Part

Cheers,

Suraj Supekar

CRM 4 Web Part for Sharepoint Available

Aaron Elder Posted

---

Download it here

The List Web Part for Microsoft Dynamics CRM 4.0 provides a way to view and update Microsoft Dynamics CRM records using a Windows SharePoint Services 3.0 SP1 or Microsoft Office SharePoint Server 2007 SP1 Web site. Microsoft Dynamics CRM users can create shared or personal List Web Parts of Microsoft Dynamics CRM records from a SharePoint Web site, open records in Microsoft Dynamics CRM 4.0 from the List Web Part, and create connected List Web Parts.

Install was just an MSI.  After I just had to deploy the web part through Central Administration.  It was very easy and took about 10 minutes.

Here is shot of configuring the web part once it’s added to a site:

And here is the final output with the top being a list of My Activities and the bottom being a list of Customers (Accounts)

posted at: 7:17 AM by Ross Lotharius

The List Web Part for Microsoft Dynamics CRM 4.0

The List Web Part for Microsoft Dynamics CRM 4.0
Overview
The List Web Part for Microsoft Dynamics CRM 4.0 provides a way to view and update Microsoft Dynamics CRM records using a Windows SharePoint Services 3.0 SP1 or Microsoft Office SharePoint Server 2007 SP1 Web site. Microsoft Dynamics CRM users can create shared or personal List Web Parts of Microsoft Dynamics CRM records from a SharePoint Web site, open records in Microsoft Dynamics CRM 4.0 from the List Web Part, and create connected List Web Parts.

System Requirements

• Supported Operating Systems: Windows Server 2003; Windows Server 2008
  Microsoft Dynamics® CRM 4.0
• Microsoft Windows® SharePoint® Services 3.0 SP1
• Microsoft® Office SharePoint® Server (MOSS) 2007 SP1
• Microsoft® Internet Explorer 6 with SP1 or later versions

Instructions

You must have local administrator privileges on the server where you plan to install the List Web Part for Microsoft Dynamics CRM 4.0. You must also have administrator privileges for the SharePoint Services server. The List Web Part for Microsoft Dynamics CRM 4.0 must run by using full trust mode.Click the ListWebPart.msi file to run the List Web Part for Microsoft Dynamics CRM 4.0 Setup wizard. If you are using Windows Server 2008, install the List Web Part by using the command prompt instructions found in the documentation.

For more information, see the documentation: List Web Part for Microsoft Dynamics CRM 4.0 Administration and Deployment Guide.

CRM, Performance Point and MOSS (CRM+PPS+MOSS)

Published Tuesday, October 21, 2008 10:00 PM by Jonas Deibe

---

Extending CRM with BI capacity has been on the radar for a while and with the new BI accelerators this will be an easy customization. The same applies to CRM and MOSS integrations. Since I have spent some time in this very interesting area I tough why not share some parts (screen captures). My goals was to build an application with Sales support (CRM) extend it with document management/collaboration (WSS/MOSS) and analyses, drilldown reporting plus dashboards (PPS)
The end application would be fully integrated and user navigation will be from the CRM client. Since its all installed on-premise authentication is single sign on (SSO). One very important goal is to let the end user not to know what underlying product she is using, it just doesn’t matter as long it works and supports the end-users business/processes.

The first step to do is to install the software. I use two servers and a client in my lab.
• Installation domain controller and Exchange (Server1)
• Installation SQL Server, AS for OLAP’s, Reporting Service (default port 80), CRM server (port 5555), MOSS Server (random port NOT default web 80), Performance Point Server (Monitoring), Visual studio (Server2)
• Installation of Client with Office package, Visual Studio (Client)

The installation process might take some time so don’t expect to install it all on an afternoon.
Details on how-to configure each product is not in scope of the blogs post (might be a later post)

The end result is a very powerfull application; below you see some screen shoots


CRM Webclient, Sharepoint site and PPS webparts rendering Dashboards from OLAP cube

Sharepoint Document Library. Context menu about to open workflows on current document.

Drill down to product from opportunities, all depending how the cube has been designed

PerformancePoint Monitoring SDK
http://msdn.microsoft.com/en-us/library/bb848116.aspx

Working with Online Analytical Processing (OLAP)
http://msdn.microsoft.com/en-us/library/ms175367.aspx

MOSS Developer center
http://msdn.microsoft.com/en-us/office/aa905503.aspx

CRM 4.0 sdk
http://msdn.microsoft.com/en-us/library/aa477293.aspx

Business Data Catalogue (BDC) and Microsoft Dynamics CRM

Manbhawan Prasad Published Tuesday, October 07, 2008 9:10

---

Business Data Catalogue (BDC) is one of the very exciting features of SharePoint. It got introduced in SharePoint 2007. This gives a declarative way to connect SharePoint to business applications such as standard ERPs, CRM, home grown apps etc.

It supports direct DB access as well as access through Web services. You can find good details at MSDN site (http://msdn.microsoft.com/en-us/library/ms551230.aspx). Ramping up to know BDC might be little work initially. One of the good way to get started is playing with samples provided at http://www.microsoft.com/downloads/details.aspx?familyid=6d94e307-67d9-41ac-b2d6-0074d6286fa9&displaylang=en.

If you decide to use BDC for your organization. You can follow the guidelines below.

1. Prepare BDC metadata (XML) which contains following information about back end system in a defined format:

a. Connectivity details

b. Entity details

c. Details around different entity methods/APIs with arguments & return types

2. You can use the tool developed by SharePoint to write these definitions. More details are at http://blogs.msdn.com/sharepoint/archive/2007/08/22/announcing-the-microsoft-business-data-catalog-definition-editor-for-microsoft-office-sharepoint-server-2007.aspx.

3. Import the BDC metadata file into SharePoint

a. You can find the place to do this at Shared Services in SharePoint central administration

b. It might take few cycles to get all the errors corrected here

And you are done with letting SharePoint know the details which it will use to connect to your LOB (line of business)/back end application. Many capabilities can be surfaced using BDC. You can get more details at http://blogs.msdn.com/sharepoint/archive/2006/04/18/business-data-catalog.aspx.

IMHO, one of the biggest advantage BDC provides is a great help in surfacing a common platform for the users, where they are dealing with different applications/data sources. For example lets assume that an organization uses MSCRM to deal with a set of data, and other App to deal with some other set of data. Many a times data stored in these two different App ends up having relationships with each other. In this case, it becomes very painful for user to switch applications for getting their daily work done. BDC comes here for rescue and provides a common platform with some nice out of box Web parts using that one can perform activities right in SharePoint without changing the context & applications.

In addition to this, it provides a strong programming environment through its object model. Using this one can very easily build applications which talks to different LOB apps. Guess what, developer does not need to learn different back end applications APIs & schemas. They just need to know BDC object model, and they can easily develop common platform talking to disparate systems.

If prudently used, it can prove to be a magic for end users, where they will love navigating to different types of records from different back end systems (related & unrelated) so smoothly as if data is coming from one source system. Enterprise search using BDC becomes even more stronger here, as searching in data coming from disparate systems become so cheap to own.

Because of the reasons above, importance of building BDC metadata for MSCRM cannot be undermined. And so there have been various efforts toward this. Some important ones are as follows:

MSCRM V3 – http://blogs.microsoft.co.il/files/folders/9188/download.aspx. This is a link to the BDC metadata file which can be a good starting point for building your own BDC metadata for MSCRM. Pls note that this might not work ‘as is’ for your implementation of MSCRM.

MSCRM V4.0 – http://blogs.msdn.com/crm/archive/2008/08/26/crm-accelerators-part-iv-enterprise-search-accelerator.aspx

While implementing BDC for the organization, one more thing we should keep in our mind is to define a good process around getting BDC metadata definitions changed if source system goes thorough some customizations like new attribute creations, relationship creations etc.

Some good resources around BDC can be found at:

1. http://channel9.msdn.com/shows/In+the+Office/Introducing-the-Business-Data-Catalog-BDC/
2. http://office.microsoft.com/en-us/sharepointserver/HA102200501033.aspx
3. http://blogs.msdn.com/martinkearn/archive/2006/10/05/What-is-the-Business-Data-Catalouge_3F00_.aspx

Cheers,

Manbhawan Prasad

How to Integrate SharePoint with Microsoft CRM

Integrating Microsoft CRM with SharePoint is often highly desired, SharePoint is an excellent document management tool and the list management features are unmatched. I have found many examples of SharePoint integration but none of them seemed to be particularly good, they either pointed to generic document libraries or did not provide sufficient information to bring the two products together in a seamless manner.

Read more Here

Creating a Dashboard using MOSS, Excel Web Services, Excel 2007 and Microsoft CRM

I.B.I.S. CRM Blog wrote

---

Creating a Dashboard using MOSS, Excel Web Services, Excel 2007 and Microsoft CRM

Creating a dashboard using SharePoint, Excel Pivot Charts and Microsoft CRM data is nothing new. Perform a Google search and you will receive thousands of results. However, finding information on how to create a live dashboard using the latest components of MOSS 2007, Excel Web Services, Excel 2007 and Microsoft CRM data is a bit more difficult.

1) Using the old method available in SharePoint 2003 of inserting the Office Pivot Chart web part and connecting to your CRM data source to retrieve the data does not work in MOSS 2007. The Office Pivot Chart web part is not available in this version using MOSS 2007 and Excel 2007.

2) Setting up Excel Web Services authentication methods (Windows Authentication, Single Sign On, or none) to allow the automatic refresh of the data in the spreadsheets on your dashboard page can be a bit confusing. I will discuss this in more detail later in this post.

3) Certain items used in Excel spreadsheets are not supported in Excel Web Services.

4) There is not one document that explains it all from start to finish, with all of the pitfalls highlighted. That is what I am hoping to accomplish with this post. My initial hurdles in getting this to work were all centered on getting the data to refresh with live data every time the page is opened. I kept receiving a data retrieval error saying the connection was unavailable. This was mainly the combination of the following:

a. Data Authentication

b. Creating the Excel Report based on a Query table, instead of a Pivot Table and Chart

c. Publishing the Excel Chart as a Report instead of a dashboard

This post is written based on the assumption that you have knowledge of MOSS 2007, creating a dashboard page in SharePoint and that the environment has been configured to use Excel Web Services. If not, below are some links that go into detail about how to do this and some other helpful links:

1) Plan external data connections for Excel Services – This article contain full instructions on how to configure MOSS to use Excel Web Services. Note: All of the steps in this article should be completed before moving on to building your reports and your dashboard.

http://technet.microsoft.com/en-us/library/cc262899.aspx#section7

2) Using Analysis Services data in Excel Services – This article goes into depth on configuring your servers to use Kerberos Authentication. This is required if you are using Windows authentication as your method of authenticating your spreadsheets to the data source.

http://www.tonstegeman.com/Blog/Lists/Posts/Post.aspx?List=70640fe5%2D28d9%2D464f%2Db1c9%2D91e07c8f7e47&ID=43

3) Excel Services part 12: Unsupported features

http://blogs.msdn.com/excel/archive/2005/12/01/499206.aspx

Before moving on with the rest of this post, a word on authentication; when creating your Excel spreadsheet and connecting to your CRM database to retrieve data, you have to select a method of authentication. There are three options:

1) Windows Authentication

2) Single Sign On

3) None

All three have their caveats, but the third option of “None” is the easiest to configure and is the one I have chosen to use in this example. The other two options require a much deeper understanding of Kerberos authentication and this will not be addressed in this post. Read the section entitled “Authentication to external data” in the “Plan external data connections for Excel Services” article listed above for a complete explanation on the configuration of each.

As I noted above, you need to configure your MOSS environment prior to actually creating your spreadsheet and building your page. A synopsis of the steps is listed below and is explained in detail in the “Plan external data connections for Excel Services” article:

1) Enable MOSS to use Excel Web Services

2) Add a trusted file location

3) Enable external data access for a trusted file location

4) Configure the unattended account settings – This is required if your authentication method is “None.” In this step, you will want the unattended account to be a domain user that is also a user in CRM. In my case, I just used the administrator login used when we installed CRM.

5) Create a data connection library

6) Add a data connection library to trusted list

7) Set a registry key to surface a data connection library in the client – This step is not required, but if you would like the data connection library to show up as a location to select data connections from when building your spreadsheet and creating a connection, you will have to do this. Otherwise, you can still access the location by typing in the path to the URL when browsing for your data connections.

Once you have completed these steps, you are ready to create your worksheet and integrate it into your website.

1) Create a new connection within a new Excel spreadsheet.

· Open Microsoft Excel 2007.

· Go to the Data tab.

· Select “From Other Sources.”

· Select “From Data Connection Wizard.”

· Select Microsoft SQL Server. Click Next.

· Enter your server name. It is ok to use Windows Authentication here. Click Next.

· Select your CRM database and the view in which to use. In this example, I am going to create a simple graph using the Opportunity View. Click Next.

· Select a file name for your data source that will be saved. We will need to modify the location so that the data connection is stored to the Data Connection Library on the SharePoint site. To do this, click Browse. If you do not have a link to your SharePoint Data Connection Library, you can find this by browsing to your SharePoint site using Internet Explorer and selecting the Data Connection Library link. Copy the location from the Address Bar in IE (All the way through the DataConnections only. Do not include the .aspx ending. Ex. - http://intranet/Department%20Sites/Sales%20%20Marketing/sales_dash/Data%20Connections/) and paste this before the file name you have selected.

· Select the “Always attempt to use this file to refresh data check box.”

· Click on the Authentication Settings button.

· Select your authentication method. In my case, I select “None.” This will trigger the Excel Web Services to use the Unattended Account we set up earlier.

· Select OK on this dialog and then Finish on the previous dialog.

· When you are prompted with the Web File Properties dialog, select SharePointLibrary as your Connection Type and ReadWrite as your UDC Purpose.

· On the Import Data dialog, select PivotChart and PivotTable Report. Note: Selecting Table will not work. It is called a Query Table and is not an option that is supported in Excel Web Services.

· Build your Pivot Chart. Here I am performing a simple summation of the Estimated Value of my opportunities in a particular category.

· Click on any area within the Pivot Table, click Data from the top menu bar, then Connection Properties.

· Under the usage tab, select all three of the Data Refresh options.

· Under the definition tab, select the “Always use the connection file” checkbox.

· Since we have changed the connection information, we will have to re-save it to the Data Connection Library on the SharePoint site. To do this, click on the Export Connection File button. If your location did not default to the Data Connection Library, browse to it as described in step (h). Click Save to update the Data Connection file.

· Click OK to close the Connection Properties dialog.

· We are now ready to publish the spreadsheet to the Reports Library on the SharePoint site. Click on the Office Button in the top left corner, select Publish, then select Excel Services.

· Before saving the file, make sure you are publishing it to the Reports Library on the SharePoint. As in step (h), if you do not have a link to the Reports Library, you can find it by navigating to the Reports Library on your SharePoint site using Internet Explorer. Copy the address and paste it before the file name. (Ex. http://intranet/Department%20Sites/Sales%20%20Marketing/sales_dash/ReportsLibrary/)

· The next dialog will prompt you with choices of the items you would like to publish. In my case, I only want to display the chart, so from the Show tab, I select Items in the Workbook and Chart 1. You can choose to show any item in the spreadsheet that is support by Excel Web Services. Select OK.

· The next dialog is important as well. Be sure to select Dashboard Page. If you select Report, whatever you intend to publish is only published as a snapshot and the data will not refresh when revisiting the web page containing your data and charts.

· Your published report will be rendered in Internet Explorer. The next step will be to add the report from your Reports Library to your SharePoint Dashboard page.

Assuming that your base dashboard page has already been built in SharePoint, I am jumping ahead a few steps to actually adding the new Excel Chart to the page.

· From your dashboard page, select Add a Web Part.

· From the dialog box, select the Excel Web Access part.

· After the part is added click on the “Click here to open the tool pane” link.

· In the “Workbook Display” section of the Properties, select the ellipsis to browse your SharePoint Report Library. Select the Excel file that you uploaded earlier.

· Go through all of the Properties areas for the web part, adjusting what is displayed and what is not. Once you are done with the Properties, select OK to add your web part to the dashboard page.

There you have it! To build more reports and data connections, simply follow the steps listed above and then add them to your dashboard page. In this example I used the CRM database as my data source, but in reality this will work for any external data source.

Creating SharePoint Sites With CRM Workflow

Simon Hutson continues his post with an example on creating SharePoint sites within CRM. Click Here.

Minimal SharePoint Site Definition For Use With CRM

Simon Hutson has a great blog on creating an SharePoint Site Definition.

Click Here

Share Point and CRM

As many of my customers know, MSCRM doesn't come with an out-of-box connection the Microsoft SharePoint. You would think it would, at convergence this year I heard them say that the next release would have it. But that doesn't help us today. Currently the integration is using what are called iframes and will display a linked SharePoint site within it. However, by default you get all of theose wonderful menus and headers that make the site function, when all you realy want is a minimual site. Simon Hutson has a nice write-up on creating a minimal site for use with CRM. Check it out here

Share Point and CRM

As many of my customers know, MSCRM doesn't come with an out-of-box connection the Microsoft SharePoint. You would think it would, at convergence this year I heard them say that the next release would have it. But that doesn't help us today. Currently the integration is using what are called iframes and will display a linked SharePoint site within it. However, by default you get all of theose wonderful menus and headers that make the site function, when all you realy want is a minimual site. Simon Hutson has a nice write-up on creating a minimal site for use with CRM. Check it out here