CRM 2011 and SharePoint 2010 Integration - Part 2

Technorati Tags: Excel Services sharepoint 2010,crm sharepoint integration,sharepoint crm integration,excel services

Hi SharePoint Folks,

This post comes after a long time after the Part 1. But as the saying goes – better late than never. Here I am back with Part 2, this time we will focus on Reporting CRM information into SharePoint Using Excel services.

Part 1: Introduction and CRM 2011 - Document management Integration with SharePoint 2010

Part 2: Reporting CRM data in SharePoint using Excel services (This post)

Part 3: Publishing CRM entities in SharePoint.

Part 4: Search CRM entities from SharePoint Enterprise Search.

The word you are thinking is “awesome”, well I know J. Ok let’s cut short the talking and make it work, get ready!

Reporting CRM data in SharePoint using Excel services

So below is what you will need J

1. CRM 2011 deployment and some sample data (I populated my CRM with built in sample data).

2. A SharePoint 2010 farm, with a web app created

3. This post

What we will achieve by end of this post is to create an excel based report, to – Show all the account’s revenue, and have a filter on accounts based on number of employees . Our first step in the direction of SharePoint –is the SSS i.e. Secure Store Service. We will use Secure Store Service to, create an Application ID, We will use that App ID to retrieve the information from database server where the CRM 2011 is currently connected.

TASK 1

So we open SharePoint Central admin > Secure Store Service application and Create a New Application ID> and fill in the info as shown in the figure below,

1. Target Application ID – CRMKey

2. Display name – CRMKey

3. Contact E-mail – any valid email address preferably

4. Target Application Type – Keep it to Indivdual

Click Next,

Modify the Field name to reflect appropriate application credentials, this is not a necessary step, you could leave the default names as is.

Click Next

Specify who is going to manage the target application, Apart from farm admin, who by default has rights to modify the settings. For now, I have specified my CRM admin’s account – contoso\crm11.

Click Ok and proceed to SSS main page, with our Application ID created.

Here we just need to set the credentials once, Click on the drop down and select Set Credentials

Below is what you will see, in Credentials owner specify the account that will manage this credentials – more simply put – just put the farm admin account here. What is more important here is in the username and password box, you specify the CRM 2011 account who has admin credentials, basically who can create a connection to the database Server of CRM 2011 deployment

I have specified my crm11 admin account and its password. Click Ok to Finish

We are DONE with SSS now.

Task 2

Next we will create a Data connection Library, which is a specific type of library where we will store the data connection file (.odc),

Now create a library where you want the resulting excel file to be published, this can be any normal library. I am going to use my Shared document library.

TASK 3

Now one important task, We need to configure excel service to trust the “data connection library” and the “shared documents” . Unless the trust is configured the report would not render.

So Open Excel Service application main page > click on “Trusted File Location”

Click “Add Trusted File Location”

Enter the shared documents location (and of course remove the trailing /forms/allitems.aspx)

Tick mark – Children Trusted

Scroll down, Under the External Data section Select Trusted data connection libraries only (this is because we will use the connection kept in SharePoint)

Uncheck Refresh warning enabled

Click on OK

Now go back to Open Excel Service application main page> click on “Trusted Data connection Libraries”

Click on Add Trusted data connection library

Enter the location for data connection library and click OK

The result should like this

TASK 4

After this Next comes the task to create a connection file which will enable Excel to connect to CRM database views

1. Open Excel > Data > From Other Sources> From SQL Server

2. Specify the CRM’s SQL server name,

3. Specify the Organization database of the CRM, in my case its Fabrikam_MSCRM, and Select FilteredAccount

4. On the next screen, click Authentication Settings next to Excel Services, and enter the SSS Application ID we created

1.

1. Click ok and come back to the wizard, Now clik on Browse and save the connection file to SharePoint Data connection library

Click Finish in the wizard, after the wizard finishes just click on OK, in the property window that pops up

Then just hit cancel and exit out of Excel.

2. Now click Save, Once saved, Go to SharePoint data connection library and Approve the connection file, as shown below

TASK 5

Let’s now create an Simple Excel Report. Open Excel > Data> Existing Connections> Click on Browse for More and specify the SharePoint data connection library location, and click open.

Now you will get the Import data prompt , select Pivotchart and Pivotable Report, and click Ok

You get the Pivot chart on the Excel worksheet

Now Coming back to our objective to –“– Show all the account’s revenue, and filter to filter the accounts based on number of employees”

We will drag the Field “Name” to “Axis Fields (categories)” box, By the way the fields are sorted in alphabetical order, it should be easy to find fields.

Next drag the “revenue” field to “Values” box,

The end result is as below

You will notice the pivot table shows the COUNT of the revenue, which is not what we want, we want to see the number, so right click on the “revenue” or “count of revenue”

Continued in Page 2

Comments

• Anonymous
  April 07, 2012
  Is very informative, thanks.

• Anonymous
  July 30, 2012
  Anand has come up with some cool articles on the following topics, which I am sure will be useful to

• Anonymous
  September 30, 2014
  Introduction
  In October 2014 I will be giving a talk to the Geneva SharePoint User Group about the

• Anonymous
  September 30, 2014
  Introduction
  In October 2014 I will be giving a talk to the Geneva SharePoint User Group about the integration options between Dynamics CRM and everyone’s favourite document management repository...