Publishing an Excel workbook to a SharePoint 2010 farm, from start to finish

Introduction

This posting demonstrates, from start to finish, the process of:

1. Creating an Excel workbook that connects to external data using Windows authentication;
2. Modifying the external data connection to focus on specific data;
3. Configuring the PivotTable;
4. Configuring a PivotChart; and
5. Publishing the workbook to SharePoint Server 2010 Excel Services.

The target web application used in this demonstration hosts a single site collection.  By working through all of the steps of this posting, you will gain experience in configuring an Excel 2010 workbook to connect to external data and then publish this workbook to SharePoint Server 2010 Excel Services.  This posting assumes that the Excel Services application has already been started.  This posting integrates the discussions presented in many of the references listed below.

Preparation

1. Add a blank site to the target web application, naming it AdventureWorks:
   
2. Configure the site to inherit global navigation from the parent:
   
3. Add a document library to this site, naming it Workbooks:
   
4. Restore the database, AdventureWorks2008R2Full Database Backup to a SQL Server instance hosted within the same domain as the SharePoint Server 2010 farm:
   

Step 1: Create an Excel workbook and connect it to external data

1. Launch Excel 2010 on a workstation hosted within the same domain as the target SharePoint Server 2010 farm, naming the new workbook SalesOrderHeader.
2. On the Data ribbon, go: Go External Data > From Other Sources > From Data Connection Wizard:
   
   This opens the Data Connection Wizard.
3. Select Microsoft SQL Server:
   
4. Click Next.  Enter the name of the SQL Server Database instance hosting the AdventureWorks database, and then select Windows Authentication:
   
5. Click Next.  From the dropdown, select the table, SalesOrderHeader:
   
   It's located towards the bottom of this listing.  This listing cannot be sorted.
6. Click Next.  Enter values for File Name, Friendly Name and Search Keywords; and leave the checkbox unchecked:
   
   Leaving the checkbox unchecked causes the data connection to be embedded in the Excel file itself.  Checking this option causes the connection data to be stored in and used from an external data connection (odc) file.
7. Click the Authentication Settings button.  Ensure that Windows Authentication has been selected:
   
8. Click OK.  The Excel Services Authentication Settings dialog closes.
9. Click Finish.  The Data Connection Wizard dialog closes, the Import Data dialog appears:
   
10. Select PivotTable Report.  Only pivot tables can be refreshed on the SharePoint Server, once published and viewed by a user.
11. Click OK.  The Import Data dialog closes, and the spreadsheet is populated with the data from the AdventureWorks table:
    
12. This completes this step

Step 2: Modify the workbook's external data connection

1. On the Data ribbon, look for the Connections button:
   
2. Click Connections. The Workbook Connections dialog appears:
   
3. Click the Properties button.  The Connection Properties dialog appears:
   
4. Select the Definition tab.
5. From the Command type dropdown, select SQL.
6. In the Command text box, delete the contents, and replace with the following:
   
   The Definition tab will then look like this:
   
7. Click the Authentication Settings button.  The Excel Services Authentication Settings dialog appears.
8. Select Windows Authentication:
   
9. Click OK.  The Excel Services Authentication Settings dialog closes.
10. Click where it states, Click here to see where the selected connections are used.  This updates what is displayed in the bottom panel with the workbook location of the data obtained from the external data connection:
    
11. Click Close.  The Excel workbook sheet pivot table updates and now displays fewer fields in the PivotTable Field List panel:

12. This completes this step 2.

Step 3: Configure a PivotTable

1. Drag-and-drop the fields, in the Pivot Field List, as shown:
   
2. On the Design ribbon, click Report Layouts and then point to Show in Tabular Form:
   
3. Click this option.  The pivot table updates to display the data in tabular form:
   
4. On the Options ribbon, in the Show section, click the +/- button.  The pivot table updates and no longer displays the show/hide (+/-) button.
5. On the Design ribbon, click Report Layout, and then select Repeat All Item Labels. The spreadsheet updates to repeat the labels shown in the CountryCode column.
6. On the Design ribbon, click Subtotals, and then point to Do Not Show Subtotals:
   
7. Select this option.  The pivot table updates and no longer shows subtotals:
   
8. At the top of the column to the immediate right of the Sum of TotalSales column, enter the column header SelectedCurrencyValue:
   
9. Click once anywhere in the columns at left, to bring back the Pivotable Field List panel, and then click the small dropdown on the OrderDate button, which brings up a popup menu:
   
10. Select Move Up.  The PivotTable updates to now display the OrderDate column first:
    
11. In the field just below the new SelectedCurrencyValue header, enter the following:

    =GETPIVOTDATA("TotalSales", $A$1, "OrderDate", A2, "Territory", C2, "CountryCode", B2)*ExchangeRate
    

12. The order of the field names in this function is critical.  They must be exactly in the same order as the columns in the PivotTable.
13. Press Enter.  The cell updates to display the function value:
    
14. Scroll down to the bottom of the PivotTable and note down the row number for the last row of data.  For this posting, the row number is: 5832.  Then scroll back to the top.
15. In the Name field, enter E2:E5832.  This causes the enter column to be selected (except for the header).
16. Press CTRL+D.  This causes the function in E2 to be repeated for all of the other cells in this column:
    
17. In the Name field, enter A1:E5832, and then press Enter.  This causes all of the data rows and the header row to be selected:
    
18. In the Name field, overwrite any value there with the value SourceDataTable, and then press Enter.  This assigns a name to all that was selected, effectively creating a source of data:
    
19. Select Sheet2.
20. In cells A1 and B1 enter these values:
1. Exchange Rate.
2. 1.
21. Select cell A2, and then in the Name field (currently displaying A2), overwrite this with ExchangeRate:
    
    This assigns that cell a variable name.
22. In cell A2 and B2, enter these values:
1. Currency Code.
2. USD.
23. Select cell B2, and then in the Name field (currently displaying B2), overwrite this with CurrencyCode:
    
24. In cell A3 and C3, enter these values:
1. Chart Title.
2. ="Sales in " & CurrencyCode.
25. Select cell C3, and then in the Name field (currently display C3), overwrite this with ChartTitle:
    
26. To review all of the cell and table variables that have been created thus far, on the Formulas ribbon, in the Defined Names section, click Name Manager.  The Name Manager dialog appears:
    
27. This completes Step 3.

Step 4: Configure a PivotChart

1. Select Sheet3.
2. On the Insert ribbon, click PivotTable.  The Create PivotTable with PivotChart dialog appears:
   
3. Enter the name for the source data table created previously, SourceDataTable, and then click OK.  The sheet update to display a blank PivotTable, PivotChart and PivotTable Field List panel:
   
4. On the PivotTable Field List panel, drag fields into the Axis Fields, Legend Fields and Values boxes like so:
   
5. Then move the chart and resize it so that it fills the viewable area:
   
6. Select the chart.  This activates the PivotChart Tools ribbons.
7. On the Layout ribbon, click Chart Title, and then point to Above Chart:
   
8. Click this option.  A title field is added to the chart, displaying a default value:
   
9. Select the chart title field, and then enter the following into the Formula bar:

   =SalesOrderHeader.xlsx!ChartTitle
   

   
10. Select the chart to activate the Chart ribbons.
11. On the Design ribbon, click the Change Chart Type button.  The Change Chart Type dialog appears:
    
12. Hover the cursor over the various images displayed in this dialog to view the names of the different chart types.
13. Select the Stack Chart type, and then click OK.  There will appear to be little noticeable difference in the chart, but this is due to the amount of data currently displayed by the chart, which tends to obscure such things.
14. On the Design ribbon, in the Chart Styles section, expand the chart styles panel and then point to Style 16:
    
15. Click on Style 16.  Again, there will be little noticeable change in the chart currently displayed.  However, this change will become apparent, when zooming into date ranges using the filter options.  Additionally, these chart options must be set now before publishing to the SharePoint Server instance, as, once published, they cannot be changed from the web interface.
16. This completes Step 4.

Step 5: Publish the workbook to SharePoint Server 2010 Excel Services

1. On the Excel UI, select the File tab, at far left.
2. On the File tab, go: Save & Send > Save to SharePoint:
   
3. Double-click Browse for a location. The Save As panel appears.
4. In the File Name field, enter the path to the document library created in step 1:
   
5. Press Enter.  After a few moments, the Save As dialog updates to display the SharePoint Server 2010 document library location.
6. Enter the name of the Excel 2010 workbook, SalesOrderHeader:
   
7. Be sure to also check: Open with Excel in the browser.
8. Press Enter. A progress bar appears momentarily.  Then a new browser instance is launched and connected to the web-enabled workbook displaying a warning message:
   
9. Click Yes. After a few moments, the browser displays the web-enabled workbook:
   
10. Click the Row Labels dropdown, point to Date Filters and then point to Between:.
11. Click Between.  A Custom Filter dialog appears.
12. Enter the between dates 7/3/2005 and 7/30/2005:
    
13. Click OK. The chart updates to display a filtered date-range of values:
    
14. Click the Column Labels dropdown, and then point to Filters:
    
15. Click Filters.  The Filter dialog appears.
16. Uncheck the CA and US items:
    
17. Click OK.  The chart updates to display just the sales for GB, FR, DE and AU:
    
18. This completes Step 5.

Optional: Configure Session Timeout

1. Launch Central Administration.
2. Go: Central Administration > Application Management > Manage Service Applications > Excel Services Application > Trusted File Locations:
   
3. Click on the http:// link listed there.  The Edit Trusted File Location page is displayed.
4. Scroll down to the Session Management section.
5. Change the Sessions Timeout and Short Session Timeout values to 20 minutes (1200 seconds):
   
6. Click OK.

Summary

This posting has presented the step-by-step process for creating, configuring and publishing an Excel 2010 workbook to SharePoint's Excel Service in order to make it generally available to other users.  The external connection used by this workbook is embedded in the workbook itself.  By working through this posting, one gains experience in how to configure an Excel workbook to connect to external data and in how to publish a workbook to SharePoint.  For additional detail on any of the topics discussed in this posting, pleased consult the references below.

References

• TechNet Library SharePoint 2010
• Configure Excel Services for a BI test environment
• Chapter 12: Excel Services (SharePoint 2010 Web App The Complete Reference)
• Configuring a BI infrastructure: Hands-on labs
• Configure Excel Services Application settings (Office Web Apps)
• What's new for Excel Services (SharePoint Server 2010)
• Excel Services administration (SharePoint Server 2010)
• Configure Excel Services data refresh by using the unattended service account (SharePoint Server 2010)
• Plan Excel Services authentication (SharePoint Server 2010)
• Manage Excel Services trusted locations (SharePoint Server 2010)
• Al's Tech Tips
• Build a SharePoint Server 2010 Two-Tier Dev Environment on Windows 2008 R2
• Microsoft Office
• Publish a workbook to a SharePoint site
• Differences between using a workbook in the browser and in Excel
• Refreshing data in a workbook in a browser window
• Refresh external data in a workbook in the browser
• Business intelligence in Excel and Excel Services (SharePoint Server)
• Sessions and session time-outs in a workbook in the browser
• Blogs
• Uncovering Publish to Excel Services in Excel 2010
• Using Office Data Connection files (.odc) and the DataConnections Web Part in SharePoint to Specify External Data Connections in Newly Created Excel Workbooks
• Excel Services delegation
• SQL Server Database product Samples
• AdventureWorks2008R2-Full Database Backup.zip

Notes

• By default, Session Timeout is 450 seconds, or 7.5 minutes. Longer session times can be configured through the Excel Services Application.