Scientific Data Server Quick Start Manual
This Guide is for Users of Windows XP, Vista and Excel 2007
(for the Excel 2003 quick start guide go here)
Data Access From Excel 2007
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. Choose the "Data" tab
3. Choose "From Other Sources". Choose "From Analysis Services" in the sub-menu. This will bring up the "Data Connection Wizard"
4. Fill in the data connection information. Then click the "Next >" button.
5. In the "Select Database and Table" window. Select the database using the drop-down. Check the box "Connect to a specific cube or table" and choose a cube from the list. Now click "Next >".
6. The next window will come up with a suggested "File Name" and "Friendly Name" for the connection. Check the "Save password in file" and the "Always attempt to use this file to refresh data" boxes. When you are done select "Finish".
7. In the next window click on "OK".
8. You should now see an empty pivot table in your Excel interface. 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 and drag fields down to the four white boxes at the bottom. 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.When there is a "+" sign next to a field name, 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.
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, use the "Pivot Table Field List". 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.
Once you have used one of the above selection method, 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 functions 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.