Excel advanced report development

Timothy Zapawa

Excel® Advanced

Report Development

01_588117 ffirs.qxd 6/21/05 6:35 PM Page iii

01_588117 ffirs.qxd 6/21/05 6:35 PM Page ii

Excel®

Advanced

Report Development

01_588117 ffirs.qxd 6/21/05 6:35 PM Page i

01_588117 ffirs.qxd 6/21/05 6:35 PM Page ii

Timothy Zapawa

Excel® Advanced

Report Development

01_588117 ffirs.qxd 6/21/05 6:35 PM Page iii

Excel® Advanced Report Development

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2005 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 0-7645-8811-7

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1MA/RR/QX/QV/IN

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any

form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise,

except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without

either the prior written permission of the Publisher, or authorization through payment of the

appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA

01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be

addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis,

IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representa￾tions or warranties with respect to the accuracy or completeness of the contents of this work and

specifically disclaim all warranties, including without limitation warranties of fitness for a par￾ticular purpose. No warranty may be created or extended by sales or promotional materials. The

advice and strategies contained herein may not be suitable for every situation. This work is sold

with the understanding that the publisher is not engaged in rendering legal, accounting, or other

professional services. If professional assistance is required, the services of a competent profes￾sional person should be sought. Neither the publisher nor the author shall be liable for damages

arising herefrom. The fact that an organization or Website is referred to in this work as a citation

and/or a potential source of further information does not mean that the author or the publisher

endorses the information the organization or Website may provide or recommendations it may

make. Further, readers should be aware that Internet Websites listed in this work may have

changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support, please

contact our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317)

572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in

print may not be available in electronic books.

Library of Congress Cataloging-in-Publication Data: Available from the Publisher

Trademarks: Wiley, the Wiley Publishing logo and related trade dress are trademarks or regis￾tered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other

countries, and may not be used without written permission. Excel is a trademark of Microsoft

Corporation in the United States and/or other countries. All other trademarks are the property

of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor

mentioned in this book.

01_588117 ffirs.qxd 6/21/05 6:35 PM Page iv

For Lisa

01_588117 ffirs.qxd 6/21/05 6:35 PM Page v

About the Author

vi

Timothy Zapawa (Darnestown, Maryland) obtained a baccalaureate in Account￾ing and Arabic from the University of Michigan in Ann Arbor in 1997. Prior to

that, he served four years in the United States Navy, specializing in electronic

communications. He has completed several certifications and professional

examinations in a variety of financial and technical fields, including Certified

Public Accountant (CPA), Certified Management Accountant (CMA), Certified

Financial Manager (CFM), Project Manager Professional (PMP), Microsoft Certi￾fied Systems Engineer (MCSE), and Microsoft Certified Database Administrator

(MCDBA). Tim is currently a project director at Advantage Computing Systems,

Inc. (www.advantagecs.com), a company that produces enterprise software

for publishing companies and service bureaus. He leads teams of engineers,

developers, and managers through software implementation projects. He has

also developed several technical training courses for his company’s clients,

including modules on Crystal Reports, SQL query development for Oracle and

SQL Server, and Excel PivotTable Reporting.Credits

01_588117 ffirs.qxd 6/21/05 6:35 PM Page vi

Acquisitions Editor

Katie Mohr

Senior Development Editor

Jodi Jensen

Technical Editor

Brian Patterson

Production Editor

Pamela Hanley

Copy Editor

Kathryn Duggan

Editorial Manager

Mary Beth Wakefield

Vice President & Executive Group

Publisher

Richard Swadley

Vice President and Publisher

Joseph B. Wikert

Project Coordinator

Michaek Kruzil

Graphics and Production Specialists

Denny Hager, Stephanie D. Jumper

Quality Control Technicians

John Greenough, Leeann Harney

Proofreading and Indexing

TECHBOOKS Production Services

01_588117 ffirs.qxd 6/21/05 6:35 PM Page vii

Acknowledgments xv

Introduction xvi

Part I Report Initiation and Source Data 1

Chapter 1 Taking a First Look at Excel’s Reporting Tools 3

Why Use Excel for Reports? 3

PivotTable Reports 6

PivotChart Reports 9

Spreadsheet Reports 10

Parameter Queries 13

Web Queries 14

Related Office Components 15

Microsoft MapPoint 16

Microsoft FrontPage 17

Microsoft Access 18

Chapter Review 18

Chapter 2 Getting Started with PivotTables 19

PivotTable Data Organization 19

Creating Your First PivotTable 22

Modifying the PivotTable 27

Removing a Field from a PivotTable 27

Changing the Summary Type 28

Refreshing Report Data 31

Drilling Down on Report Data 34

Basic Components and Terminology 36

PivotTable Areas 37

PivotTable Toolbar 42

Contents

viii

02_588117 ftoc.qxd 6/21/05 6:29 PM Page viii

PivotTable Field List 43

Terminology 45

Trying It Out in the Real World 45

Getting Down to Business 46

Reviewing What You Did 49

Chapter Review 49

Chapter 3 Understanding and Accessing Source Data 51

Characteristics of a Data Source 52

Types of Data Sources 52

Data Source Locations 53

Authenticating to a Data Source 53

Importing Text Files 53

Delimited Files 54

Fixed-Width Files 59

Creating Data Source Name Files 64

Creating a Data Source for a Text File 65

Setting the Directory 67

Defining the File Format 68

Supporting Files of Text File DSNs 72

Creating a Data Source for an SQL Database 75

Authenticating to the Database 77

Supporting Files of Database DSNs 81

Creating a Data Source for an OLAP Cube 82

Accessing the OLAP Cube 85

Supporting Files of OLAP Cube DSNs 87

Viewing the DSN File for an OLAP Cube 88

Trying It Out in the Real World 88

Getting Down to Business 89

Reviewing What You Did 93

Chapter Review 93

Chapter 4 Using the Query Wizard 93

Overview of the Query Wizard 93

Starting the Wizard 95

Selecting Objects 96

Using Table Options for Object Selection 97

Choosing Fields 99

Filtering Data 101

Using the Wizard to Filter Data 102

Applying Multiple Filters 104

Mathematical Operators 104

String Operators 105

Viewing and Changing Filter Conditions 106

Sorting Data 106

Finishing Up 107

Saving the Query 108

Viewing a Saved Query 109

Contents ix

02_588117 ftoc.qxd 6/21/05 6:29 PM Page ix

Opening a Saved Query 111

Editing with Microsoft Query 112

Trying It Out in the Real World 113

Getting Down to Business 113

Reviewing What You Did 115

Chapter Review 116

Chapter 5 Getting Started with Microsoft Query 117

Introducing the Microsoft Query Program 118

Starting Microsoft Query 119

With New Reports 119

With Existing Reports 121

Understanding the Basics 122

Getting to Know the Environment 123

Customizing the Environment 131

Working in the Environment 138

Trying It Out in the Real World 141

Getting Down to Business 141

Reviewing What You Did 143

Chapter Review 143

Chapter 6 Working with SQL in Microsoft Query 145

Managing the SQL Query 145

Before You Begin 146

Working in the Select Part of an SQL Query 147

Working in the Where and Having Parts 156

Working in the Order By Part 165

Managing Table Joins 166

Before You Begin 167

Understanding Joins and Join Types 170

Executing SQL Commands 178

Managing Tables 179

Understanding How Microsoft Query Uses SQL 180

Automatic Query Modification 181

Hiding an SQL Query 182

Use Brackets instead of Single Quotes 182

Criteria Incorrectly Put into the Having Clause 184

Stored Procedures with Input Parameters 184

Inserting a Stored Procedure 184

Using Parameters 185

Accessing Multiple Databases in a Single Session 188

Multiple Databases on a Single Server 188

Multiple Databases on Different Servers 189

Trying It Out in the Real World 191

Getting Down to Business 191

Reviewing What You Did 193

Chapter Review 194

x Contents

02_588117 ftoc.qxd 6/21/05 6:29 PM Page x

Part II Advanced Reporting Features 195

Chapter 7 Designing PivotTable Reports 197

A Review of the PivotTable Components 198

Before You Begin 198

Layout Manager 199

PivotTable Toolbar 204

Formatting the PivotTable Report 205

Auto and Manual Formatting Features 206

Formatting Options 209

Preparing the Report for Printing 213

Designing and Working with Page Area Fields 214

Sorting 217

Conditional Formatting 219

Grouping Items 220

Managing Inner and Outer Fields 222

Renaming Fields 226

Managing How Data Items Are Displayed 227

Formatting Data Area Fields 227

Managing PivotTable Functions 229

Creating Calculated Items and Calculated Fields 229

Managing Data Area Fields 236

Trying It Out in the Real World 241

Getting Down to Business 242

Reviewing What You Did 244

Chapter Review 244

Chapter 8 Managing PivotTable Data 245

Before You Begin 245

Configuring PivotTable Data Options 247

Saving Data with the Table Layout 248

Enable Drill To Details 249

Refresh On Open 249

Refresh Every 250

Save Password 251

Managing Data Sources 252

Identifying and Changing Valid Data Sources 252

Configuring a PivotTable to Share Source Data 255

Configuring a PivotTable to Use Separate Source Data 256

Purging Ghost Values 257

Trying It Out in the Real World 258

Getting Down to Business 258

Reviewing What You Did 260

Chapter Review 260

Contents xi

02_588117 ftoc.qxd 6/21/05 6:29 PM Page xi

Chapter 9 Exploring PivotChart Functionality 261

Before You Begin 262

Basic Components and Terminology 264

Creating a PivotChart 264

Terminology and Display 264

PivotChart Toolbar 267

Changing the PivotChart’s Location 268

Field List Window 269

Formatting the PivotChart 270

Selecting a Chart Type 270

Managing the View of 3-D Charts 271

Formatting the Plot Area 274

Formatting the Data Series 274

Working with PivotChart Options 277

Setting the Chart Title 278

Configuring the Chart Legend 279

Adding a Data Table 280

Trying It Out in the Real World 281

Getting Down to Business 282

Reviewing What You Did 284

Chapter Review 284

Chapter 10 Working with OLAP Cubes 285

Introducing OLAP 286

Understanding OLAP Cubes and Data Warehouses 287

Key OLAP Concepts and Terminology 288

Client- and Server-Based OLAP Processing 292

Understanding Server-Based OLAP 292

Understanding Client-Based OLAP 294

Working with Server-Based Cubes 296

Accessing an OLAP Cube on the Analysis Server 296

Creating and Working with Offline Cube Files 297

Working with Client-Based OLAP 301

Option 1 — Retrieving Data on Demand 302

Option 2 — Retrieving All Data at Once 303

Option 3 — Saving a Cube File 304

Security Options Related to Offline Cubes 305

Using the OLAP Cube Wizard 307

Working with OLAP Data in a PivotTable 312

Comparing OLAP and OLTP 312

Understanding the Display of OLAP Report Fields 313

Working with Dimensions 314

Working with Measures 317

Trying It Out in the Real World 318

Getting Down to Business 319

Reviewing What You Did 322

Chapter Review 322

xii Contents

02_588117 ftoc.qxd 6/21/05 6:29 PM Page xii