Like wasting money? Don't read this.

[Former Member]

Full disclosure: this blog post is based on a presentation I gave when I was invited to the Mastering Business Objects 2009 conference in Sydney.  I suppose they could have some sort of intellectual property claim on this information, but A) it isn't proprietary, B) the demo during the presentation blew up -- they tastefully referred to this phenomenon as "gremlins", which is much more polite than saying "ill-prepared foreign speaker just pooped the bed" -- and I wasn't invited back, and C) the conference team is awesome,  really vested in the community, and probably have no interest in flying me back down there to do anything about it.  So we'll go on, IP concerns notwithstanding.

The Intro

Assuming you've read the marketing materials, you know that Crystal Reports is very, very good at one thing: crunching lots of numbers from a relational database and displaying that information in a "pixel-perfect" format.  The marketing materials stress that because that is how you justify the purchase, and those same marketing guys (who probably owe me a drink, or, even better, a slot in a golf tournament) really just need that purchase justified.  But you need more that that, don't you.  You need to stretch your IT dollars and wring as much value out of every purchase as you can.  I'm here to give you a few tips on how you can get a lot more out of your meager Crystal investment by poking around the Available Data Sources area, and you won't even have to enter me in a golf tournament to do it (although I'll happily play, I'm admittedly terrible).  

Think about the possibilities of taking "non-traditional" data (that most people don't normally think about as data at all) and combining it with "traditional" data sources (any relational database you'd like) into a beautifully-formatted, presentation-ready report you'd be proud to put your name on.  Can you think of a scenario where that would be useful?

No?  Probably my fault for not setting that up better -- don't blame yourself.  Let me give you a scenario: your boss wants a report that will show each regional manager their sales, some dimensional data that only exists in Excel, a count of how many customer service emails they got in the last month, and how much space they are taking up on your SAN, and they want it all without writing a single piece of ETL.  Why they want all that seems a bit odd, but you couldn't even come up with a scenario, so deal with it.  Just for fun, we'll even throw in what people are saying about your store on twitter.  Which is #awesome!  Now let's look at some possible data sources.

The Table (because no blog post is complete without one)

Data Source	Pros	Cons	Best Practices
Excel Spreadsheets	Users can easily update. Great way to make use of informal data.	Users can easily update.  Very little – if any – data integrity exists in most spreadsheets.	Encourage use of Validation in spreadsheets. Create comparison tables/spreadsheets of your own to deal with non-matching dimensions.
Universe Queries	Leverage existing investment. Improve accuracy between Deski/Webi and Crystal Reports.	Not always as efficient during runtime as going against the database.	Be aware of data granularity – it isn’t always handled as effortlessly as in BusinessObjects.
File System	Lots of information available without any special coding.	Cannot report off of the body of the files.	Best used to determine when information was last updated/accessed. Be specific about the file/directory you want to look at.
Web Services	Make use of publicly available information. Leverage SOA investment if applicable.	Web Services can be tricky to deal with. Public services can be updated without your input.	Check public web services regularly to make sure they are still providing the data you want.
Outlook	Good way to gauge activity surrounding a specific subject area.	Requires stringent rules to only view emails that you want.	Be specific about the data you want. Don’t show body of emails, show subject at most.

A Treatise on the Combining of Data

Just wanted to take a quick minute to say that here are two basic options when it comes to combining data, and, as almost always, have some thoughts on both.

Option 1: Combine them directly on the report.

• Good for varied data sources.
• Quick turnaround for one time reports.
• Can run pretty slowly.

Option 2: Combine them in MS Access, then report off of that.

• Good for multiple Microsoft data sources.
• Leverages database engine in linking tables to each other.
• Can easily create macros, functions to cleanse data.
• Can aggregate info to appropriate grain before it gets to report.

I don't really have a ton more to say about that, except that if you are going to run this report a lot, I'd go with Option 2 whenever possible.

Tips to avoid getting in over your head

• Watch out for files that move and get renamed.
• Often better to run on desktop rather than publishing across network.
• Beware security, especially across network or through BO Enterprise.
• Be proactive about dimension matching and data integrity.
• Be aware or data granularity and aggregation issues.

The Big Finish

Crystal Reports, when used carefully, can provide a polished presentation layer for non-traditional data sources.  With a little savvy and attention to detail, report viewers will see a clean, professional presentation, and you will have squeezed a little more value out of a sunk cost.  And I think we all know that extra value + no extra investment + bells & whistles are what get you promoted. 
And maybe even free entry into a golf tournament.