Display Charts in SharePoint 2010 using Excel Services and Chart Web Parts

Posted by: Mahesh Sabnis , on 6/14/2012, in Category SharePoint
Views: 103653
Abstract: In this article, we are going to discuss the mechanism of fetching data in SharePoint 2010 from an Excel Spreadsheet using Excel Services and represent that data in the form of a Chart

Intra-organization portal users often need access to some form of Sales, Production or Revenue generation graphs or charts by directly querying Data Stores (persistent database like SQL Server) or even a spreadsheet like MS Excel. To achieve this, developers usually write a huge amount of complex code in Web Applications using concepts like ADO.NET or VSTO programming. If SharePoint 2010 is used for such scenarios, then such complex solutions like these  can be easily designed by developers or even the non-developer IT-staff in the organization, within no time.

SharePoint (SPS) 2010 is a competent technology for managing contents like documents, spreadsheets etc. In SPS2010, we have a set of services using which we can manage various operations needed for intra-organization automation like managing document library where Excel Workbooks, Word Documents, PDF’s etc can be stored.

One important feature of SPS 2010 is that if you have Excel worksheets in the document library, then using Excel Services, data from a specific range of the worksheet can be used to generate charts. The Excel service is a part of SPS 2010 and built upon ASP.NET and SharePoint Foundation.

 

In this article, we are going to discuss the mechanism of fetching data in SharePoint 2010 from Excel Spreadsheet using Excel Services.

Reading Data from Excel Workbook to Generate Charts using Excel Service

This feature is really exciting. We can simply consider a scenario where various marketing personals are maintaining worksheets to record the sales information in it. Once they submit their worksheet to the office, the IT personal generates a consolidated worksheet where state wise sales data is stored. Now this data is to be represented in a chart format without any hassles. To achieve this task, the Excel workbook must be stored in the Shared document library of the SharePoint 2010 portal as below:

shared-document-library

The Sales data in the workbook is as shown below:

sales-data

Step 1: Browse to the site, and click on ‘Edit’:

edit-page

Step 2: You will see the ‘Editing Tools’ on the ribbon. Click on ‘Insert’ and select a ‘Web Part’ as shown here:

insert_view-webpart

Step 3: Select ‘Business Data’ from Categories and ‘Chart Web Part’ from Web Parts and click on the ‘Add’ button:

business-data

You will see a default chart Web Part similar to the one shown below. To retrieve data for the chart, click on ‘Data & Appearance’:

chart-default-appearance

You will be promoted to save, just click on ‘Ok’.

Step 4: The ‘Data Connection and Chart Wizard’ starts. Click on ‘Connect Chart to Data’

chart-wizard

On clicking ‘Connect Chart to Data’, you will get a ‘Data Connection Wizard’ page with Data source options:

data-source-options

The above image shows various data source options. You can connect the chart to another web part, a List, to an External Content type (e.g. SQL Server, WCF etc.) and even Excel Services. In this case, we will be using Excel Services. As for this scenario, a typical use of Excel Service is as shown below:

excel-service-usecase

The above diagram represents that Excel Workbooks are stored in SPS content database. The Application server which contains Excel Calculation service, loads the workbooks and performs calculations on it, if required. One of the major responsibilities of the excel calculation service is to maintain the session for the duration within which caller interacts with the Workbook. When caller closes the workbook, the session is closed. The Excel Web service is a part of Excel Service which provides access to Workbook to perform operations; in our case currently we are making use of Excel Service to generate Chart in the Chart Web Part by providing a Cell Range.

Step 5: Click on ‘Connect to Excel Service’ and click on Next. This step will ask the Excel Workbook path for an excel document which is already stored in the Shared Document. A sample URL is as follows:

hxxp://MySite.Organization.com/sites/MIS/Shared%20Documents/State_wise_Sales.xlsx

Here the information of the ‘Excel Workbook Path’ and ‘Range’ needs to be entered:

url-path

After clicking on ‘Next’, data will be retrieved from Excel sheet as shown below:

excel-data

Click on ‘Next’ and the UI Display requirements can be set – like the X-field, Y-field etc

chart-settings

Clicking on Finish displays the chart

sharepoint-chart

The appearance of the chart can be changed as per your requirements. To change the appearance of the chart, click on ‘Data & Appearance’ once again and you will get the ‘Data Connection & Chart Appearance Wizard’ page as shown below:

chart-customization

Click on ‘Customize Your Chart’. Here in the ‘Chart Customization Wizard’, you will get the ‘Chart Type Categories’ and ‘Chart Templates’. From the ‘Chart Type Categories’ select ‘Pie’ and select ‘template’ as per your requirements:

chart-type

Click on Finish and a chart gets displayed

chart-web-part-result

Conclusion:

By providing a deep integration with Excel Services and Chart Web Parts, it is easily possible to build statistical data representation in SharePoint 2010. Users who are interested in visual representation than plain figures, will find this feature very exciting and quick to implement. One more benefit here is that any member of your IT-staff can easily perform such operations without any additional developer skillsets.

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Manet on Thursday, June 14, 2012 11:56 AM
Mahesh, thanks for the explanation, is it possible to have a filter drop down so the users can select the variant and the charts can be refreshed based on the selection by the users. I am limited to excel services only and hence cannot use performance point which relies on SQL. Thanks
Comment posted by Manet on Thursday, June 14, 2012 12:13 PM
Mahesh, thanks for the explanation, is it possible to have a filter drop down so the users can select the variant and the charts can be refreshed based on the selection by the users. I am limited to excel services only and hence cannot use performance point which relies on SQL. Thanks
Comment posted by Manet on Thursday, June 14, 2012 12:20 PM
Mahesh, thanks for the explanation, is it possible to have a filter drop down so the users can select the variant and the charts can be refreshed based on the selection by the users. I am limited to excel services only and hence cannot use performance point which relies on SQL. Thanks
Comment posted by Mahesh Sabnis on Friday, June 15, 2012 2:23 AM
Hi,
  It can be implemented using visual Web Part where you can create a List of available charts and then using custom code you can deal Excel data sheet.
Thanks
Regards
Mahesh Sabnis
Comment posted by Shivani on Wednesday, January 9, 2013 8:19 AM
Hi,

Excellent post.

Is it possible that user upload a raw data in pre-defined template AND Sharepoint does the pivot table in the background and then generate a chart based on it ? The pivot table options and the chart will be defined by me. All my allowed users have to do is upload a spreadsheet to see the dashboard I want them to see.

Thanks,
Shivani
Comment posted by Vinnie on Wednesday, January 9, 2013 3:30 PM
Is it possible to display 2 different chart types...I.e. Line and bar together?
Comment posted by Deepak chauhan on Thursday, January 10, 2013 10:45 PM
Hello sir,
Explanationos too good in this Article.


Thanks & Regard
Deepak chauhan
Comment posted by Tabares on Wednesday, January 23, 2013 2:57 PM
Very useful. Thank you very much.
Comment posted by Satbir on Thursday, January 24, 2013 4:13 AM
Hi,

Very informative article. i would require your help:

Scenario is: There are list of excel files in document library, one for each month. I want an interface from where user can select the month range and chart is to be displayed on the basis of the data stored in the month range excel sheets, eg if user selects, jan to march then chart is to be displayed from the data in three excel files.

is it possible using the approach u mentioned?

thanks
Comment posted by Jignesh on Wednesday, January 30, 2013 9:05 AM
How to take the print out of the chart?? Data & Appearance and Advance property option should not appear in printout
Comment posted by Jignesh on Wednesday, January 30, 2013 9:07 AM
Is it possible to convert chart to the Excel??
my chart is populating from the another web part now i want to convert that chart to excel.
Is it Possible??
Comment posted by Ejaz on Wednesday, February 13, 2013 2:39 AM
I am getting the following error message any ideas please?

Please correct the following:
•Exception has been thrown by the target of an invocation.
Comment posted by Jon on Thursday, February 28, 2013 10:54 AM
What is the performance of a chart web part if the data source is a sharepoint list of say 15,000 items. I know that the List View threshold is 5000. Does the chart of the same constraint?

Thanks
Comment posted by paromita on Tuesday, March 26, 2013 8:41 AM
Unable to configure chart webpart. getting the below error. please help
Exception has been thrown by the target of an invocation.
Comment posted by Candace on Wednesday, April 3, 2013 11:50 AM
I am getting the following error message any ideas please?

Please correct the following:
•Exception has been thrown by the target of an invocation.
Comment posted by Frank on Monday, April 29, 2013 2:50 PM
Do you have a 'Step-by-Step' PowerPoint to follow on how to enable Excel Services.

Hope you can help,
Thanks
Comment posted by Jamie on Tuesday, May 21, 2013 6:43 AM
I have an excel document with a pie chart.  When I "Save and Send" to SharePoint to have it display in an Excel Webpart it removes the Data Label lines.   Any suggestions on what may be the cause?
Comment posted by Frank on Tuesday, June 4, 2013 10:33 AM
Very good information but my question is, "How do I canfigure Excel Services for SharePoint 2010?" I've been looking for step-by-step instructions and even better, screen shots.
Hope you can help
Comment posted by Devlin on Thursday, June 6, 2013 7:32 AM
Getting the following error when trying to set the excel data source

Exception has been thrown by the target of an invocation.
Comment posted by Mohan Dhokare on Wednesday, August 14, 2013 7:21 AM
Very good post for sharepoint learner.....
Comment posted by sunil on Tuesday, December 31, 2013 1:56 AM
Great post, Thank you
Comment posted by Juhi on Tuesday, April 22, 2014 7:56 AM
how do i refresh the source xls in case the SHarepoint list gets updated
Comment posted by Samanth on Thursday, July 31, 2014 8:02 AM
I am getting the following error message
•Exception has been thrown by the target of an invocation.
Please help me if any one knows

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel