Excel Advanced Report Development

by Zapawa, Timothy

• ISBN13: 9780764588112
• ISBN10: 0764588117
• Edition: 1st
• Format: Paperback
• Copyright: 2005-07-29
• Publisher: Wiley

Summary

If you're a SQL programmer or an experienced Excel user, here at last is the ultimate resource on developing reporting solutions with Excel. Focused on report development using OLTP databases, this book is packed with comprehensive information on both technical and strategic aspects. You'll thoroughly examine the main features of Excel's reporting technology-PivotTable reports, Spreadsheet reports, parameter queries, and web components. With notes, tips, warnings, and real-world examples in each chapter, you'll be able to put your knowledge to work immediately. This book includes: Single-source coverage of Excel's report development features Extensive and in-depth information on PivotTable and Spreadsheet report features, functions, and capabilities Thorough documentation of the Microsoft Query program included with Excel Comprehensive information on Excel's client-based OLAP cube tools for processing very large datasets from OLTP data sources Detailed information on creating and working with web-enabled Excel reports

Author Biography

Timothy Zapawa is a project director atAdvantage Computing Systems, Inc., where he leads teams of engineers, developers, and managers through long-term software implementations. He has developed software training programs and has obtained several professional and technical certifications, including Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Database Administrator (MCDBA).

Table of Contents

Acknowledgments	xv
Introduction	xvi
Part I Report Initiation and Source Data	1	(194)
Taking a First Look at Excel's Reporting Tools	3	(16)
Why Use Excel for Reports?	3	(3)
PivotTable Reports	6	(3)
PivotChart Reports	9	(1)
Spreadsheet Reports	10	(3)
Parameter Queries	13	(1)
Web Queries	14	(1)
Related Office Components	15	(3)
Microsoft MapPoint	16	(1)
Microsoft FrontPage	17	(1)
Microsoft Access	18	(1)
Chapter Review	18	(1)
Getting Started with PivotTables	19	(32)
PivotTable Data Organization	19	(3)
Creating Your First PivotTable	22	(5)
Modifying the PivotTable	27	(9)
Removing a Field from a PivotTable	27	(1)
Changing the Summary Type	28	(3)
Refreshing Report Data	31	(3)
Drilling Down on Report Data	34	(2)
Basic Components and Terminology	36	(9)
PivotTable Areas	37	(5)
PivotTable Toolbar	42	(1)
PivotTable Field List	43	(2)
Terminology	45	(1)
Trying It Out in the Real World	45	(4)
Getting Down to Business	46	(3)
Reviewing What You Did	49	(1)
Chapter Review	49	(2)
Understanding and Accessing Source Data	51	(42)
Characteristics of a Data Source	52	(1)
Types of Data Sources	52	(1)
Data Source Locations	53	(1)
Authenticating to a Data Source	53	(1)
Importing Text Files	53	(11)
Delimited Files	54	(5)
Fixed-Width Files	59	(5)
Creating Data Source Name Files	64	(1)
Creating a Data Source for a Text File	65	(10)
Setting the Directory	67	(1)
Defining the File Format	68	(4)
Supporting Files of Text File DSNs	72	(3)
Creating a Data Source for an SQL Database	75	(7)
Authenticating to the Database	77	(4)
Supporting Files of Database DSNs	81	(1)
Creating a Data Source for an OLAP Cube	82	(6)
Accessing the OLAP Cube	85	(2)
Supporting Files of OLAP Cube DSNs	87	(1)
Viewing the DSN File for an OLAP Cube	88	(1)
Trying It Out in the Real World	88	(5)
Getting Down to Business	89	(4)
Reviewing What You Did	93	(1)
Chapter Review	93	(1)
Using the Query Wizard	93	(24)
Overview of the Query Wizard	93	(2)
Starting the Wizard	95	(1)
Selecting Objects	96	(5)
Using Table Options for Object Selection	97	(2)
Choosing Fields	99	(2)
Filtering Data	101	(5)
Using the Wizard to Filter Data	102	(2)
Applying Multiple Filters	104	(1)
Mathematical Operators	104	(1)
String Operators	105	(1)
Viewing and Changing Filter Conditions	106	(1)
Sorting Data	106	(1)
Finishing Up	107	(6)
Saving the Query	108	(1)
Viewing a Saved Query	109	(2)
Opening a Saved Query	111	(1)
Editing with Microsoft Query	112	(1)
Trying It Out in the Real World	113	(3)
Getting Down to Business	113	(2)
Reviewing What You Did	115	(1)
Chapter Review	116	(1)
Getting Started with Microsoft Query	117	(28)
Introducing the Microsoft Query Program	118	(1)
Starting Microsoft Query	119	(3)
With New Reports	119	(2)
With Existing Reports	121	(1)
Understanding the Basics	122	(19)
Getting to Know the Environment	123	(8)
Customizing the Environment	131	(7)
Working in the Environment	138	(3)
Trying It Out in the Real World	141	(2)
Getting Down to Business	141	(2)
Reviewing What You Did	143	(1)
Chapter Review	143	(2)
Working with SQL in Microsoft Query	145	(50)
Managing the SQL Query	145	(21)
Before You Begin	146	(1)
Working in the Select Part of an SQL Query	147	(9)
Working in the Where and Having Parts	156	(9)
Working in the Order By Part	165	(1)
Managing Table Joins	166	(12)
Before You Begin	167	(3)
Understanding Joins and Join Types	170	(8)
Executing SQL Commands	178	(1)
Managing Tables	179	(1)
Understanding How Microsoft Query Uses SQL	180	(4)
Automatic Query Modification	181	(1)
Hiding an SQL Query	182	(1)
Use Brackets instead of Single Quotes	182	(2)
Criteria Incorrectly Put into the Having Clause	184	(1)
Stored Procedures with Input Parameters	184	(1)
Inserting a Stored Procedure	184	(1)
Using Parameters	185	(3)
Accessing Multiple Databases in a Single Session	188	(3)
Multiple Databases on a Single Server	188	(1)
Multiple Databases on Different Servers	189	(2)
Trying It Out in the Real World	191	(3)
Getting Down to Business	191	(2)
Reviewing What You Did	193	(1)
Chapter Review	194	(1)
Part II Advanced Reporting Features	195	(224)
Designing PivotTable Reports	197	(48)
A Review of the PivotTable Components	198	(7)
Before You Begin	198	(1)
Layout Manager	199	(5)
PivotTable Toolbar	204	(1)
Formatting the PivotTable Report	205	(24)
Auto and Manual Formatting Features	206	(3)
Formatting Options	209	(4)
Preparing the Report for Printing	213	(1)
Designing and Working with Page Area Fields	214	(3)
Sorting	217	(2)
Conditional Formatting	219	(1)
Grouping Items	220	(2)
Managing Inner and Outer Fields	222	(4)
Renaming Fields	226	(1)
Managing How Data Items Are Displayed	227	(1)
Formatting Data Area Fields	227	(2)
Managing PivotTable Functions	229	(12)
Creating Calculated Items and Calculated Fields	229	(7)
Managing Data Area Fields	236	(5)
Trying It Out in the Real World	241	(3)
Getting Down to Business	242	(2)
Reviewing What You Did	244	(1)
Chapter Review	244	(1)
Managing PivotTable Data	245	(16)
Before You Begin	245	(2)
Configuring PivotTable Data Options	247	(5)
Saving Data with the Table Layout	248	(1)
Enable Drill To Details	249	(1)
Refresh On Open	249	(1)
Refresh Every	250	(1)
Save Password	251	(1)
Managing Data Sources	252	(6)
Identifying and Changing Valid Data Sources	252	(3)
Configuring a PivotTable to Share Source Data	255	(1)
Configuring a PivotTable to Use Separate Source Data	256	(1)
Purging Ghost Values	257	(1)
Trying It Out in the Real World	258	(2)
Getting Down to Business	258	(2)
Reviewing What You Did	260	(1)
Chapter Review	260	(1)
Exploring PivotChart Functionality	261	(24)
Before You Begin	262	(2)
Basic Components and Terminology	264	(6)
Creating a PivotChart	264	(1)
Terminology and Display	264	(3)
PivotChart Toolbar	267	(1)
Changing the PivotChart's Location	268	(1)
Field List Window	269	(1)
Formatting the PivotChart	270	(7)
Selecting a Chart Type	270	(1)
Managing the View of 3-D Charts	271	(3)
Formatting the Plot Area	274	(1)
Formatting the Data Series	274	(3)
Working with PivotChart Options	277	(4)
Setting the Chart Title	278	(1)
Configuring the Chart Legend	279	(1)
Adding a Data Table	280	(1)
Trying It Out in the Real World	281	(3)
Getting Down to Business	282	(2)
Reviewing What You Did	284	(1)
Chapter Review	284	(1)
Working with OLAP Cubes	285	(38)
Introducing OLAP	286	(6)
Understanding OLAP Cubes and Data Warehouses	287	(1)
Key OLAP Concepts and Terminology	288	(4)
Client- and Server-Based OLAP Processing	292	(4)
Understanding Server-Based OLAP	292	(2)
Understanding Client-Based OLAP	294	(2)
Working with Server-Based Cubes	296	(5)
Accessing an OLAP Cube on the Analysis Server	296	(1)
Creating and Working with Offline Cube Files	297	(4)
Working with Client-Based OLAP	301	(11)
Option 1 --- Retrieving Data on Demand	302	(1)
Option 2 --- Retrieving All Data at Once	303	(1)
Option 3 --- Saving a Cube File	304	(1)
Security Options Related to Offline Cubes	305	(2)
Using the OLAP Cube Wizard	307	(5)
Working with OLAP Data in a PivotTable	312	(6)
Comparing OLAP and OLTP	312	(1)
Understanding the Display of OLAP Report Fields	313	(1)
Working with Dimensions	314	(3)
Working with Measures	317	(1)
Trying It Out in the Real World	318	(4)
Getting Down to Business	319	(3)
Reviewing What You Did	322	(1)
Chapter Review	322	(1)
Creating and Using Spreadsheet Reports	323	(48)
Introducing Spreadsheet Reports	323	(4)
Looking at Some Features of Spreadsheet Reports	324	(1)
Comparing Spreadsheet Reports to PivotTable Reports	324	(3)
A Review of Spreadsheet Report Components	327	(4)
Before You Begin	327	(2)
Using the Spreadsheet Report Toolbar	329	(2)
Managing the Spreadsheet Report	331	(15)
Configuring Spreadsheet Report Formatting Options	331	(3)
Changing Column Location	334	(1)
Using Filters	335	(2)
Sorting Data	337	(2)
Calculating Subtotals	339	(2)
Using Fill-Down Formulas	341	(1)
Conditional Formatting	342	(1)
Configuring Spreadsheet Report Data Options	343	(3)
Using a Web Query	346	(4)
Trying a Web Query	346	(2)
Working in the Web Query Dialog Box	348	(2)
Using a Parameter Query	350	(17)
Understanding Parameters	351	(1)
Integrating a Parameter into an SQL Query	351	(2)
Integrating a Parameter into a View	353	(2)
Integrating a Parameter into a Stored Procedure	355	(2)
Customizing the Parameters	357	(1)
Using Parameters in the Report	358	(9)
Trying It Out in the Real World	367	(2)
Getting Down to Business	367	(1)
Reviewing What You Did	368	(1)
Chapter Review	369	(2)
Using Excel Reports on the Web	371	(48)
The Basics of Publishing Excel Reports to the Web	372	(9)
Before You Begin	373	(4)
Reviewing the Converted PivotTable List	377	(1)
Introducing PivotTable Lists	378	(3)
Basic Components and Terminology	381	(13)
Using the PivotTable List Toolbar	381	(2)
Accessing the PivotTable List Menus	383	(1)
Understanding the Terminology and Display of a PivotTable List	384	(2)
Dragging Fields to the PivotTable List	386	(7)
Using the PivotTable Field List Window	393	(1)
Managing the PivotTable List	394	(14)
Working with Total Fields	394	(8)
Working with Data Fields	402	(4)
Exporting Data to Microsoft Excel	406	(2)
Formatting the PivotTable List	408	(7)
Configuring Display Behavior Options	409	(2)
Formatting and Renaming Captions and Fields	411	(4)
Trying It Out in the Real World	415	(2)
Getting Down to Business	415	(1)
Reviewing What You Did	416	(1)
Chapter Review	417	(1)
Closing Thoughts	417	(2)
Part III Appendixes	419	(2)
Appendix A Configuring Your System	421	(6)
Displaying File Extensions	421	(2)
Creating the NorthwindCS SQL Database	423	(4)
Appendix B SQL Reference	427	(14)
Dissecting a Basic SQL Query	428	(3)
Working in the Select	428	(2)
Working in the From	430	(1)
Working in the Where	430	(1)
Working with More Sophisticated SQL Queries	431	(9)
Dissecting a Sophisticated SQL Query	431	(1)
Using String Functions	432	(2)
Using Case Logic	434	(1)
Using Aggregate Functions	435	(1)
Joining Tables	436	(1)
Using Operators	437	(2)
Sorting the Result Set	439	(1)
Appendix Review	440	(1)
Index	441