• Home
• Blog

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

Posted Tuesday, June 24th, 2008

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.

7 Responses to “Creating a Dashboard using MOSS, Excel Web Services, Excel 2007 and Microsoft CRM”

1. Will says:

   May 16, 2009 at 12:22 pm

   Helpful post–

   I’m trying to use MOSS to present a combination of BI cube data and hand-reported data (in Excel) from a variety of human sources.

   Let me say, for the record, that I’m not really an IT person.

   My plan is to gather data from individual areas in spreadsheets (with the same flat-file “schema” on collaboration sites. Then, cut/paste them into the master-spreadsheet in which I will pivot the data to enable easier reporting/publishing as you outline here.

   Clearly, my “plan” is ridiculous/cumbersome to administer.

   I would love any hints you might provide as to how I could streamline this process.

   Appreciatively,

   Will
2. Loyal says:

   June 1, 2009 at 2:51 am

   Excellent post,

   one thing i would like get clarified is that, how do we get data from another document library(eg: Calendar) to display the data in excel instead of getting data from sql databases?
3. David Pritchett says:

   July 21, 2009 at 1:10 pm

   AS far as getting data from another document such as a calendar file, it really depends on the limitations of Excel. If you go to the Data Connections area of Excel and click on the Add button, you will be able to add custom connections to the spreadsheet. I personally have not connected to anything other than SQL, CSV or other Excel files, but there are many other options. You may want to check the Excel area of the MSDN site or perform a web search for suggestions on using Excel to connect to external data sources of your choice. Sorry I do not have expertise in that specific area.
4. Abdullah says:

   October 18, 2009 at 9:23 pm

   Thanks a lot,

   that was very useful for me..

   appreciate your work
5. Ruben says:

   November 4, 2009 at 1:07 pm

   This is a great post. FINALLY information that was useful in learning to configure a dashboard in SP2007.
   Nice work.
6. Dan L. Hill says:

   November 10, 2009 at 3:58 am

   Inspiring Post. Thanks for this. I’ve implemented the steps as you have outlined. The only change is that I am updating the 2007 Excel spreadsheet directly (no data connection to SQL needed). I need to re-publish the graph to my dashboard library after each update AND reload the workbook from my dashboard web part page in order to get this to work. What could I possibly have missed?
7. Ivo Milanov says:

   February 5, 2010 at 1:18 pm

   There is a smarter and more advanced way to display pivot charts in SharePoint. The Nevron Chart for SharePoint can connect to any type of data source – including Databases, Excel Spreadsheets, BDC, SharePoint lists etc.

   The chart is using a very advanced pivoting engine and the settings for the pivot are visually edited in the browser – no need to publish, administer etc. – just edit the web part in the browser directly, with a desktop like UI built for the web.

   The pivot charting capabilities of Excel are also not very good – you do not have the power to define multiple grouping and sorting rules, many charting types are simply not supported by the pivot chart etc.

   The charting capabilities of Excel are also below industry standards for “best charts”. So see Nevron Chart for SharePoint for the best pivot charting solution up to the moment.

• Home
• Dynamics AX
• Dynamics CRM
• Dynamics GP
• Terms
• Contact