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.
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.
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.
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:
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:
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.
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.
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.
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.
Publish to Excel Services
The first version of our workbook is now complete and ready to be published to our SharePoint site:
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.
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:
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.
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.
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:
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.
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.
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.
Share Point 2010 Related Interview Questions
|Web Services Interview Questions||XML Interview Questions|
|Share Point 2010 Interview Questions||ASP.NET Interview Questions|
|Share Point Administration Interview Questions||BizTalk Admin Interview Questions|
|Microsoft Office SharePoint Server (MOSS) Interview Questions||Biztalk Server Interview Questions|
|Asp Dot Net Mvc 4 Interview Questions||Biztalk Esb Toolkit Interview Questions|
|InfoPath Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.