How to do Calculations in Excel Sevices?

Microsoft Office Excel 2007 allows to specify different calculation formulas in a workbook.
Once the formula is set up in a workbook and published to sharepoint Excel Services,Excel Calculation setting is supported and retained in the workbook.
To perform the calculation or recalculation:
1.    open the appropriate webpart page .
2.    Open the workbook in excel web accesss webpart
3.    On the webparts toolbar,click the update menu and then click the Calculate workbook
4.    Repeat the steps to recalculate

What are the steps to Publish Excel Workbook to sharepoint?

Connect the Web Parts by using a document URL
1.    Add the List View Web Part of the document library and the Excel Web Access Web Part to a Web Part Page. For more information, see List View Web Part and Add or remove a Web Part.
2.    On the Site Actions menu  , click Edit Page.
3.    From the Excel Web Access Web Part, click the Web Part menu  , point to Connections, point to Get Workbook URL From, and then click the appropriate List View Web Part.
4.    In the Configure Connection dialog box, select Document URL from the Field Name list box, and then click Finish.
5.    To exit Web Part Page Edit Mode and view the page, at the top of the Web Part Page, under the Site Actions  drop-down menu, click Exit Edit Mode.
6.    To view each workbook in the Excel Web Access Web Part, click the option button next to the workbook item in the List View Web Part.
Connect the Web Parts by using a named item
1.    Add the List View Web Part of the list that contains the named items and the Excel Web Access Web Part to a Web Part Page. For more information, see List View Web Part and Add or remove a Web Part.
2.    On the Site Actions menu  , click Edit Page.
3.    On the opening screen of Excel Web Access, click the link, Click here to open the tool pane.
The Web Part enters Edit Mode and the Web Part Tool Pane is displayed.
4.    Enter the URL or UNC of the workbook that contains the named items in the Workbook text box.
To easily locate a workbook, click Select a Link  and use the Select a Link dialog box.
5.    From the Excel Web Access Web Part, click the Web Part menu  , point to Connections, point to Get Named Item From, and then click the appropriate List View Web Part.
6.    In the Configure Connection dialog box, select Title from the Field Name list box, and then click Finish.
7.    To exit Web Part Page Edit Mode and view the page, at the top of the Web Part Page, under the Site Actions  drop-down menu, click Exit Edit Mode.
8.    To view each named item in the Excel Web Access Web Part, click the option button next to the named item in the List View Web Part.

How to create a Pivot Table in Sharepoint?

Pivot Tables are one of the most powerful features that Microsoft has ever developed for MS Office. Initially it only formed part of Excel, but due to it's popularity it was eventually converted to a "Service" that could be used in Word , Access and many other applications. With the advent of "Excel Services" in MOSS 2007 Enterprise edition, we can now use pivot tables in Sharepoint, running server side.
Before we can view a pivot table embedded as a web part in a Sharepoint site, we first have to create one in MS Excel 2007 and then save the spreadsheet into a document library. The initial steps would therefore be as follows:
1.    Create a site in Sharepoint
2.    Create a Document Library to store the spreadsheet
3.    Create a "New" spreadsheet from the document library

To simulate a proper use of a "Pivot Table" in an enterprise environment I am going to use a back end database as the source of the spreadsheet. Once the spreadsheet has opened in MS Excel, navigate to the data tab on the ribbon and select "From Other Sources".

There is a whole range of data source options to choose from and in a perfect world everyone would be running some form of BI cube on "Analysis Services", but unfortunately that is not the case. I will therefore be using a more realistic example like a SQL Server database. Once you selected the option, a wizard will start the will guide you through the rest of the process.

Step1: Specify the SQL Server name that you will be connecting too and the Log-on credentials.

Step 2: Select the database of your choice from the drop down list and then select the table or view that will be the source.

Step 3: Note that the connection string will be stored as a ODC file and will be automatically uploaded to Sharepoint when we publish. What we need to do now is look at how the users will be authenticated. Select the button at the bottom of the screen called "Authentication Settings".

Step 4: Once the Pivot Table is published to Excel Service in Sharepoint the authentication will be done server side. The default option is NTLM, but the SSO (Single Sign-on Service) can also be used if it has been configured. "None" can only be used if the data is already in the spreadsheet itself. Select "OK" to close this dialogue box and then select "Finish".

Step 5: The final part of the wizard is used to determine how and where you would like to display the data retrieved from the database. In this case we want a "Pivot Chart and Report" and the location can be any cell in the current sheet. Click "OK"
Using the "Pivot Table" wizard in Excel, build a Pivot table to display some data. I am assuming that you know how to do this, otherwise why are you reading this blog. Once you are done, it should look something like this:

Now that the Pivot Table is complete, we need to publish it to Excel Services running on Sharepoint.

To do this , select the "Pearl" in to the top left hand corner of Excel, navigate down the list to an option called "Publish" and then select "Excel Services". This will open the "Save As" dialogue box with some additional options.

The spreadsheet will automatically attempt to save back to the document library from where it was initiated. Give to spreadsheet a name, but don't click save yet, there are some additional options that need to be configured. Select the "Excel Services Options" button.

In the drop down list on the Show tab, select the option called "Items in the Workbook". Then select the checkboxes next to the "Chart" and "Pivot Table" objects. We only want to publish these objects to Excel Services, not the entire workbook or sheets. Click "OK and then "Save". Close Excel and navigate to the site in Sharepoint.
Select "Edit Page" from the "Site Actions" menu and then select "Add Web Part" in a Zone of your choice. This action will then launch the "Web Part Gallery" selection screen.

The web part that we are interested in is called "Excel Web Access". Select the web part from the list by selecting the checkbox and then click "Add". This will then snap the web part into the zone. Once the web part appears, you will notice that it has a hyperlink called "Click here to open tool pane". By selecting this option a tool pane will open on the right hand some with a whole set of configuration options.

One of the options is called "Workbook" with a browse button located next to it. Browse to the spreadsheet that we created earlier and saved to a document library. Once this is done, select "OK" and exit edit mode on the page.

The pivot table should then appear displaying the "Chart" by default. You can toggle between the chart and pivot table by selecting them from the "View" menu on the toolbar. All the filtering capabilities are also available as if you were working in Excel.
The data from the database can also be refreshed by selecting "Update" from the menu.
This is one of the core components used to build Digital Dashboards and it is a vast improvement on Office 2003 OWA (Office Web Components) that use to download as Active X Controls.

What is Pivot Table?

Excel has a feature called Data Validation that controls the possible values a user might enter into a cell or a range of cells.

As you can see there are many options for validating data entry. The most popular is probably validating against a list of values. The list can be included in the validation definition or can be a region in the sheet referenced from the dialog.
Recently I saw an example created by a customer that validates input using a PivotTable instead of data validation. The idea is to use only the report filter area of the PivotTable and to use the values selected in multiple filter fields as inputs for a model:

In the example you see multiple input fields and when clicking the filter icon the user will see a list of values and can choose one.
The advantage of this method is easier maintenance of multiple lists of values used for validating multiple fields. The range that the PivotTable is based on looks a bit odd because usually PivotTables are based on rectangular ranges and not something that looks like this:

In order to avoid the "(blank)" value appearing in the PivotTable drop-down list of values, fill the last value in each category all the way down to the last row of the region:

Use of PivotTable for validation in Excel Services
So far I explained how a PivotTable could be used as a validation method in Excel. The impact of using this technique is not significant until we apply it to Excel services. Excel services does not support data validation and any workbook containing data validation will not be loaded by Excel Calculation Services (ECS). In addition, entering inputs to a model in Excel Services is not as user-friendly as Excel since values cannot be entered directly into cells. Using PivotTable filters for validation is fully supported in Excel Services and can solve these two problems. We have a friendly way to input values into a model and also to apply a validation against a list of values. The same example might look like this in the browser:

So if you are looking to build a spreadsheet that can be consumed by Excel Services and provides user-friendly, validated, input, give the PivotTable report filter a try.

Differentiate between Snapshot-vs-Open in Excel?

Let me begin by explaining the two concepts first. When you load a workbook on Excel Services, you are given two options to open the workbook on Excel.
1.    Snapshot: This provides users a copy of the workbook with all the formulas behind the numbers stripped out.
2.    Open in Excel: This provides users with a copy of the original workbook. Changes to the read-only version of this workbook will not appear in the original workbook on Excel Services.

What the Diff?
•    Snapshot has all the formulas behind the numbers stripped out.
•    Some features within snapshot are stripped out: Conditional Formatting

Why have Snapshots?
Security! Again, Snapshot is another excellent example of how Excel addresses security. This technology will allow authors of these workbooks to prevent readers from surfacing formulas (which could be considered confidential).
Business Scenario
Tracy is a Sales Manager that works for a software consultation company. One of her primary tasks is to conduct client visits and advertise how their company could benefit their client. In order to convince her clients of the benefits of working with her company, she would like to provide each client with a workbook indicating how other company's have benefited from using her company's products. This Excel Services workbook contains a chart indicating that their previous anonymous client have reduced their IT expenditure by 35% over 8 months after using their product. However, Tracy does not want to reveal how the number 35% was derived (via external data or other manners). As a result, she provides each potential client with a Snapshot of the original workbook.

What is snapshots?

A snapshot is a restricted view of a workbook with only some of the spread sheets information saved to the local computer.
The reason for using snapshot over a workbook is that only the values within each cell are made available to end users.They are unable to see any of the formulas.

What is Volatile Function Cache lifetime?where we can find it?

You can find this setting on the Trusted Location administration page under the "Calculation Behavior" section.
This setting governs one thing - the amount of time Excel Services will wait before recalculating sheets that contain volatile information when a new session is opened. This last bolded part is where people sometimes misunderstand what the setting is about exactly.
Excel Services recieves an OpenWorkbook() request in the following cases:
1. When a user causes the EWA (Excel Web Access) Web part  to open a new session (this happens the first time a user accesses a workbook on a certain EWA, when the session times out and the user requests it be reopened or when the user selects the "Reload workbook" option in the EWA drop-downs).
2. When a user of the Excel Web Services calls the OpenWorkbook() API method.
When this type of request is received by Excel Services, it first checks to see if the workbook is already loaded (and since it shares out loaded workbooks, it usually just serves those copies up immediatly). If, however, the workbook contains a volatile function in it (for example, =RAND() or =NOW()), Excel Services also checks to see when the last time the shared copy was recalculated. If that last time was more than what the VFCL is set up as, Excel Services will cause an implicit recalculation of the workbook and serve up the new calculated version as the result.
What this means for you:
1. If your workbook has volatile function calls in it and you want them to update, it's not enough to call one of the GetXXX() functions - you need to actually cause a recalculation first. This can be done by simply calling the .CalculateWorkbook() method. Once that request comes back, the workbook has been recalculated and the cells that contain volatile functions should display the most up-to-date values.
2. If you have a trusted location that contains a workbook with calls to volatile functions and you want that workbook to update frequently when people open it up for viewing, you can reduce the VFCL setting to whatever number suites your scenario.