Crystal Xcelsius For Dummies

Published by
Wiley Publishing, Inc.
111 River St.
Hoboken, NJ 07030-5774
www.wiley.com

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 .

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered 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. 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.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: The publisher and the author make no representations 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 particular 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 professional 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, 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.

About the Author

Michael Alexander is a Microsoft Certified Application Developer (MCAD) with over 14 years experience consulting and developing office solutions. He is the author of several books and the principle contributor at www.datapigtechnologies.com , where he shares free Access and Excel tips to intermediate users. He currently lives in Frisco, Texas where he works as a Senior Program Manager for a top technology firm.

Dedication

For my wonderful family: Mary, Ethan, and Emma.

Author’s Acknowledgments

Thank you to Kirk Cunningham for all his help in getting this project off the ground. Thank you to Jaime Zuluaga for all of the wonderful ideas, tips, and tricks. Thank you to the Crystal Xcelsius technical team for answering all my questions. Thank you to Santiago and Santi Becerra for creating this groundbreaking program. A big thank you to Loren Abdulezer, a superb Technical Editor who kept me honest and sparked some great ideas. Thank you to Greg Croy for taking a chance on this book about a new product. Many thanks to Chris Morris and the brilliant team of professionals at Wiley Publishing who helped bring this book to fruition. And a special thank you to my beautiful wife Mary for supporting all my crazy projects.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/ .

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Media Development

Project Editor: Christopher Morris

Executive Editor: Gregory S. Croy

Senior Copy Editor: Teresa Artman

Technical Editor: Loren Abdulezer

Editorial Manager: Kevin Kirschner

Media Development Manager: Laura VanWinkle

Editorial Assistant: Amanda Foxworth

Cartoons: Rich Tennant (www.the5thwave.com )

Composition

Project Coordinator: Michael Kruzil

Layout and Graphics: Andrea Dahl, Heather Ryan

Proofreaders: Jessica Kramer, Techbooks

Indexer: Techbooks

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

Title

Introduction

About This Book

Foolish Assumptions

How This Book Is Organized

Icons Used In This Book

Part I : Say Hello to Crystal Xcelsius

Chapter 1: Introducing Crystal Xcelsius

Overcoming Static Cling

More Than Just Fancy Graphics: The Benefits of Using Crystal Xcelsius

Crystal Xcelsius under the Hood

Chapter 2: Taking Crystal Xcelsius for a Spin

Creating Your First Dashboard

Getting Fresh with your Dashboard

Part II : Getting Started with the Basics

Chapter 3: Interacting with Single Value Components

Managing Interactivity: Input vs. Output

Understanding Scale Behavior

Building a Loan Payment Calculator

Chapter 4: Calling Attention to Alerts

The Anatomy of the Alert Tab

Applying Your First Percent Alert

Applying Your First Value Alert

Chapter 5: Getting Graphic with Charts

The Basics of Crystal Xcelsius Charts

Creating Your First Chart

Changing Chart Appearance

Understanding Combination Charts

Chapter 6: May I Please See the Menu?

Delivering Choices with a Selector Component

Understanding the Insert Option Property

Working with the Filter Component

Creating Your First Filtered Dashboard

Part III : Get Fancy with Advanced Components

Chapter 7: Getting Geo-Graphic with Maps

Understanding the Concept of Regions

Creating a Basic Map-Based Dashboard

Applying Alerts to Map Components

Dynamically Feeding Data to Map Components

Chapter 8: Focusing in on Dynamic Visibility

Seeing the Basics of Dynamic Visibility

Implementing Menu-Based Visibility

Chapter 9: Working with Crystal Xcelsius Professional

Creating Drill-Down Charts

Using Accordion Menu Components

Working with Picture Menus

Using Interactive Calendar Components

Part IV : Wrapping Things Up

Chapter 10: Adding Style and Personality to Your Dashboards

Employing the Five Formatting Must-Haves

Adding Style and Personality

Discovering Skins and Templates

Chapter 11: Taking Your Dashboards to Market

Exporting a Visual Model

Paying Attention to Distribution Matters

Sending Data Back to Excel

Incorporating Input from Others

Part V : The Part of Tens

Chapter 12: Designing Effective Excel Models: Ten Best Practices

Best Practice #1

Best Practice #2

Best Practice #3

Best Practice #4

Best Practice #5

Best Practice #6

Best Practice #7

Best Practice #8

Best Practice #9

Best Practice #10

Chapter 13: Ten Cool Crystal Xcelsius Tricks

Creating a Waterfall Chart

Password-Protecting Your Dashboard with Dynamic Visibility

Highlighting the Below-Average Data Points in a Chart

Making a Data Series Disappear and Reappear

Creating a Scrolling Chart

Using Conditional Formatting to Create Regions on a Map

Making Your Own Map Component

Adding an Export to PowerPoint Button

Nesting a Dashboard within Another Dashboard

Using Crystal Xcelsius to Build Your Web Site

Chapter 14: Frequently Asked Questions (Two Sets of Ten)

Basic Questions about Crystal Xcelsius and Excel

Common Error Messages and What They Mean

Commonly Asked Component Questions

Chapter 15: Ten (or So) Real World Examples

Load Optimization (Logistics)

Instructor Staffing (Education)

Basic ROI Calculator (Finance)

Service Outage Analyzer (IT)

Fuel Cost Analysis (Transportation)

Software Development

Site Statistics (Web Site Management)

Google AdWords Tracker (Marketing)

Headcount Visibility Reporting (HR)

Appendix: Going Beyond Spreadsheets

The More Things Change, the More They Remain the Same

Digital Dashboards and Visual Models

Deployment Issues

Concluding Remarks

Introduction

I like to pretend that I’m a young man, but then I remember that one of my first jobs was typing up orders on a Wang computer. For you spring chickens who don’t remember Wang computers, let’s just say they don’t make them anymore. Anyway, my point is that I’ve been in the business world a long time. I remember the mad rush to invest in large data warehouses and enterprise reporting tools. These tools came with the promise of business intelligence, affectionately called BI. Business intelligence is what you get when you analyze raw data and turn that analysis into knowledge. BI can help an organization identify cost-cutting opportunities, uncover new business opportunities, recognize changing business environments, identify data anomalies, and create widely accessible reports. Unfortunately, data warehouse and enterprise tools of the past had analysis and reporting capabilities that were clunky at best and not very user-friendly. This left many business professionals using tools such as Lotus 1-2-3 and Excel to analyze and report data.

Fast-forward about a decade later, and you’ll see that a lot has changed. The Internet is now a cornerstone of business, new technologies have emerged to enhance the quality and performance of Web reporting, and even the previously clunky BI tools can now provide analytical capabilities that are both robust and user-friendly. Nevertheless, even with all these advances in business intelligence capabilities, most of the data analysis and reporting done in business today is still done by using a spreadsheet: that’s right, our old friend Excel, which has remained more or less unchanged for the last ten years. Make no mistake — no matter how advanced an IT manager thinks his enterprise system is, Excel is embedded somewhere in that organization’s data pipeline.

This is where Crystal Xcelsius enters the scene. Unlike other enterprise solutions, Crystal Xcelsius doesn’t try to replace Excel or to take away its need. Instead, Crystal Xcelsius works with Excel to create interactive visualizations by using Excel’s data and functionality. With Crystal Xcelsius, users no longer have to feel bad about using Excel in an environment that touts high technology. Crystal Xcelsius allows Excel users to turn their spreadsheets into professional looking dashboards, scorecards, what-if visualizations, or even highly polished PowerPoint presentations. The best thing about Crystal Xcelsius is that with its user-friendly click-and-drag interface, anyone can create highly compelling dashboards in minutes. So ignore SAP for a while. Close out your Crystal Reports, and log off of your Panorama and Cognos portals. Fire up the stalwart Excel and take an in-depth look at this fabulous new program called Crystal Xcelsius.

About This Book

The chapters in this book are designed to be standalone chapters that you can selectively refer to as needed. These chapters provide you with step-by-step walkthrough examples as well as instruction on the wide array of functionality that Crystal Xcelsius has to offer. As you move through this book, you will be able to create increasingly sophisticated dashboards using more advanced components. After reading this book, you will be able to

Create basic dashboards with charts, gauges, and sliders.

Add advanced functionality to your dashboards such as alerts, maps, and dynamic visibility.

Create interactive business calculators and what-if analysis tools.

Integrate Crystal Xcelsius models into PowerPoint presentations.

Create Crystal Xcelsius–based Web pages.

The three versions of Crystal Xcelsius are Standard, Professional, and Workgroup. In this book, I focus on the components and functionalities of Crystal Xcelsius Standard and Professional. If you use Crystal Xcelsius Workgroup, you will find that much of the information found here still applies to your version. However, this book doesn’t cover the collaboration and the enterprise-level functionality of Crystal Xcelsius Workgroup.

Foolish Assumptions

I make three assumptions about you, the reader:

Given that you’re even reading this book, you’ve already bought and installed Crystal Xcelsius.

You are a relatively experienced Excel user familiar with basic concepts, such as referencing cells and using formulas.

You have enough experience with PowerPoint to add objects, resize objects, and run a presentation.

How This Book Is Organized

The chapters in this book are organized into five parts, each of which includes chapters that build on the previous chapters’ instruction. As you go through each part, you will be able to build dashboards of increasing complexity until you’re a Crystal Xcelsius guru.

Part I: Say Hello to Crystal Xcelsius

Part I is all about introducing you to Crystal Xcelsius. In Chapter 1, I share with you the various ways you can use Crystal Xcelsius as well as the core concepts that make Crystal Xcelsius components work. In Chapter 2, throw caution to the wind and create your first dashboard — without reading the instructions. At the end of Chapter 2, you will have a firm understanding of the fundamentals of using Crystal Xcelsius, including importing data, working with components, publishing your dashboard, and refreshing your data.

Part II: Getting Started with the Basics

In Part II, I take an in-depth look at some of the basic components that are key to any dashboard. In Chapter 3, I show you how Single Value components work and how to use them to build interactivity into your dashboards. In Chapter 4, I show you how to leverage alerts to enable conditional coloring in your components, allowing your audience to get an instant visual assessment on performance. Chapter 5 is all about creating charts in Crystal Xcelsius. I wrap up this part with Chapter 6, where I show you how to easily build menus and selectors into your dashboards with Selector components.

Part III: Getting Fancy with Advanced Components

In Part III, I go beyond the basics to take a look at some of the advanced components that Crystal Xcelsius has to offer. In Chapter 7, I demonstrate the different ways you can use Map components to add flair to your visualizations. In Chapter 8, I walk you through the basics of dynamic visibility and look at some examples of how dynamic visibility can help achieve focus on the parts of your dashboard that are important. Chapter 9 focuses on the advanced components and functions that are found only in the Professional version of Crystal Xcelsius, discussing how each can be used to enhance your visual models.

Part IV: Wrapping Things Up

Part IV focuses on the last two actions a user takes when wrapping up the production of a dashboard: formatting and distribution. Chapter 10 focuses on the functions and utilities that enable you to show off your artistic side and add your own style to your visual models. In Chapter 11, I show you just how easy it is to take your dashboards to market, and I share a few other tricks on how to share the data in a visual model.

Part V: The Part of Tens

Part V is the classic Part of Tens section found in every For Dummies title. The chapters here each present ten or more pearls of wisdom, delivered in bite-sized pieces. In Chapter 12, I share with you ten best practices that will help you design Excel models that allow you to go beyond simple dashboards. In Chapter 13, I share ten of my best Crystal Xcelsius tricks, making ordinary components do extraordinary things. Chapter 14 focuses on answering some of the questions that I hear most often. Chapter 15 covers real-world examples of Crystal Xcelsius in the workplace.

Lastly, the appendix at the end of the book contains an essay by Loren Abdulezer about the significance of the paradigm shift that Crystal Xcelsius represents.

Icons Used In This Book

Sometimes I have to talk about certain technical things in order to keep my guru mystique. These things are interesting but not crucial, so I mark them with this icon. You don’t need to read them, but for some of the more tech-savvy of you, they may be useful.

Tips are suggestions to make your life easier. Skim these nuggets for timesavers, tricks, and just plain cool moves.

These notes denote info you ought to think about, but they’re not going to cause a disaster if you don’t pay attention.

Be sure to read text marked with this icon! If you do not follow a warning, bad things can happen: Puffs of black smoke might come out of your monitor, your workspace could be deluged by a plague of frogs, or your program simply won’t work right.

This icon denotes subject matter about which you can find more on the World Wide Web. For the most part, the icon is used to point out examples you can download from this book’s companion Web site at www.dummies.com/go/xcelsius .

Part I

Say Hello to Crystal Xcelsius

In this part . . .

In this part, you are introduced to Crystal Xcelsius. In Chapter 1, I share with you the various ways you can use Crystal Xcelsius as well as the core concepts that make the Crystal Xcelsius components work. In Chapter 2, throw caution to the wind and create your first dashboard — without reading the instructions. By the end of this part, you should have a firm understanding of the fundamentals of creating dashboards in Crystal Xcelsius.