Scientific Data Server User Manual
Excel 2007 Pivot Tables - Connecting to Data Cubes
Links to Other Manual Sections
Excel 2007 introduced a new ribbon style menu at the top of the worksheet and a completely new set of menus to go with it. In addition, in Excel 2007 the pivot table controls and layout mechanics are revamped from Excel 2003. This manual is designed to give you a brief intro into using Excel 2007 pivot tables with data cubes. For a more thorough tutorial on Excel 2007 pivot tables, please refer to the Microsoft tutorials on Excel 2007 data connections and Excel 2007 pivot tables.
Data Access From Excel 2007
Excel 2007 has a significantly different user interface than previous versions of Excel. The menu system in Excel 2007 is presented via a ribbon interface. The circle in the upper left hand corner provides access to the traditional file menu and then the tabs across the second row in the interface provide access to the excel functions. In the diagram below the "Data" tab is shown.
1. Configuring a Data Connection
2. Pivot Table Layout
Configuring a Data Connection
In order to connect to the data on the data server, you will first need to create a data connection in Excel.
1. To begin, open a blank excel 2007 worksheet
2. Using the top ribbon choose the "Data" tab
3. To define a new data connection, in the "Get External Data" section of the ribbon, choose "From Other Sources". This will bring up a sub-menu. Choose "From Analysis Services" in this sub-menu. This will bring up a new window with the "Data Connection Wizard"
4. Fill in the data connection information as shown below except replace "username" with the username you want to connect with and type in the appropriate password. Then click the "Next >" button.
5. The next window should be the "Select Database and Table" window. (If instead of this window you get a transport error, you are likely either unable to reach bwc.berkeley.edu via the network or the username and password you supplied were mistyped.) . The first thing you need to do is select the database using the drop-down under "Select the database that contains the data you want:" After you select the database, check the box "Connect to a specific cube or table" and choose a cube from the list. (In the example below I have chosen the RussianRiver database and the RussianRiver cube.) Now choose "Next >".
6. In the next window, you will have the opportunity to save the connection information so you can use it again in the future. The window will come up with a suggested "File Name" and "Friendly Name" for the connection. These can be changed if you prefer not to use the default names. Make sure you have checked the "Save password in file" and the "Always attempt to use this file to refresh data" boxes. (When you check the "Save password in file" box, you will be prompted with a question asking whether you really want to save the password: choose yes.) When you are done select "Finish".
7. In the next window you will be prompted to specify where in your Excel workbook you want the pivot table to appear. You will also have the opportunity to specify whether you want both a table and a chart. The choices in the window below are a good starting point. Once you have made your choices, click on "OK".
8. If all goes well you should now see an empty pivot table in your Excel interface. The ribbon will also change to a new set of tabs that are the "PivotTable Tools". This ribbon will show any time you have selected within a pivot table or pivot chart on a worksheet. To lay out the pivot table, use the "Pivot Table Field List" window which, in the picture below, runs vertically down the right ride of the pivot table. There are two types of fields in the fields list. Measures are fields that represent thedata values you can display in the main part of the pivot table or chart.They are listed under headings with a Summation symbol preceding them ("Values" and "Data" are typical measure headings and "Average" would be a typical measure). The remaining fields can be used as column or row headings or as page filters. These fields are all listed under headings with a picture of a page next to them ("Site" is a typical field heading that fits this category). In the cases where there is a "+" sign next to a field name, this means that the field is an expandable hierarchy. If you use it as a column or row, you will on first placement get the highest level dimension but double-clicking on any of the items will cause it to expand to show the next lower level in the hierarchy. For example, the "Water Year to Day" in the image below is a hierarchy. If it were placed as the columns of the pivot table then water years would be displayed. Double-clicking on any particular year in the pivot table would expand it to the days in the water year).
9. The image below shows a pivot table that has been laid out to have "Site" in the rows and "Datumtype" in the columns. "Count" is the measure. The resulting pivot table provides in each cell the amount of data available for the corresponding site and datumtype (across all years). Note: if you wanted to look at this information but only considering the year 2004 then you could put "Year" in the page filter area and select only the year 2004.
10. If you want to select particular items within a field, there are two ways to select the set of items.
The first method is in the "Pivot Table Field List". The first step is to select an item to make sure the "Pivot Table Field List" window is the mouse focus . Float the mouse over the fields in the lists. As you move over a field, it will be highlighted. If the field has multiple items in it, a down arrow will also appear on the right hand edge. If you select this down arrow, a new window will pop up that will allow you to change the sort order of the items and to select particular items. To deselect all items, click once on the "Select All" check box. To then select particular items click on their associated check boxes. If you only want to deselect a few items, leave "Select All" selected and then click on the check boxes of the items you wish to remove. This will clear their check boxes.
The second method is in the pivot table. You will see cells labeled "Row Labels" and "Column Labels". These cells will have either a down arrow or a filter symbol and a miniature down arrow in their far right corner. Clicking on the down arrow or filter symbol will bring up a menu that will allow you to choose the items or change the sort order of the items. To deselect all items, click once on the "Select All" check box. To then select particular items click on their associated check boxes. If you only want to deselect a few items, leave "Select All" selected and then click on the check boxes of the items you wish to remove. This will clear their check boxes. If you have multiple fields in the columns or rows, then select between them using the drop down menu on the "Select field" box at the top of the window. The page filters will appear in the upper left corner of the pivot table and each one will have its own down arrow that can be used to select items within the field.
Once you have used one of the above two selection methods, the symbol next to the field in the "Pivot Table Field List" and in the Row or Column Labels cell will change to a filter indicating that only a portion of the field is being displayed. In the image below, the "Datumtype" field has this symbol.
11. If you want to get rid of the per field sub-totals in the pivot table, in the layout area at the bottom of the "PivotTable Field List" window click on the down arrow to the right of the field and select "Field Settings" from the drop down menu. This will bring up the window below. Change "Subtotals" to "None". Then select "OK".
12. If you are making major layout changes and the cube you are connected to is relatively large, you may also want to check the box "Defer Layout Update" at the bottom of the "PivotTable Field List" window before making the changes to the layout and then uncheck it when you have the layout the way you want it.
13. If you want to see the same data as is in the pivot table in a pivot chart, then select the "Insert" tab in the "PivotTable Tools" menu ribbon and then select the appropriate type of chart from the "Charts" section of the ribbon. The chart will appear on the same worksheet as the pivot table. You can also bring up a pivot chart
14. If you accidentally close the "PivotTable Field List" window, you can redisplay it by going to the "Options" tab in the "PivotTable Tools" ribbon menu and in the "Show/Hide" section and selecting "Field List".
The Excel 2007 pivot tables have significantly more fundtions than have been covered here but what is covered above should get you to a working pivot table and a basic working knowledge of the interface.
Contact: BWC Support
Credits:The research and development of the Scientific Data Server is funded by the Microsoft Corporation.