The requirement is that a business user (who is very familiar / comfortable with Excel) should be able to do the data input using Excel which needs to be stored in SharePoint and using that data we should be present the information in browser using various display formats like grid, chart etc.
In my previous blog I explained how to create a site collection of type “Business Intelligence Center”. The very first thing we will do is to create an excel file which will be used for data input. Now here we can use our creativity to present a very well formatted and structured sheet for data input for business user and have a data sheet based on linked formulas to transform that data into a tabular list which will become source for Chart data.
The sample excel file I created looks something like this.
Template Sheet:
Notice the Name Range defined as “ACME_USA” for the complete template range.
Data Sheet:
Notice the Name Range defined as “Corporate_Budget” for the complete data range. Also the data in Actual and Budget column are having the simple excel formulas linking to the template sheet. We can very well hide the data sheet so that the user only has the template sheet to work with and is not confused by the data sheet.
Once the template is ready we need to publish the template to SharePoint. We can either create a new document library or publish to an existing document library.
*Note: While publishing the excel file to SharePoint we need to explicitly publish the named ranges otherwise it will not be available while creating the “Excel Services” data source connection.
Once the template is published in SharePoint along with the data items ACME_USA (Input Template) and Corporate_Budget (Data Sheet) we can very well use this data to display in SharePoint pages. Lets add a new page to the site and add a Chart Web Part to it.
Once Chart Web Part is added to the page we get the option to Customize the Chart and Connect Chart to Data. We need to click on “Connect Chart To Data”.
Once these steps are followed to get the data we can configure the Series and Axis information for the Chart.
Click on Finish to complete the setup. Similarly we can even include “Excel Web Access” Web Part to display the Input Template that was used while data entry.
Now Business user can anytime open the excel file from SharePoint to update the numbers and those numbers are reflected in the chart.
After this I need to validate is there is a way to post this “Corporate_Data” back to a SharePoint List or External Database or External Web Service.
Technology Discussions by Ajay Singh 
August 23, 2010 at 3:18 pm
This looks great !!!
August 25, 2010 at 7:38 am
Hi Ajay,
Your article is very informative.
I edit my home page and added Excel web access part. I later used it to open my timesheet excel sheet.
All that works fine but I cannot edit my timesheet from sharepoint webpart. Also if I open my sheet in excel and edit it, the chnages are not saved back to my excel sheet sitting in document library.
Can you please tell me if I can do anything to be able to edit my excel sheet from excel web access webpart?
Basically I am trying to implement employee timesheet for all the emplpoyees in my company. I have to produce a prototype in few days. I am thinking if using excel services and web parts is the best approach to use.
Appreciate your reply.
Puja
August 25, 2010 at 8:00 am
Hi Ajay,
I later found that it takes couple of minutes to sync and show updates.
However I would still like to ask if implementing timesheets using excel web access webp[art is a good idea or not.
I am new to SP excel services and web parts.
most of my SP experince lies in workflows usinf Visual studio.
Regards,
Puja
August 25, 2010 at 9:13 am
Hi Puja,
In order to be able to edit the Excel sheet in browser, the very first requirement is installation of OWA (Office Web Applications). This needs to be downloaded separately and is only available to MSDN Subscribers (its just my guess). You can verify it by opening the Document library where you have uploaded the Time Sheet excel file and in the drop down menu you will get the option of “Edit in Browser” along with “Edit in Excel”.
If you want to take this approach for implementing Time Sheet then you will need to create one excel file for each employee (and a separate one for getting consolidated hours) or one excel file with multiple sheets for each employee (chanced of one over righting others data). My recommendation would be to create a List object with all the appropriate columns and provide a simple form for inputting data.
Let me know your thoughts.
August 25, 2010 at 10:42 am
Hi Ajay,
You are right, I donot have OWA installed.
creating a excel sheet for each user wont be tough i think, good thing about excel is that employees r very familiar with it.
If I use one excel sheet per user, how wil I determine a, excel sheet for a user, when he logs in. Basically my Excel web app should know which excel sheet to load when a user logs in.
I think it can be complicated? Do you have any ideas on it?
I also explored the option of using timecardmanagement.wsp which uses a punchin/out mechanism to log times for employees.
I do not understand List object and simple form approach…from list object do you mean custom list and creating a infopath form for it? how can i approach to edit a single infopath form which in turns create a new entry in custom list? I hope my question is not too complicated.
Thanks for replying to promplty.
Regards,
Puja
August 25, 2010 at 10:31 pm
Hi Puja,
Instead of creating something from scratch, I would recommend having a look at this site (http://techsolutions.net/Blog/tabid/65/EntryId/17/Fab-40-Templates-for-MOSS-2010.aspx) which has around 40 templates for SharePoint 2010. In that list the last one you will find is “Timecard Management”.
Hope this will help to address your requirement.
Regards,
Ajay
July 6, 2011 at 1:11 pm
this is good
July 6, 2011 at 10:31 pm
Thanks kk.
August 2, 2011 at 3:03 am
Hi Ajay
When I import data with Date field in it.. it is converting it to Numbers…
For exampel if I have data by 07/2011, 08/2011 it is converting them into numbers when I import..
Help appreciated…
August 12, 2011 at 2:51 pm
Do you have any idea regarding data entry application in sharepoint 2010 using SQL server as backend.
August 13, 2011 at 5:11 pm
SharePoint 2010 itself uses SQL Server as back-end.
October 6, 2011 at 1:02 am
Hi Ajay…This is a wonderful presentation. Thought you could suggest some ideas on what I am trying to accomplish. Sales Managers need to enter quotas for Year 2012. Sales Compensation and Sales Management need to review the numbers. There is communication between the 2 groups to finalize the numbers and then we take those numbers in excel and internally use those numbers. I want to use Excel 2010 for data entry by multiple individuals who can enter their numbers and save their numbers. Is it possible to share this spreadsheet on SharePoint 2010 and then have some reporting so that the managers can see the visualization of their numbers online? Once they enter their numbers, Sales Compensation will take those numbers and approve it for the new year. Thanks.
October 6, 2011 at 9:59 am
Should be possible. We just need to create the appropriate excel files for data entries and then create the data source connection on those files and create the report on that data source. Now the source excel files can be edited by respective parties either in excel or using Office Web Apps (OWA) directly in the browser.
October 20, 2011 at 7:00 pm
Hi Ajay,
Your presentation is very useful to a new use like me. I want your suggestion to accomplished following aim.
I want to display chart on Sharepoint which will reflect Test result which are maintain in Excel. And i want place this excel on sharepoint and want to updated this excel throught CMD using script or code from my remote machine. I divided task in two step
1. To get data on share point in excel.
2 To configure web ex part which will show chart based on data received in 1st.
I get 2nd step but need ur suggest/help for 1st step.
Plesae suggest what are possible way to achieved 1st step.
Thanks..
April 17, 2012 at 7:16 pm
Hi Ajay.
Is there a similar solution for this using Sharepoint 2007?
April 27, 2013 at 6:21 pm
Hi, I think your site may be having internet browser compatibility
issues. Whenever I look at your website in Safari, it looks fine however,
if opening in Internet Explorer, it has some overlapping issues.
I just wanted to provide you with a quick heads up! Apart from that, excellent website!
May 25, 2013 at 2:26 pm
I just could not depart your web site prior to suggesting that I extremely loved the standard
info a person provide to your guests? Is going to be back ceaselessly in order to inspect
new posts
May 28, 2013 at 5:20 am
Hey there! I could have sworn I’ve been to this site before but after browsing through some of the post I realized it’s new to me.
Nonetheless, I’m definitely glad I found it and I’ll be bookmarking and checking back frequently!
August 1, 2013 at 1:15 pm
Hi, all the time i used to check web site posts here early in
the break of day, since i love to learn more and more.
July 1, 2014 at 6:40 am
Wow, fantastic blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your web site is wonderful, let alone the content! eadbkkgedced
July 28, 2014 at 7:29 am
I could nott refrain from commenting. Exceptionaally well written!
August 5, 2014 at 12:00 am
Hello! I’ve been following your blog for some time now and finally got the courage to go ahead and
give you a shout out from Porter Tx! Just wanted to say keep up the fantastic job!
August 5, 2014 at 10:46 am
What’s up, just wanted to say, I enjoyed this blog post.
It was helpful. Keep oon posting!
August 27, 2014 at 6:35 am
Good article! We will be linking to this particularly great article
on our site. Keep up the good writing.
August 28, 2014 at 12:59 pm
I have learn several good stuff here. Definitely worth bookmarking for
revisiting. I surprise how so much attempt you place to create the sort of fantastic
informative website.
August 29, 2014 at 5:39 pm
Aw, this was an extremely nice post. Spending some time
and actual effort to make a top notch article… but what can I say… I procrastinate
a lot and never seem to get nearly anything done.
September 27, 2014 at 8:39 am
It’s amazing to go to see this web page and reading the views of all mates concerning this paragraph, while I am also eager of getting experience.
October 2, 2014 at 9:30 pm
Why visitors stoll make uuse oof to read news papesrs when in this technological globe thee whole thing
is available on net?