Demonstration Scenario of Excel Services - Share Point 2010

To demonstrate the capabilities of Excel Services, consider the following scenario:AdventureWorks, one of your clients, has implemented SharePoint 2010 as itscorporate intranet platform. To increase awareness of company performance withinthe organization, you’ve been asked to add an indicator of global sales to the frontpage of the intranet site. Since AdventureWorks is a global organization, it shouldbe possible to filter the sales figures by geographic region, and the amounts shownshould be visible in a range of currencies, selectable by the user.It probably comes as no surprise to learn that we can implement this solution usingExcel Services. We can render an interactive chart using the Excel Web Access web part,and if we base the chart on a pivot table, data will be automatically refreshed. To filter thesales figures by geographic region, we can incorporate a slicer into the design, which willallow users to select from a graphical list of available regions.Displaying the results in various currencies is a bit more involved, since the data in theunderlying database is stored in a single currency only. To achieve this, we’ll create a customUDF that will retrieve current exchange rates via a web service call. We’ll then make use ofthat data to recalculate the workbook in the appropriate currency. Because we want to allowthe user to select from a list of currencies, we’ll make use of the JavaScript API to pass in acurrency code selected from a drop-down list. When the currency code is passed into theworkbook via the API, the workbook will be refreshed using the appropriate currency.

Set Up Adventure Works Sample Database

To provide some sample data to work with in our various examples, we need to download and install the SQL Server 2008 sample databases.Our examples make use of the AdventureWorks OLTP database installed on the local instance of SQL Server 2008.

Create a Sample Site

Now we’ll create a new blank site to use for development. Note that if we were creating a complete Business Intelligence (BI) solution or intended to use the site mainly for hosting dashboards, the Business Intelligence Center site template would be a more appropriate place to start. Since we’re using only a single Excel workbook, we’ll make use of a basic blank site.

  1. In SharePoint Designer, choose File | Sites, and then click the New Blank Web Site button..
  2. We’ll add a new document library to host our Excel Workbook. From the Site Objects pane, select Lists and Libraries.
  3. From the ribbon, select Document Library | Document Library. Name the new library Excel Workbooks:

Set Up Adventure Works Sample Database
Create a Workbook for Use with Excel Services
Before we can make use of an Excel workbook in SharePoint 2010, we need to create it using the Excel client application. For our demonstration scenario, we need a simple workbook that contains a pivot table and a pivot chart. The pivot table should be generated from data stored in the organization’s ERP system.

  1. Open Excel 2010. A new blank workbook will be automatically created, and we can make use of this workbook for our demonstration. First, we need to add a data connection so that we can retrieve appropriate sales data. On the Data tab, in the Get External Data section of the ribbon, select From Other Sources | From Data Connection Wizard, as illustrated:
  2. Create a Workbook for Use with Excel Services

  3. In the Data Connection Wizard dialog, accept the default selection of Microsoft SQL Server by clicking Next to proceed.
  4. In the Server Name text box, enter .SQLExpress. For the Log On Credentials, select Use Windows Authentication. Click Next.
  5. In the Select Database and Table step, change the selected database to AdventureWorks, and uncheck the Connect To A Specific Table checkbox as shown. Click Finish to complete the wizard.
  6. Create a Workbook for Use with Excel Services
  7. After the wizard has completed, the Select Table dialog is displayed. Although the data that we require comes from more than one table, we need to select a table to create a connection in our workbook. Select the SalesOrderHeader table, as shown, and then click OK.
  8. Create a Workbook for Use with Excel Services
  9. The Import Data dialog allows us to select what we want to do with the connected data and includes a few options, such as Table and PivotTable as well as a range selector that we can use to specify where the data should appear in the workbook. When using Excel Services, you should be aware that only PivotTables can be refreshed on the server. Although it is possible to add a table and use the data within the table in Excel Services, the only way to refresh the table data will be to open the spreadsheet in Excel and refresh manually. Bearing this in mind, select PivotTable Report and put the data at the default location of $A$1 in the current worksheet.

Configure a Data Connection

A new PivotTable is inserted on the page at the specified location. Before we start configuring the PivotTable, we need to review our connection settings. Recall that we selected the SalesOrderHeader table as the source of our data; before we can set up our pivot table, we need to change this to use a SQL query instead.

  1. From the Data tab, select the Connections option. In the Workbook Connections dialog, select the ._sqlexpress AdventureWorks connection. Notice that your connection may have a slightly different name, depending on your database server and whether an existing connection with that name already exists.
  2. Click Properties to view the connection details. Change the connection name to AdventureWorksLast30DaysSales.
  3. Click the Definition tab to see details of the connection string, the command type, and the command text as well as a few other options. Change the Command type to SQL and enter the following SQL statement in the Command Text text box:
  4. SELECT H.OrderDate, T.Name as Territory, T.CountryRegionCode as CountryCode, sum(TotalDue) as TotalSales FROM [Sales].[SalesOrderHeader] as H INNER JOIN [Sales].[SalesTerritory] as T ON H.TerritoryID=T.TerritoryID WHERE H.OrderDate>'2004-07-01' GROUP BY H.OrderDate, T.Name, T.CountryRegionCode
  5. When a data connection is used by Excel, a copy of the connection information is stored in the workbook. In the Properties dialog, we’re effectively editing the properties of this cached copy of the data connection. To update our locally saved connection file, click Export Connection File and then, in the file dialog that appears, type the filename as AdventureWorksLast30DaysSales.odc. Click Save to create the new Office Database Connection file.
  6. Click OK to close the Properties dialog, and then click Close to close the Workbook Connections dialog. Notice that the fields listed in the PivotTable Field List have changed to match those in our amended query.

Configure a PivotTable to Act like an External Data List

PivotTables are a great help for analyzing a data set interactively. We can easily add in row headers or columns headers or formulas and grouping to the data. Sometimes we don’t need to do any of that clever stuff, though; we might want a simple list of the data as it looks in the database. In Excel client, we could of course achieve such a result by creating an External Data List as opposed to a PivotTable. However, External Data Tables aren’t supported in Excel Services, so we’re stuck trying to reign in the analytical faculties of the PivotTable to produce a more sedate output. To create a PivotTable that behaves in a similar manner to an External Data List, take the following steps:

  1. From the PivotTable Field List, drag OrderDate, CountryCode, and Territory into the Row Labels section. Drag Sum of TotalSales into the Values section, as illustrated:
  2. From the PivotTable Tools tab, select the Design menu. In the Layout section of the ribbon, select Report Layout | Show In Tabular Form. Again from the Layout section, select Report Layout | Repeat All Item Labels.
  3. The resulting PivotTable is starting to look a bit like a data list. We can now remove the total rows by selecting Subtotals | Do Not Show Subtotals from the Layouts section of the ribbon.
  4. To remove the +/– buttons, open the Options menu from the PivotTable Tools tab. Click the +/– button on the Show section of the ribbon to toggle the buttons off.

Using Named Ranges in Excel Services

You may be wondering why we had to go to the trouble of changing our PivotTable to a flat data list. It’s fair to say that, generally speaking, we wouldn’t normally need to take this step when using data in Excel Services, but this case is a bit different. The TotalSales value retrieved from the database represents the sales value in US dollars (USD). However, our demonstration scenario requires us to be able to present this data using a variety of currencies. So that we can convert this value to a different currency, we need to use a formula, and formulas within PivotTables are limited to include only data from within the PivotTable. In our case, the exchange rate value that will be used by our formula will be stored elsewhere in the workbook, so using a PivotTable formula isn’t an option. We can achieve our desired outcome by flattening our PivotTable and then adding appropriate formulae in adjacent cells. Let’s move on to add a few named ranges that will be used on our calculation logic:

  1. Navigate to Sheet2 in the Excel workbook. We’ll use this sheet to store the values required by our exchange rate calculation.
  2. In cell A1, type Exchange Rate. In the adjacent cell (B1), type the number 1. We’ll come back to this later when we create a UDF. With the cell B1 selected, in the Name box, enter ExchangeRate, as illustrated:
  3. Using Named Ranges in Excel Services

  4. In cell A2, type Currency Code. In the adjacent cell (B2), type USD. With cell B2 selected, in the Name box, type CurrencyCode.
  5. In cell A3, type Chart Title. In the adjacent cell (B3), add the following formula: ="Last 30 Days Sales in " & CurrencyCode When completed, the first few cells of Sheet2 should look like this:

Using Named Ranges in Excel Services

Perform Calculations Using PivotTable Values

Now that we’ve defined the parameters for our exchange rate calculation, we can add the necessary formulae to Sheet1.

  1. Switch back to Sheet1. In column E, cell E1, add header text SelectedCurrencyValue.
  2. In cell E2, add this formula: =GET PIVOTDATA ("TotalSales", $A$1,"OrderDate", A2,"Territory", C2,"Country Code", B2)*ExchangeRate
  3. This formula extracts the value of the TotalSales column from the PivotTable, where the OrderDate, Territory, and CountryCode columns match the values contained in cells A2, C2, and B2. In plain English, the formula returns the TotalSales value for the current row.

  4. Since we want to perform this calculation for each row in the table, we need to use this formula in every cell down to the bottom of the table. To do this, type E2:E206 in the Name box, and then press ctrl-d. Alternatively, we can manually select the cells in question and then click Fill | Down from the Editing section of the Home ribbon.
  5. NOTE Using formulae in this manner requires special consideration when the PivotTable referenced will be periodically refreshed. If, during a subsequent refresh, the Pivot Table ends up with a different number of rows, the formulae will not automatically be filled down to accommodate the growth of the table. It is important that you ensure that the size of the returned dataset remains constant, and generally this can be done using Transact- SQL (T-SQL) or by calling a stored procedure to produce the required data.

  6. Since we’ll use the data contained in the PivotTable and our calculated column later, we’ll give it a name for ease of reference. Either manually highlight the cells in the range A1:E206 or enter the range in the Names box. Once the range is highlighted, type SourceDataTable. Sheet1 should now look like this:


Add a PivotChart

Now that we’ve created a data source that can be automatically refreshed by Excel Services, we can move on to create a chart based on the source data. We’ll render the chart on our web page to provide a graphical representation of the sales data.

  1. Select Sheet3. We’ll use this sheet to contain the elements of our workbook that will be rendered on our sample site. Choose Insert | PivotTable | PivotChart.
  2. Add a PivotChart

  3. In the Create PivotTable with PivotChart dialog, type SourceDataTable as the Table/Range:
  4. From the PivotTable Field List, drag OrderDate into the Axis Fields section, CountryCode into the Legend Fields section, and SelectedCurrencyValue into the Values section. The field lists should look as shown:
  5. Add a PivotChart

  6. Our chart is automatically generated based on our PivotTable data. However, the default clustered bar chart type doesn’t make it easy to visualize our data set, so let’s change this to something more appropriate. From the Design menu, select the Change Chart Type button. In the Change Chart Type dialog, select the Stacked Area chart type.
  7. To add a title to our chart, select the Chart Title button from the Layout menu. Since we want our chart title to be automatically generated based on the currency code selected, we can add the following formula: =Sheet2!$B$3

Publish to Excel Services

The first version of our workbook is now complete and ready to be published to our SharePoint site:

  1. In Excel, click the File menu to enter the backstage area. Select Share from the list of options and then select Publish to Excel Services.
  2. Set the path and the filename to Last30DaysSales.xlsx.
  3. Click to save the file to SharePoint.

TIP When using Excel 2010 on Windows 2008 server, trying to save files to SharePoint doesn’t quite work as it should. This is because the WebClient service that maps the SharePoint URL to a UNC path behind the scenes, isn’t configured by default since it has no purpose on a server operating system. To fix this problem, install the Desktop Experience feature using Server Manager.

Create a User Interface Using the Excel Web Access Web Part

Now that we have our workbook published to SharePoint, we can move on to make use of it when generating a user interface for our sample application. We’ll customize the homepage of our site to include our sales chart.

  1. Navigate From the Site Actions menu, choose Edit Page.
  2. In the Left Web part zone, click Add A Web Part.
  3. Select the Excel Web Access (EWA) web part from the Office Client Applications category. Click Add to include the web part on the page.
  4. To set the properties of the web part, click the Click Here To Open The Tool Pane link.
  5. In the Workbook Display section, type the workbook as /Chapter12/Excel Workbooks/
  6. Since we’re interested only in the chart for now, in the Named Item field type Chart 1. Click Apply to see the results.

We’ve now got our PivotChart on the page ready for use. Let’s tidy up a few of the remaining web part settings to give the page a more integrated look:

  1. Set the Type of Toolbar to None. This will hide the toolbar that appears above the chart.
  2. In the Appearance section, set the Chrome type to None.
  3. Click OK to commit the changes and stop editing the web part.
  4. From the Page ribbon, click the Stop Editing button to switch back to View mode.

Adding Interactivity Using a Slicer

You’ve seen how easy it is to make use of Excel data on a page when using the Excel Web Access web part. Let’s move on to look at an example of the interactive features available via the web part. Our demonstration scenario requires that the data displayed in our chart be filterable using geographical locations. Although we have listed multiple series, one for each country code, at the moment we don’t have any way to select which series should be displayed on the chart. This section introduces the Slicer, one of the new features available in Excel 2010 that works very well with Excel Services. Before we can use a Slicer, we need to add it to our Excel workbook.

  1. Navigate to the Excel Workbooks document library. Open the Last30DaysSales.xlsx file using Microsoft Excel by right-clicking the file and selecting Edit in Microsoft Excel from the context menu.
  2. Note that the workbook opens in Protected View mode. This happens because the workbook is opened from a web site as opposed to a local folder. Click Enable Editing to proceed.
  3. The next warning we receive says that “Data Connections have been disabled.” This is a standard security feature of Excel that prevents active content from running until it is explicitly allowed. Click Enable Content to refresh the connected data.
  4. We have the option to make the document trusted. Click Yes, and the workbook won’t open in Protected View mode each time. This is OK since we’ll be doing a bit of work with it.
  5. Adding a Slicer is simple. Select the PivotChart and then, from the Analyze menu, click the Insert Slicer button.
  6. From the Insert Slicers dialog, check the Territory checkbox and then click OK to add the Slicer to our worksheet.
  7. To see the Slicer in action, try selecting one or more (by holding down the ctrl key while clicking) of the listed Territory values. We can see that the PivotTable data is filtered based on our selection and the PivotChart is redrawn accordingly.

Grouping Excel Items for Display Purposes

Since we want to display only the Slicer and chart on our web page, we need to lay them out in a specific manner. You’ll remember that when we configured the EWA web part earlier in the chapter, we entered a specific named item to display—Chart 1. We now need to display the chart and the Slicer, and since we can enter only one named item, we need to group these into a single item. As you’ve seen earlier, named ranges can be defined by selecting a range of cells and then adding a name. We’ll use a named range to refer to our chart and Slicer control.

  1. Place the chart and the Slicer next to each other on the sheet.
  2. Resize the chart and the Slicer so that they fill entire cells as much as possible. This will reduce unnecessary white space when the control is rendered in the web page. The zoom function is very useful for this purpose.
  3. Select the underlying range using one of the methods described earlier and type the name ChartAndSlicer in the Name box.
  4. Click the Save icon in the upper-left corner to save the changes to SharePoint. We’ll keep the workbook open for now since we’ll be making additional changes later. If we now open the home page of our sample site using Internet Explorer, unsurprisingly we’ll find that our chart is still there just as before, without our new Slicer control. One thing that may be apparent is that the chart now shows only the territories that we selected earlier when we were messing around with the Slicer in Excel client. Note that slicer selections are published along with the workbook, so it’s important to make sure that an appropriate default is set before saving. Let’s move on to change our Excel Web Access web part to display our Slicer as well as our chart.
  5. From the SiteActions menu, select Edit Page.
  6. Highlight the Excel Web Access web part by clicking the header; then, from the Web Part Tools tab, select Web Part Properties from the Options menu.
  7. Change the Named Item to ChartAndSlicer. Click Apply to view the change.
  8. Our recently defined named item should be displayed on the page, but, instead, we’re presented with the following error message stating that the named item cannot be displayed:

    Excel Web Access

  9. Click OK to acknowledge the error message. Then from the Page menu, select Stop Editing. The page will now be blank except for the error message.

Change Published Items Within a Workbook

When we simply gave it a name and accepted the default values. Whenever we click the Save icon, rather than re-publishing the workbook, we’re merely saving the data back to the document library. The significance here is that when publishing a workbook to Excel Services, we have the option of specifying additional metadata, but when saving, the metadata is not changed. We received the error because the metadata did not contain details of our new named item.

  1. Switch back to Excel client. Click the File menu to enter the backstage area. Select the Share option, as shown:
  2. Change Published Items Within a Workbook

  3. The Share section offers two options: Save to SharePoint and Publish to Excel Services. As described, the difference between these two options is the ability to add metadata that’s useful to Excel Services. Let’s see what that means in practice. Click Save to SharePoint.
  4. Click the Current Location icon to open the Save As dialog, which automatically displays the contents of our Excel Workbooks document library and allows us to save the workbook in the normal manner. Click Cancel and then return to the Share section of the backstage area.
  5. This time, click Publish To Excel Services to open the Save As dialog, but notice that an Excel Services Options button now appears in the bottom section of the dialog.
  6. Click the Excel Service Options button to define or overwrite metadata for the document. In the Excel Services Options dialog’s Show tab, select Items In The Workbook from the drop-down list.
  7. Check the All Named Ranges and the All Charts options to ensure that they will be available for use by the EWA web part.
  8. NOTE At the time of writing, a bug (or feature, depending on your point of view) exists within Excel 2010. Named ranges that are blank are not detected by the Excel Services publishing process and therefore don’t appear in the list of Items in the workbook. To resolve this issue, select the ChartAndSlicer named range and press the SPACEBAR. This will ensure that the range appears in the list of metadata.

  9. Click Save to complete the publishing process.
  10. With our metadata updated appropriately, if we return to the sample site home page, we can see that our EWA web part now displays our chart and Slicer as expected. The Slicer behaves in much the same manner as we saw earlier when we used it within the Excel client application.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Share Point 2010 Topics