Skip to Content
Author's profile photo Former Member

Like wasting money? Don’t read this.

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.


  • 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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Tammy Powlas
      Tammy Powlas
      Thanks for posting.  Excellent presentation...sorry for the Australia troubles.
      Author's profile photo Former Member
      Former Member
      So, here is what I want to understand - Crystal can read ABAP DDIC tables and report off them beautifully. Several clients already do this. But I am told rest of BOBJ products do not do this - is there a good reason?
      Author's profile photo Former Member
      Former Member
      Thanks for the note, Vijay.  Not knowing anything about ABAP DDIC tables (but guessing they are service-y type things), I'm thinking they aren't supported in BOBJ proper for the same reasons that XML and web services aren't.  The semantic universe in BOBJ is really all about creating rules to write SQL to execute against a database.  With these service-y type jobbers, you can't really do that.  In future releases of BOBJ, I believe they are going to incorporate Data Federator principles, which, I would guess, will make reading that sort of thing possible.  I wonder what Ingo thinks?
      Author's profile photo Former Member
      Former Member
      These are regular relational tables, Jamie - like Purchase order header and detail type tables.
      Author's profile photo Former Member
      Former Member
      Well then I just have to assume SAP is no longer going to support ABAP. 🙂  Really, though, I think it'll be there in the next release.
      Author's profile photo Former Member
      Former Member
      Thanks Jamie, good value in your post. For what it's worth, I'd welcome you back this year... Maybe next year? 🙂

      We recently got asked about a project involving a daily newsletter that was emailed out.  It needed to pull data from multiple web services as well as SAP R/3 tables, plus have a regularly changing picture and main article.

      This type of requirement can only be met by Crystal Reports, and can be almost fully automated.

      I believe Crystal Reports still plays a valuable part of the SAP BusinessObjects stack.

      Cheers, Josh

      Author's profile photo Former Member
      Former Member
      Hi Jamie!! Nice post!

      I recently had a hand at this when I was asked to come up with an idea to replace our current conference room management software.  It creates lots of pretty reports on conference room utlization, using the outlook plug in I can create those too!  No more need for expensive conference room software!

      Author's profile photo Former Member
      Former Member
      Woo hoo!  I'll be waiting for my check (65% of your previous conference room software spend for the first year seems fair).
      Author's profile photo Former Member
      Former Member
      Hey Jamie,
      Nice post. Recently we also used CR2008 for XML data. CR did a great job in in reading and reporting xml data.

      You mentioned 'twitter' as a data source -- is there a way to access this information - what's being said on  our company's twitter (or facebook etc.) accounts?

      Author's profile photo Former Member
      Former Member
      Author's profile photo Former Member
      Former Member
      You can also use various twitter web services to call specific items.  Timo Elliott does a great job showing the functionality off in a few blog posts on Xcelsius (but if there is a web service, Crystal can use it).

      Author's profile photo Jamie Wiseman
      Jamie Wiseman
      thanks for this helpful post, Jamie.

      for the file system data, you can report off of the contents of the file system in certain cases...i.e. where there is a file type that the cr non-relational drivers can read off of...for example, .txt files etc.

      here's a link to a sample report which shows how to do this.

      Author's profile photo Former Member
      Former Member
      Thanks for adding on, Jamie (so tastefully named).  Great post yourself!