Exercise 2: Performing Analysis with Excel and Excel Services

In this exercise you will work with the Microsoft Office Excel 2010 client application. Throughout this exercise you will utilize the Excel client integration points in SharePoint sites and Excel Services.

1. In the browser, navigate to the site at https://intranet.contoso.com/sites/Lab12a.
2. Select Site Actions » Site Settings.
3. Inside the Site Collection Administration section of the Site Settings page, select Site collection features.
4. Activate the feature titled Open Documents in Client Applications by Default.(Note: without this activated Excel files will default to opening with the web display rather than the client application).
5. Create a new document library named Excel Workbooks so you have a location to publish Excel workbooks.
   1. Select Site Actions » New Document Library.
   2. Name the document library Excel Workbooks and configure it to have a Document Template of type Microsoft Excel spreadsheet. Click the Create button.

6. At this point, you should be at the page with the default view for the Excel Workbooks document library. Click on the Documents tab in the contextual Library Tools menu of the ribbon. Then click on the New Document button. This should launch the Excel 2010 client and give you a new empty workbook as a starting point.

   Figure 14

   Add a new document

   (Note: if this does not open the document in the Excel windows application. Open Excel directly from your Start menu» All Programs » Microsoft Office » Microsoft Excel 2010)

7. Now it is time to begin work inside the new workbook. Starting in cell B:3, create a simple set of Adventure Works sales data from which you can create a pie chart. Make one column on the left with country names. Make a second column on the right with Sales figures for these countries. Use the following screenshot to fill in the sample data. Also add a title to cell B:2 and do a little formatting work to make the title stand out.

   Figure 15

   The AdventureWorks Sales data

8. Now create a chart from this data.
   1. Select a range of cells which includes both columns of data.
   2. Up on the ribbon, select the Insert tab.

   3. Drop down the Pie menu and select the first Pie chart.

      Figure 16

      Add a Pie Chart

9. Once you have created the chart, you will need to resize and relocate it. Spend about 60 seconds trying to make the workbook as a whole look as pretty as possible.

   Figure 17

   The Pie chart for the AdventureWorks Sales data

10. Now save your work using the standard Excel Save command. Make sure you save the new workbook in the Excel Workbooks document library (i.e. https://intranet.contoso.com/sites/Lab12a/Excel%20Workbooks/) with a name of AdventureWorksSales.xls.
11. Over the next few steps, you will publish the workbook into Excel Services. However, this step has been added to give you an understanding of trusted file locations. While you will not be required to take any actions to configure trusted file locations in this lab exercise, you can follow these steps so you can see where and how trusted file locations are configured.
    1. Go to Start » All Programs » Microsoft SharePoint 2010 Products » SharePoint 2010 Central Administration.
    2. In the Application Management section, click Manage service applications.
    3. Click on the link titled Excel Services Application. Doing this brings you to a page where you can configure the service application.
    4. Click on the Trusted File Locations link.

    5. On your VM, you should see that there is already a trusted file location configured with an URL of https://. Click on this trusted file location so you can see its configured settings. Note that the checkbox with the caption of Children Trusted is checked. If you scroll down to the External Data section, you can see there is a property named Allow External Data with a setting value of Trusted dataconnection libraries and embedded. This allows users to publish workbooks with both kinds of data connections.

       Figure 18

       Configure a trusted location

    6. There has been nothing for you to do in these last few steps other than observe because the VM you are using already has a trusted file location that will allow you to do your work. However, while Excel Services by default allows users to publish Excel workbooks anywhere in the farm using any type of connection, do not assume this will always be the case. In secure environments, the IT staff will remove this default trusted file location and only add trusted file locations at a much narrower scope and not allow for embedded connections.
12. Now you will publish the workbook to Excel Services using the following steps.

    1. Select the File button (i.e. the green button at the top-left of the screen)

       Figure 19

       The File button

    2. In the left column, click on Save & Send.
    3. In the middle column click on Save to SharePoint.

    4. In the right column click the Current Location of Excel Workbooks (see image below).

       Figure 20

       Publish to Excel Services

    5. Click Save As

    6. The Save As dialog appears. Note that, unlike the usual Save As dialog, this dialog has a button in the bottom section with the caption Publish Options…. Click this button to display the Excel Services Publish Options dialog.

       Figure 21

       Publish the Excel workbook to SharePoint

    7. In the Show tab, change the value of the dropdown list from Entire Workbook to Sheets. Unselect Sheet2 and Sheet3 so that only Sheet1 is published. Click OK to save your changes and dismiss the Excel Services Options dialog.

    9. Click Save in the Save As dialog to publish the workbook to Excel Services. If you receive a prompt asking you if you want to overwrite the existing file, confirm by clicking OK. After you complete this step Excel will begin the publishing process. If this is the first time Excel Services has been started on your VM, it may take a minute to complete. When the publishing process is completed, you should now see your workbook inside the browser.

       Figure 22

       Open the workbook in the SharePoint site

13. Now it is time to create a second Excel workbook to publish to Excel Services. This example will involve pulling data from a data source using a connection to SQL Server.
    1. Navigate back to the Excel Workbooks document library.
    2. Click on the Documents tab in the contextual Library Tools menu of the ribbon.
    3. Click on the New Document button. This should launch the Excel 2010 client and give you a new empty workbook as a starting point.
    4. From within Excel, Save the workbook back to the document library with a name of PivotTable. (Note that this step is asking you to initially save the workbook which will use the standard Excel Save As command and not the Excel Services Publishing option.)
14. Now it’s time to create a new PivotTable Report based on a cube that has been created from the Adventure Works database to track Internet sales figures. Begin by creating a data source to point to SQL Server.
    1. Inside Excel, make sure Sheet1 is the active worksheet and that A:1 is the selected as the active cell.
    2. In the ribbon, select the Data Tab
    3. In the Get External Data group, click on the drop-down with the caption From Other Sources.
    4. Choose From SQL Server from the dropdown list.
    5. For server name, enter demo2010a. Choose the Next button.

    6. Make sure that the drop-down says Adventure Works DW 2008 R2 and that the Connect to a specific cube or table checkbox is checked. Select the cube named Adventure Works in the list control and click Next.

       Figure 23

       The Data connection wizard

    7. The final screen will have a caption of Save Data Connection FileandFinish.

       1. Change the File Name to AdventureWorksCube.odc.
       2. Change the Description to A connection point to the AW cube
       3. Change the Friendly Name to Adventure Works Cube.
       Figure 24

       The Data Connection wizard

       1. Click on the button at the Authentication Settings… button at the bottom to display the Excel Services Authentication Settings dialog.
       2. In the Excel Services Authentication Settings dialog, note that the connection is set to use Windows Authentication. Click OK.
          1. 1. Click Finish to complete the Data Connection Wizard. If you get prompted that a file for that connection already exists, simply click yes.

          2. Now you should be prompted by Excel to select how you wish to view the data in the Import Data dialog. Make sure that PivotTable Report is selected. Select OK.

             Figure 25

             Excel Services Import Data Settings

          3. At this point, the connection has now been established between the workbook and the Adventure Works cube and you are ready to begin your work configuring a Pivot table.

             Figure 26

             Creating a pivot table

15. Now turn your attention to the PivotTable Field List inside the task pane on the right side of the screen. Locate the dropdown list right under the caption Show fields related to. This allows you to select a measure group. Select Internet Sales from the dropdown list. Inside the Internet Sales measure group, locate the set of items under Σ Internet Sales. Select the checkbox next to Internet Sales Amount which will add the Internet Sales Amount measure to the PivotTable report.

    Figure 27

    The pivot table Fields list

16. Scroll down the list of fields and locate the Customer section. Check the checkbox next to Customer Geography to add its hierarchy to the rows of the PivotTable report.

    Figure 28

    Add the Customer Geography

17. Scroll down the list of fields and locate the Date folder. Locate and expand the inner folder named Calendar. Check the checkbox for Date.Calendar.

    Figure 29

    Add the Date.Calendar

18. At this point, your PivotTable Report should look something like this.

    Figure 30

    The PivotTable report

19. Creating a filter to drill down on data from 2002 by using the drop-down in the cell marked Column Labels to filter the Calendar Date Hierarchy. You can create this filter to only show Calendar Year (CY) 2006 by un-checking CY 2005, CY 2007, CY 2008 and CY 2010.

    Figure 31

    Create a filter

20. Now you must drill down into monthly sales figures for 2006. Right-click on CY 2006 in the PivotTable and select Expand/Collapse » Expand to Month.

21. Now you are required to hide columns that show totals so only monthly totals show.

    1. Right click on January 2006 within the PivotTable and click on Show/Hide Fields » Calendar Year which will toggle that column to a hidden state.
    2. Right click on January 2006 a second time and this time select Show/Hide Fields » Calendar Semester to hide the totals column for Calendar Semester.
    3. Right click on January 2006 a third time and select Show/Hide Fields » Calendar Quarter to hide the totals column for Calendar Quarter.
    4. You should now see a PivotTable Report that looks like the one below.
    Figure 32

    The PivotTable report

22. Now it’s time to create Sparklines to compliment the PivotTable Report.
    1. Copy the names of the countries in cells [A3:A8] and paste them into cells [A12:A17].
    2. Select cells [B12:B17].
    3. In the ribbon, go to the Insert tab. Select Line from the Sparklines group.
    4. In the dialog box, select the data of the PivotTable – [B3:M8].
    5. Click OK.
23. Now format the Sparklines to add a high point marker and a low point marker.
    1. In the ribbon, go to the Sparkline Tools Design tab and locate the Marker Colors button which has the icon with four colored squares.
    2. Select Marker Colors » High Point and select a color of Green.
    3. Select Marker Colors » Low Point and select a color of Yellow.

    4. You Sparklines should appear as the ones below.

       Figure 33

       The Sparklines graph

24. Now publish the workbook.

    1. Select the File button (i.e. the green button at the top-left of the screen).
    2. In the left column, click on Save & Send.
    3. In the middle column click on Save to SharePoint.
    4. In the right column click Current Location section Excel Workbooks https://intranet.contoso.con/sites/Lab12a/Excel%20Workbooks (Note: this should be the default location).
    5. Click Save As
    6. The Save As dialog appears. Click OK to publish your workbook to the Excel Workbook document library. (Note: If prompted to overwrite the existing file click Yes.)
    Figure 34

    Publish the Excel workbook to SharePoint