List of Figures

Chapter 2: Basic Data Analysis Techniques

Figure 2-1: The original unsorted data.

Figure 2-2: Data sorted by the Wait Time field.

Figure 2-3. Data summarized by year.

Figure 2-4: Data trends for three years’ worth of customer service scores.

Figure 2-5: Records with all average customer service scores less than 4.

Figure 2-6: Formatting highlights customer service scores less than 2.

Figure 2-7: A PivotChart report showing customer service scores summarized by year.

Figure 2-8: Dragging the Year data field to another location in the PivotTable report.

Figure 2-9: The results of dragging the Year data field to another location in the PivotTable report.

Figure 2-10: The Nonrelational Data table.

Figure 2-11: The Relational Data–Customer Info table.

Figure 2-12: The Relational Data–Receipts table.

Figure 2-13: Two tables related by Customer Number.

Figure 2-14: PivotTable report layout.

Figure 2-15: PivotTable report showing profits for all three states.

Figure 2-16: PivotTable report showing profits for drink products.

Figure 2-17: An XML data file.

Chapter 3: Analyzing Data with Microsoft Excel

Figure 3-1: The Sort dialog box.

Figure 3-2: Customers sorted by highest room service charges in descending order.

Figure 3-3: The August data records.

Figure 3-4: Advanced filter criteria.

Figure 3-5: The Advanced Filter dialog box.

Figure 3-6: Results of running the advanced filter.

Figure 3-7: Advanced criteria for customers that booked less than 5 or more than 15 nights in any given month.

Figure 3-8: Advanced criteria for customers that booked less than 5 or more than 15 nights and spent less than $100.00 or more than $1,500.00 on room service in any given month.

Figure 3-9: Summary of room service charges.

Figure 3-10: The Conditional Formatting dialog box set up to highlight values in the number of nights booked.

Figure 3-11: Nights booked with conditional formatting.

Figure 3-12: The Abercrombie Reservations (2001) chart.

Figure 3-13: Comparing number of nights booked to room service charges.

3-14 : The Query Wizard - Choose Columns page.

Figure 3-15: The Microsoft Query user interface, where filter criteria can be added.

Figure 3-16: Microsoft Query data returned to Excel.

Figure 3-17: The New Web Query dialog box.

Figure 3-18: Selecting the preferred customer data table.

Figure 3-19: Use the outline buttons and the plus and minus buttons to the left of the row number indicators to work with subtotals.

Figure 3-20: Subtotals displayed by nights booked and total room service.

Figure 3-21: A list of simple status bar functions.

Figure 3-22: The sum of the subtotals for total room service is displayed in the status bar.

Figure 3-23: The Descriptive Statistics dialog box.

Figure 3-24: Completing the Descriptive Statistics dialog box.

Figure 3-25: Results of running the Descriptive Statistics tool.

Figure 3-26: The Histogram dialog box.

Figure 3-27: Completing the Histogram dialog box.

Figure 3-28: The histogram produced for Hotel.xls.

Figure 3-29: The Moving Average dialog box.

Figure 3-30: Completing the Moving Average dialog box.

Figure 3-31: The chart showing actual and forecasted room service charges.

Figure 3-32: The Rank And Percentile dialog box.

Figure 3-33: Completing the Rank And Percentile dialog box.

Figure 3-34: All the values above this point are in the 90th percentile.

Figure 3-35: The Sampling dialog box.

Figure 3-36: Completing the Sampling dialog box for a random number of samples.

Figure 3-37: Running the Sampling tool for a random number of samples.

Figure 3-38: Running the Sampling tool for a periodic number of samples.

Figure 3-39: The Solver Parameters dialog box.

Figure 3-40: The Add Constraint dialog box.

Figure 3-41: The Hotel.xls workbook set up with the information required to run the Solver add-in calculation

Figure 3-42: The Solver Parameters dialog box with the problem’s cell references and a constraint.

Chapter 4: Analyzing Data with PivotTable and PivotChart Reports

Figure 4-1: An empty PivotTable report.

Figure 4-2: A completed PivotTable report. Month has been assigned as a row field, and Region as the column field. The data is the sum of gross sales.

Figure 4-3: An empty PivotChart report.

Figure 4-4: A completed PivotChart report.

Figure 4-5: The PivotTable Sort And Top 10 dialog box for order IDs sorted by Sum Of Extended Price.

Figure 4-6: Order IDs sorted by highest overall total price.

Figure 4-7: Products sorted by highest overall total sales.

Figure 4-8: The PivotTable Sort And Top 10 dialog box with options for showing the 10 order IDs with the highest discount.

Figure 4-9: Order IDs with the highest discount.

Figure 4-10: The PivotTable And PivotChart Wizard - Step 1 Of 3.

4 11: The PivotTable And PivotChart Wizard - Step 2 Of 3 page for an Excel list or database.

Figure 4-12: The PivotTable And PivotChart Wizard - Step 2 Of 3 page for an external data source.

Figure 4-13: When basing a PivotTable or PivotChart on more than one cell range, you can designate one or more page fields.

Figure 4-14: This PivotTable And PivotChart Wizard - Step 2b Of 3 page is displayed if you choose to have the wizard create a single page field.

Figure 4-15: This PivotTable And PivotChart Wizard - Step 2b Of 3 page lets you create your own page fields.

Figure 4-16: The PivotTable And PivotChart Wizard - Step 2 Of 3 page, which you use to select another PivotTable report or PivotChart report as a data source.

Figure 4-17: The PivotTable And PivotChart Wizard - Step 3 Of 3 page.

Figure 4-18: The PivotTable field list.

Figure 4-19: The sum of gross sales across regions by month.

Figure 4-20: The PivotTable toolbar and PivotTable menu.

Figure 4-21: List of items for the Agent ID field; you can filter the data to show records for all agents, one agent, or a particular combination.

Figure 4-22: Filtering data for the East region in the Region field.

Figure 4-23: Top five sales months, sorted in descending order, for the East region.

Figure 4-24: The Insert Calculated Field dialog box.

Figure 4-25: The Insert Calculated Item dialog box.

Figure 4-26: The insert calculated field dialog box for the Commission field.

Figure 4-27: The Insert Calculated Item dialog box for the WA Overhead Proceeds field.

Figure 4-28: PivotChart report for yearly West region home sales.

Figure 4-29: PivotChart report displaying columns for both state and month.

Figure 4-30: PivotTable report displaying sales figures for Oregon state.

Chapter 5: Analyzing Data with Microsoft Access

Figure 5-1: Sample nonrelational data.

Figure 5-2: Single data table that relies on other relational data tables.

Figure 5-3: Results of importing the CustServ.xls file’s Original Data worksheet.

Figure 5-4: Results of linking to the Relation.mdb file’s Nonrelational Data table.

Figure 5-5: Orders sorted by highest freight charge.

Figure 5-6: Orders made by the QUICK-Stop company.

Figure 5-7: Completing the Filter By Form screen.

Figure 5-8: Orders placed by Margaret Peacock and shipped by Federal Shipping.

Figure 5-9: Completing the advanced filter/sort.

Figure 5-10: Orders booked by Janet Leverling during the month of July, 1997.

Figure 5-11: Designing the select query.

Figure 5-12: Orders booked by Steven Buchanan during the month of January 1998 and shipped via United Package.

Figure 5-13: Crosstab report displaying total order invoice amounts for all customers for the years 1996 to 1998.

Figure 5-14: Report of total room service charges by customer, sorted by highest totals first.

Figure 5-15: PivotTable report based on the Invoices query.

Figure 5-16: PivotChart report based on the Invoices query PivotTable report. (Company names have been removed for clarity.)

Chapter 6: Analyzing Data with the Office Web Components

Figure 6-1: The Microsoft Office XP Spreadsheet Component.

Figure 6-2: The Microsoft Office XP Spreadsheet Component’s toolbar.

Figure 6-3: The Microsoft Office XP Spreadsheet Component’s Commands And Options dialog box at design time.

Figure 6-4: The Microsoft Office XP Spreadsheet Component’s Commands And Options window at run time.

Figure 6-5: Publishing the InsClaim.xls file as an Office XP Spreadsheet Component on a Web page.

Figure 6-6: Average insurance claim amount.

Figure 6-7: Total amount for all insurance claims.

Figure 6-8: Data can be sorted and filtered in a Spreadsheet Component at run time.

Figure 6-9: The Office XP PivotTable Component.

Figure 6-10: The Microsoft Office XP PivotTable Component’s toolbar.

Figure 6-11: The Office XP PivotTable Component’s Commands and Options window (at design time).

Figure 6-12: The Office XP PivotTable Component’s Commands And Options dialog box at run time. The appearance of the dialog box varies depending on the layout of the PivotTable Component.

Figure 6-13: Claims by agent sorted by total claims in descending order.

Figure 6-14: Claims for January for agent 2.

Figure 6-15: Top three agents as determined by total claims.

Figure 6-16: Number of claims processed by agent.

Figure 6-17: Total claim amount for all agents.

Figure 6-18: Total administrative fees to process insurance claims.

Figure 6-19: The Office XP Chart Component.

Figure 6-20: The Microsoft Office XP Chart Component’s toolbar.

Figure 6-21: The Office XP Chart Component’s Commands And Options dialog box at design time.

Figure 6-22: The smooth line chart type.

Figure 6-23: Claim totals in descending order from left to right.

Figure 6-24: Claim totals for December only.

Figure 6-25: Bottom three agents as determined by total claims for all months.

Figure 6-26: Number of claims processed by agent.

Figure 6-27: December claim amounts by day for agent 5.

Chapter 7: Introducing Online Analytical Processing

Figure 7-1: First 10 records of the sample car sales OLAP database.

Figure 7-2: Visual representation of the sample car sales OLAP database.

Chapter 8: Analyzing OLAP Data with Microsoft Excel

Figure 8-1: PivotTable report based on the Sales cube.

Figure 8-2: PivotTable report based on the CarSales.cub file.

Figure 8-3: Completed PivotTable report based on the CarSales.cub file.

Figure 8-4: Check boxes for the Time field.

Figure 8-5: Check boxes for the Customer Sales Region field.

Figure 8-6: PivotTable report displaying car sales for the first quarter of both 2001 and 2002 for Washington State, for all sales managers.

Figure 8-7: The Price field will be the measure in the cube file.

Figure 8-8: Completed details for the second step of the OLAP Cube Wizard, with dimensions and levels selected.

Figure 8-9: The finished PivotTable report, ready for data analysis.

Chapter 9: Analyzing OLAP Data with Microsoft Data Analyzer

Figure 9-1: The Microsoft Data Analyzer main window.

Figure 9-2: A Data Analyzer dimension pane.

Figure 9-3: The Connection Properties dialog box.

Figure 9-4: Initial view based on the Car Sales cube.

Figure 9-5: The Color Scale dialog box.

Figure 9-6: The Define View dialog box’s Dimensions tab.

Figure 9-7: The Define View dialog box’s Measures tab.

Figure 9-8: Car sales for 2001 in Washington State.

Figure 9-9: Top car types sold in 2002 for salesperson number 8.

Figure 9-10: Color changes where 0 percent of the total lengths of the members in the filter are red and 100 percent is green.

Figure 9-11: Top three salespeople for the West region in 2001.

Figure 9-12: Overall car sales for salesperson 10 in 2002.

Figure 9-13: Overall car sales for salesperson 10 in 2001.

Figure 9-14: Overall car sales for 2001 by state.

Figure 9-15: Deluxe sedan sales for California in 2002 by salesperson.

Figure 9-16: Overall blue car sales in Washington by salesperson.

Figure 9-17: The Template Measure Editor.

Figure 9-18: The Sales Commission custom measure definition.

Figure 9-19: The bar lengths reflect the Sales Commission measure.

Figure 9-20: The bar lengths reflect the Sum of Price measure.

Figure 9-21: The definition of the Average Car Sales custom measure.

Figure 9-22: The Average Car Sales custom measure applied to the current view’s colors. You can easily see that sales manager 103’s sales were well below average.

Figure 9-23: The definition of the Percent of Highest Car Sales Member custom measure.

Figure 9-24: Percent of Highest Car Sales Member custom measure applied to the current view’s colors.

Figure 9-25: Business Center results comparing Sum of Price member bar patterns over time to salesperson 6.

Figure 9-26: Clicking Select in the BusinessCenter filters the view in Data Analyzer

Figure 9-27: Results of exporting a copy of the CarSales.max view file to an Excel workbook.

Figure 9-28: Results of exporting a copy of the CarSales.max view file to an Excel PivotTable report.

Figure 9-29: Results of exporting a copy of the CarSales.max view file to a PowerPoint slide.

Figure 9-30: Results of exporting a copy of the CarSales.max view file to a Web page.

Chapter 10: Working with XML Data in Excel and Access

Figure 10-1: The CustList.xml file displayed in Internet Explorer 6.

Figure 10-2: The CustList.xml file displayed in Excel 2002.

Figure 10-3: The CustOrds.xml file displayed in Internet Explorer 6.

Figure 10-4: The CustOrds.xml file displayed in Excel 2002.

Figure 10-5: Orders for customer ALFKI only.

Figure 10-6: The Products.xml file displayed in Internet Explorer 6.

Figure 10-7: The Products.xml file displayed in Excel 2002.

Figure 10-8: Products for category ID 1 only.

Figure 10-9: The completed New Web Query dialog box.

Figure 10-10: The Customers1.xml file.

Figure 10-11: The Customers1.xsd file.

Figure 10-12: The Customers1.xsl file.

Chapter 11: Extending Office Data Analysis Features with Code

Figure 11-1: The Object Browser.

Figure 11-2: The Office VBA Language Reference online help provides information about the items in an object model.

Figure 11-3: Referencing projects and programmatic libraries (object models).

Figure 11-4: Results of running the WorksheetNames subroutine on a default worksheet.

Figure 11-5: Results of running the SortSales subroutine.

Figure 11-6: Records filtered by running the FilterSales subroutine.

Figure 11-7: Subtotals added by running the CreateSubtotals subroutine.

Figure 11-8: Results of running the InsertWorksheetFunctions subroutine.

Figure 11-9: Data created by Analysis ToolPak functions run through the InsertAnalysisToolPakFunctions subroutine.

Figure 11-10: Result of running the AddConditionalFormatting subroutine.

Figure 11-11: Results of running the CreatePivotTableAndPivotChartReport subroutine.

Figure 11-12: Results of running the SaveXMLData subroutine with XDR-formatted data.

Figure 11-13: Results of running the SaveXMLData subroutine with XMLSS-formatted data.

Figure 11-14: Results of running the SortBookSales subroutine.

Figure 11-15: Results of running the FilterBookSales subroutine.

Figure 11-16: The SalesPivotCode.htm file’s Spreadsheet Component.

Figure 11-17: The SalesPivotCode.htm file’s PivotTable Component.

Figure 11-18: The SalesPivotCode.htm file’s Chart Component.

Figure 11-19: Results of clicking the Display Advertising Data button.

Figure 11-20: Results of clicking the Display Demographic Data button.

Figure 11-21: Results of clicking the Display Store Data button.

Chapter 12: Maintaining Data Reporting and Analysis Systems

Figure 12-1: The SQL Server Enterprise Manager.

Figure 12-2: Connecting to SQL Server on a different computer.

Figure 12-3: The Connect To SQL Server dialog box in Query Analyzer.