Search This Blog

Thursday, November 11, 2010

Displaying Charts in SharePoint using Excel Services


An article on how to display charts in SharePoint using Excel Services

Introduction

A chart displays data in a visually appealing way. By using charts, it is very easy to understand and compare data. We can use Office Excel 2007 for creating charts and Excel Services of MOSS to display it in SharePoint.

Excel Services

Excel Services is part of MOSS 2007. Excel Services has a server-side engine for loading Excel spreadsheets, calculating formulas and rendering their output as wide-reach HTML targeting the browser.
Open Central Admin -> Operations -> Services on Server, then Start the Excel Calculation Service on the server if it has been stopped.

Excel Web Access

Excel Web Access is a webpart that displays data and charts from an Excel workbook. It has a similar look to Office Excel workbook.

Creating a Document Library

Create a Document Library in your MOSS Site. Go to Site Actions -> Create -> Document Library to create a document library in your SharePoint site. This document library will be useful to publish our Excel spreadsheets.

Trusted File Location

Excel Services open Excel workbooks that are stored in trusted file locations only. Trusted File location is a SharePoint Document Library, a UNC path, or an HTTP site that is configured as a trusted repository for Excel workbooks.
Add this newly created Excel document library as a trusted file location for storing Excel workbooks. To do this, open the Shared Services Administration Site then click the �Trusted File Locations� link from Excel Services Settings. Now, click �Add Trusted File Location� and add our document library as a trusted location.



Creating Charts using Office Excel 2007

We can use Office Excel 2007 for creating different kinds of charts like Column, Pie, Bar, Area, Scatter, etc. See the following image which shows the different chart types available with Office Excel 2007.



Chart Types in Office Excel 2007.

In this sample, I'm going to create a Column Chart by using the following sample data.

To create a Column Chart, select the data and Click on Insert -> Column (Chart) and select your desired chart. See the following screen - I've selected Clustered Cylinder.




The following chart will be created once the chart type (Clustered Cylinder) is selected. And this chart will be named as �Chart 1�.


We are going to use Excel Web Access Webpart to display this particular chart. It is also possible to display the Entire Workbook. Once the chart has been created, publish this workbook to the document library. From the Office menu, click Publish -> Excel Services as shown in the screen below:


Click on Excel Services, this will open a �Save As� popup dialog and enter the Document Library path to save this file to the Library.



Open in Excel Services� � Once the form is saved, Excel Services will open the Excel workbook in a new browser.
We can add some more options like showing only selected sheets, named items, or adding parameters, etc., with the use of �Excel Services Options�. See the image below which shows the Excel Services Options.




These are the different options available. Here we can select the entire workbook, only selected sheets or any named item. In our sample, we are going to display only a chart. Select the �Items in the Workbook�, this will show a list of named items, in our case we have only one item �Chart 1�. Check �Chart 1� from the list. Click �Ok� and save the Excel workbook to the Document Library.

There is one more tab �Parameters� which can be used to pass parameters to Excel services. Excel Services will create a UI by using InfoPath at run time to get input for those parameters.
Now, open your SharePoint site and add the �Excel Web Access WebPart� to your page.
If this WebPart is not in your list then activate the �Office SharePoint Server Enterprise Site Features� feature.

Once the WebPart is added to the page, open the Tool pane. This tool pane contains different properties that will be helpful to display the workbook, part of a workbook or an item in a workbook.




Browse and select the workbook from the document library, enter �Chart 1� in Named Item property and enter other properties like rows, columns etc. Now, click Ok. Excel services will render the chart. If the published workbook contains parameters, then an InfoPath form will be displayed along with the chart. InfoPath form will be used to get input and the calculation service of Excel will perform the calculation in the server and render the results back to the browser.


It is also possible to use Excel Web Services (EWS) hosted in SharePoint that provides several methods that can be used as an API to build custom applications based on the Excel workbook.

42 comments:

  1. Thanks..Was very useful

    ReplyDelete
  2. When I initially commented I clicked the "Notify me when new comments are added" checkbox and now each time a comment is added I get several
    e-mails with the same comment. Is there any way you can
    remove me from that service? Thanks a lot!

    My page - NFL Jerseys Wholesale

    ReplyDelete
  3. Thankfulness to my father who told me on the topic of this blog, this website is actually amazing.



    Feel free to visit my web blog Abercrombie and Fitch

    ReplyDelete
  4. It's perfect time to make a few plans for the future and it's time to be happy.

    I have read this submit and if I may just I wish to suggest you few interesting issues or advice.
    Maybe you could write next articles regarding
    this article. I desire to read even more things about it!


    My blog Gucci Borse

    ReplyDelete
  5. Wow, incredible weblog layout! How lengthy have you been blogging for?

    you made running a blog glance easy. The whole glance of your website is excellent,
    as neatly as the content material!

    Feel free to surf to my web blog :: Discover More Here

    ReplyDelete
  6. It's actually a nice and helpful piece of information. I'm satisfied that you
    just shared this helpful info with us. Please keep us up to date like this.
    Thank you for sharing.

    Stop by my homepage ... Louis Vuitton Outlet Online

    ReplyDelete
  7. Excellent article. I am facing a few of these issues as well.
    .

    my webpage; Converse All Star

    ReplyDelete
  8. Hi, I do think this is a great blog. I stumbledupon it ;) I'm going to revisit yet again since I saved as a favorite it. Money and freedom is the best way to change, may you be rich and continue to help others.

    Feel free to visit my weblog Sidney Crosby Jersey

    ReplyDelete
  9. What's up, everything is going sound here and ofcourse every one is sharing facts, that's truly excellent,
    keep up writing.

    My web blog - Sac A Main Louis Vuitton

    ReplyDelete
  10. I have read several good stuff here. Certainly worth bookmarking for revisiting.

    I surprise how a lot attempt you put to make this type
    of great informative website.

    Here is my webpage; dance pointe shoes

    ReplyDelete
  11. I have been exploring for a little bit for any high quality articles or blog posts
    on this sort of area . Exploring in Yahoo I eventually stumbled upon this site.
    Studying this info So i am satisfied to show that I have a
    very just right uncanny feeling I discovered exactly what
    I needed. I most surely will make certain to don?
    t forget this web site and provides it a glance regularly.


    Feel free to surf to my blog Foot pain treatment

    ReplyDelete
  12. Hey there! I simply wish to give you a huge thumbs
    up for the excellent info you've got here on this post. I'll be coming back to your
    website for more soon.

    Here is my web-site ... click for source

    ReplyDelete
  13. Hello, i believe that i saw you visited my site so i came
    to return the favor?.I'm trying to in finding things to enhance my web site!I guess its adequate to use some of your ideas!!

    my website - Chaussure De Foot

    ReplyDelete
  14. My brother recommended I would possibly like this
    blog. He was once totally right. This post truly made my day.
    You cann't imagine just how so much time I had spent for this info! Thank you!

    Also visit my web page: Wholesale Jerseys Cheap

    ReplyDelete
  15. It's really very complex in this active life to listen news on Television, so I simply use web for that purpose, and get the latest information.

    Here is my web site: Full Article

    ReplyDelete
  16. hi!,I like your writing very so much! proportion we be in contact more
    approximately your article on AOL? I need an expert in this
    space to unravel my problem. May be that is you! Taking a
    look forward to peer you.

    Here is my page ... nysacpr.org

    ReplyDelete
  17. I like the valuable information you provide in your articles.
    I'll bookmark your blog and check again here regularly. I am quite certain I'll learn plenty of new stuff right here!
    Good luck for the next!

    Look into my web-site; Louis Vuitton Bags

    ReplyDelete
  18. Hello everyone, it's my first pay a visit at this site, and post is in fact fruitful designed for me, keep up posting these types of articles or reviews.

    Feel free to visit my webpage ... Louis Vuitton Outlet

    ReplyDelete
  19. I know this if off topic but I'm looking into starting my own blog and was wondering what all is needed to get set up? I'm assuming having
    a blog like yours would cost a pretty penny? I'm not very internet savvy so I'm not 100% certain.
    Any recommendations or advice would be greatly appreciated.

    Many thanks

    my page ... Michael Kors Handbags

    ReplyDelete
  20. That is a good tip particularly to those new to the blogosphere.

    Short but very accurate information… Thanks for sharing this one.
    A must read article!

    my blog: Christian Louboutin Heels

    ReplyDelete
  21. Link exchange is nothing else but it is simply placing the other
    person's weblog link on your page at appropriate place and other person will also do similar in favor of you.

    My web page: continue reading

    ReplyDelete
  22. I don't even know how I ended up here, but I thought this post was good. I do not know who you are but definitely you're going to a
    famous blogger if you are not already ;) Cheers!



    Also visit my website :: continue reading

    ReplyDelete
  23. Heya i'm for the primary time here. I came across this board and I find It really useful & it helped me out much. I am hoping to present something back and aid others such as you aided me.

    Check out my web-site ... visit this link

    ReplyDelete
  24. Nicholas Kennel - An ideal Actor

    Also visit my web site calamity

    ReplyDelete
  25. I am extremely impressed with your writing skills and also with the layout on your blog.
    Is this a paid theme or did you customize it yourself? Anyway keep
    up the excellent quality writing, it is rare to see a great
    blog like this one these days.

    my webpage - Going Here

    ReplyDelete
  26. Hi there exceptional blog! Does running a blog similar to this require a large amount of work?
    I have virtually no knowledge of computer programming but I had been hoping to start my own blog in the near future.
    Anyhow, should you have any recommendations or tips for new
    blog owners please share. I know this is off topic however I simply had to ask.
    Thank you!

    Feel free to visit my web page: Recommended Site

    ReplyDelete
  27. The fewer the sites assist in electrocution fat all concluded weightiness
    lose the body. Cut out confects and cardio physical exercises as they are very important for
    acquiring weight turn a loss. All it occupies
    is up and function your devious musculus as you will sit on a chair with your animal foots flat on the storey.
    The most effective weightiness mislay turn is the belly modify until 12 to 16 weeks.


    Here is my homepage www.truthaboutabsdon.com
    - -

    ReplyDelete
  28. Great post. I will be facing some of these issues as well.
    .

    Also visit my web page - Abercrombie France

    ReplyDelete
  29. You can certainly see your enthusiasm within the article you
    write. The sector hopes for even more passionate
    writers like you who aren't afraid to mention how they believe. All the time go after your heart.

    Here is my website ... Tory Burch Shoes

    ReplyDelete
  30. I am truly pleased to read this web site posts which carries
    plenty of useful data, thanks for providing these information.


    My webpage: Oakley Frogskins ()

    ReplyDelete
  31. Terrific work! This is the type of information that are meant
    to be shared across the net. Disgrace on Google for not positioning this publish upper!
    Come on over and visit my site . Thank you =)

    my weblog :: Cheap Oakley Sunglasses

    ReplyDelete
  32. Good web site you've got here.. It's hard to find high-quality writing like yours nowadays.
    I honestly appreciate people like you! Take care!!

    Here is my web page :: Michael Kors

    ReplyDelete
  33. I am regular reader, how are you everybody? This paragraph posted
    at this web site is truly nice.

    Also visit my blog ... purifying

    ReplyDelete
  34. You could definitely see your enthusiasm in the article you write.
    The world hopes for more passionate writers such as you who aren't afraid to say how they believe. At all times go after your heart.

    My homepage: Sac a main Guess - -

    ReplyDelete
  35. Saved as a favorite, I like your web site!

    Feel free to visit my web-site :: New Kobe Bryant Shoes

    ReplyDelete
  36. Howdy very nice web site!! Guy .. Excellent .. Superb .
    . I will bookmark your blog and take the feeds additionally?
    I am glad to search out a lot of helpful info here within the submit, we'd like develop more strategies on this regard, thank you for sharing. . . . . .

    Here is my website; Kevin Durant Shoes

    ReplyDelete
  37. Inexpensive and Trendy Ashley Home furniture Tampa

    my blog post; counterattraction

    ReplyDelete
  38. It's really a cool and helpful piece of info. I am happy that you just shared this useful information with us. Please stay us informed like this. Thank you for sharing.

    Review my webpage; LeBron James Shoes 2013

    ReplyDelete
  39. I'm not sure where you are getting your information, but great topic. I needs to spend some time learning more or understanding more. Thanks for excellent information I was looking for this information for my mission.

    My web blog :: Air Max

    ReplyDelete
  40. Hi, I do believe this is a great site. I stumbledupon it ;
    ) I am going to revisit once again since i have saved as
    a favorite it. Money and freedom is the greatest way to change, may you be
    rich and continue to guide others.

    Feel free to visit my webpage: buy bulk twitter followers

    ReplyDelete
  41. This blog is having the general information. Got a creative work and this is very different one.We have to develop our creativity mind.This blog helps for this. Thank you for this blog. This is very interesting and useful.
    Sharepoint Training in Chennai

    ReplyDelete