CRM 2011 and SharePoint 2010 Integration – Part 2 Continued….



By default Count is selected, change it to Sum and click OK

clip_image004 clip_image006

Now we see the table and the chart correctly


Now the next job is to Add a Slicer that will filter the chart and table based on the number of employees. Select the Chart by clicking it once and then Click Insert and select slicer


Select “numberofemployees” and click OK


You will now be able to see some meaning ful report


Task 6

Now the real hero comes into picture, the Excel Services. Click on the file>Save & Send> Save to SharePoint > double click on “Browser for a location” and locate the SharePoint document library location where the Excel report will be saved


Navigate to shared document library


As soon as you save you see the report in the browser


Verify if its working by selecting the “number of employee” slicer


And yes it does J

Ok what happens when the CRM data changes ? Try it – Open an CRM account and change the revenue value , I did for “A store (sample)” frm 10000, to 12000.


Now go back to the excel service report and click Data> refersh all connection


And see the smooth update of data


And this way you can hopefully build complex and more meaning ful reports and publish it in SharePoint.

Enhance this ?

If you want to show/display the excel workbook anywhere in SharePoint, you can use the Built in “Excel Web Access web part

Open your SharePoint site and edit the page and add a webpart “Excel Web Access”


Click Add and see the web part added. Now open web part properties



You will see the properties


In the workbook Box enter the excel work book location, in my case – \crmrpt/shareddocument/CRM report.xlsx. Click Apply.


Further we can just have the chart shown in the site

Change the web part property, Enter Named Item to Chart 1 (This is the chart object’s name), if you want to verify open excel and see the chart property


And It just shows the chart on the home page,


To get the chart name see in the Excel, click on the chart object to select it and see the name in ribbon.


Further Enhance this using Connected Web parts

If you have several reports in the library AND you want to just have click and see behavior we can create connections between web parts to have that kind of experience. Just ensure that you have multiple reports in the library.

Edit the home page, Add the library that has Excel Reports in it AND add the Excel Web Access webpart on that same page, Configure the Excel Web access to show a report (this would be the default report that it will show). Now click on the menu of the Library >Connections>Send Row of Data To > Excel Web Access Web part.


In the following popup menu

Got to Tab 2. Configure Connection> and set the Field name to Document URL> and Finish. Now save and Close the page.


See it in action, by selecting the report



With that I will come back with the 3rd and the 4th part soon. Thanks for reading.