Practical Reporting with Ruby and Rails (Expert's Voice in Open Source)

314 Pages·2008·4.01 MB·English

by  David Berube

cyan yelloW
MaGenTa Black
panTone 123 c
Books for professionals By professionals® The eXperT’s Voice® in open source
Companion
eBook
Author of Available
Practical Reporting with Ruby and Rails
Practical Ruby Gems
Practical
Dear Reader,
R
Perhaps the most important skill any commercial Ruby programmer can have Practical
is to write reports for data from disparate data sources. Practical Reporting with e
Ruby and Rails will show you how to do just that, using concrete, real-life examples.
p
In fact, this book covers three distinct concepts: how to load data from different
sources, how to interpret the data, and how to present the data. o
You’ll find out how to load data from a wide range of sources in various
r Reporting
formats, including web-based data sources like Google AdWords and eBay. I’ll
show you how to analyze data to produce meaningful reports using a variety t with
i
of techniques, from Active Record statistical functions to custom SQL. The
n
examples include conducting SugarCRM sales campaigns, analyzing data from
Apache web logs, and many other practical applications. g
Displaying the data visually can be the most important part. You’ll learn how

to present data on the Web and on the desktop. I’ll cover graphing using Gruff, w
Scruffy, CSS Graphs Helper, and Markaby, along with easy ways to create text Ruby Rails
i
and HTML reports. The examples demonstrate how to display reports as Excel t
h and
spreadsheets or deliver them as PDF files, as well as how to create a Windows

desktop tool that downloads data from a Rails web application into a Microsoft R
Access database.
That’s not all, though. This book also covers performance-enhancing techniques u
such as using Active Record Extensions, which let you import data at lightning
b
speed, and rolling your own SQL statements to optimize slow queries.
I hope you will enjoy learning about reporting as much as I enjoyed writing y
about it.

a
David Berube
n
d Create and present attractive reports, graphs,

and documents using Ruby on the Web, on the
R
Companion eBook
THE APRESS ROADMAP desktop, and on the server.
a
Practical Ruby Gems
i
Beginning Ruby
l
Practical Reporting s
with Ruby and Rails
See last page for details
on $10 eBook version Beginning Rails
Practical Rails Projects
SOURCE CODE ONLINE
David Berube
www.apress.com ISBN-13: 978-1-59059-933-4 B
e
ISBN-10: 1-59059-933-0
r
54299 u
US $42.99 b
e
Shelve in
Programming Languages/
Ruby
User level:
9 781590 599334
Intermediate
this print for content only—size & color not accurate spine = 0.729" 312 page count

9330FM.qxd 1/9/08 2:48 PM Page i
Practical Reporting with
Ruby and Rails
David Berube
9330FM.qxd 1/9/08 2:48 PM Page ii
Practical Reporting with Ruby and Rails
Copyright © 2008 by David Berube
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-59059-933-4
ISBN-10 (pbk): 1-59059-933-0
ISBN-13 (electronic): 978-1-4302-0532-6
ISBN-10 (electronic): 1-4302-0532-6
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence
of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Java™and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc., in
theUS and other countries. Apress, Inc., is not affiliated with Sun Microsystems, Inc., and this book was
written without endorsement from Sun Microsystems, Inc.
Lead Editors: Steve Anglin, Jason Gilmore
Technical Reviewer: Nick Plante
Editorial Board: Clay Andres, Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell,
JonathanGennick, Kevin Goff, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper, Frank Pohlmann,
BenRenow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh
Project Manager: Beth Christmas
Copy Editor: Marilyn Smith
Associate Production Director: Kari Brooks-Copony
Production Editor: Liz Berry
Compositor: Dina Quan
Proofreader: April Eddy
Indexer: Broccoli Information Management
Artist: April Milne
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail [e-mail], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,
Berkeley, CA 94705. Phone [phone], fax [phone], e-mail [e-mail], or visit
http://www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our Special
Bulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every precau-
tion has been taken in the preparation of this work, neither the author(s) nor Apress shall have any
liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly
or indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.com.
9330FM.qxd 1/9/08 2:48 PM Page iii
This book is dedicated to my parents.
9330FM.qxd 1/9/08 2:48 PM Page iv
9330FM.qxd 1/9/08 2:48 PM Page v
Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
PART 1 n n nIntroducing Reporting with Ruby
nCHAPTER 1 Data Access Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
nCHAPTER 2 Calculating Statistics with Active Record. . . . . . . . . . . . . . . . . . . . . . . 19
nCHAPTER 3 Creating Graphs with Ruby. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
nCHAPTER 4 Creating Reports on the Desktop. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
nCHAPTER 5 Connecting Your Reports to the World. . . . . . . . . . . . . . . . . . . . . . . . . . 75
PART 2 n n nExamples of Reporting with Ruby
nCHAPTER 6 Tracking Auctions with eBay. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
nCHAPTER 7 Tracking Expenditures with PayPal. . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
nCHAPTER 8 Creating Sales Performance Reports with SugarCRM . . . . . . . . . . 155
nCHAPTER 9 Investment Tracking with Fidelity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
nCHAPTER 10 Calculating Costs by Analyzing Apache Web Logs. . . . . . . . . . . . . . 189
nCHAPTER 11 Tracking the News with Google News. . . . . . . . . . . . . . . . . . . . . . . . . 215
nCHAPTER 12 Creating Reports with Ruby and Microsoft Office . . . . . . . . . . . . . . 233
nCHAPTER 13 Tracking Your Ads with Google AdWords . . . . . . . . . . . . . . . . . . . . . . 261
nINDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
v
9330FM.qxd 1/9/08 2:48 PM Page vi
9330FM.qxd 1/9/08 2:48 PM Page vii
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
PART 1 n n nIntroducing Reporting with Ruby
nCHAPTER 1 Data Access Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Choosing a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Using Active Record As a Database Access Library . . . . . . . . . . . . . . . . . . . 5
Calculating Player Salaries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Calculating Player Wins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
nCHAPTER 2 Calculating Statistics with Active Record. . . . . . . . . . . . . . . . . 19
Grouping and Aggregation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Analyzing Data with Grouping and Aggregates . . . . . . . . . . . . . . . . . . . . . . 22
Calculating Salary Distribution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Calculating Drink/Win Distribution . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
nCHAPTER 3 Creating Graphs with Ruby. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Choosing a Graphing Utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Graphing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Creating a Line Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Creating a Line Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
vii
9330FM.qxd 1/9/08 2:48 PM Page viii
viii nCONTENTS
nCHAPTER 4 Creating Reports on the Desktop . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Choosing a Desktop Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Exporting Data to Spreadsheets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Generating an Excel Spreadsheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Creating a Spreadsheet Report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Creating GUIs with Ruby. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Using FXRuby. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Graphing Team Performance on the Desktop. . . . . . . . . . . . . . . . . . . 63
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
nCHAPTER 5 Connecting Your Reports to the World . . . . . . . . . . . . . . . . . . . . 75
Choosing a Web Framework. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Live Intranet Web Reporting with Rails. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Setting Up the Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Creating the Models for the Web Report. . . . . . . . . . . . . . . . . . . . . . . 82
Creating the Controller for the Web Report. . . . . . . . . . . . . . . . . . . . . 85
Creating the View for the Web Report . . . . . . . . . . . . . . . . . . . . . . . . . 85
Examining the Web Report Application . . . . . . . . . . . . . . . . . . . . . . . . 87
Graphical Reporting with Rails . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Creating the Controller for the Graphical Report . . . . . . . . . . . . . . . . 92
Creating the Models for the Graphical Report. . . . . . . . . . . . . . . . . . . 95
Creating the View for the Graphical Report. . . . . . . . . . . . . . . . . . . . . 96
Examining the Graphical Reporting Application. . . . . . . . . . . . . . . . . 99
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
PART 2 n n nExamples of Reporting with Ruby
nCHAPTER 6 Tracking Auctions with eBay. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Using eBay APIs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Obtaining Competitive Intelligence via eBay Web Services. . . . . . . . . . . 113
Installing Hpricot and LaTeX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Coding the eBay Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131