SAP Crystal Reports

3 Posts authored by: James Oswald

This is an entry in the ongoing Dear aBI series. To learn more, visit our wiki.

The Question

On Hold in Armenia asks:

     I'm learning Crystal Reports, I'll be grateful if you help me.

That is a far, far broader question that we'd normally try to take on in Dear aBI, but I thought it might be worth discussing where to find information to help you get started in any SAP Business Intelligence tool.

The Answer

Fortunately, SAP provides you with a lot of training options.

The first is obviously SAP certified education. I've sat through some of the official training on various tools, and they do a pretty thorough job of getting you up to speed on the software. They can be on the expensive side, but you know you'll get education delivered in a reliable way from a proven curriculum and you should be able to get up and run when you get back to your office. They also have online options available if you don't have an authorized training center near you.

Another option for training is to get someone to customize training and materials for you. Typically you can find an SAP BusinessObjects partner locally familiar with the tool (and that has probably provided the formal training before) who would be willing to customize your training to your needs specifically. Don't have anyone specifically to recommend in Armenia, but feel free to contact me if you'd like a name a little more United States-y.

Want something a little less formal? Conferences and user groups are another great way to get to know the tools (and, perhaps more importantly, to get to know the people who know them really, really well). Again, I can only speak first-hand about Making Sense of the 2011 BusinessObjects Conference Landscape, but I know SAPPHIRE and SAP TechEd will be combined in Madrid this year, and that's probably a great opportunity to get to know about the products and get your hands on them all in one location.

Don't have the money or the time for those options? There are a lot of free online video resources as well. Nic Smith shared some information on BI Tutorials (including a link to Official Product Tutorials – SAP BO BI Suite ). For Crystal specifically, you can also turn to some of the work done by the Reportapalooza experts last year. Also, what with it being 2011 and all, you can just hit up YouTube and see what comes back.

Like the idea of online, but not into video? Try blogs by folks like Dallas MarksDave Rathbun, and Michael Welter to learn more about BusinessObjects, or hit the SAP Community Network and the BOB forums for specific questions.

Fan of book learning? Try to hit up SAP Press or Amazon to find a good title.

Want to indulge a shameless plug of my own (free) podcast? Head on over to the Diversified Semantic Layer where we just talked about SAP education and certification.

Your Turn

I know we all have our favorite resources for learning a new tool. What's yours?

For all of my fans here because of Reportapalooza, hurry it up and go submit your dashboards and training videos and download my tools. Whether you are here for Reportapalooza or not, you'll probably want to check out my first post in this series.

Connectivity What?

The thing, at least to me, what really sets SAP Crystal Interactive Analysis apart from the classic Web Intelligence application is the ability to connect to personal data files. While the connection options are not quite as robust as they are in Like wasting money?  Don't read this., they certainly meet the needs of the non-developer-yet-still-power-user to interact with standard and non-standard data.  

What Is Non-Standard Data?

Slow down, buddy. First let's talk about standard data. I'm defining standard data as data coming from either a relational or columnar database that can be accessed using some variant of SQL syntax. Non-Standard data is, by definition, any data that isn't that. The most common types of non-standard data are Excel files or Excel-like text files.  We've all heard of "spreadmarts", and we all know "Phyllis" at our work who has that one stupid, painfully-annoying piece of data that we just can't seem to get loaded into the data warehouse. Which is really so dumb, because that piece of data is only valuable when combined with more data from the data warehouse. Regardless, some spreadsheets will always exist, and Interactive Analysis gives you an easy way to connect to it. Just begin editing your query, drag in the new source, and play with it just like you would with any other data source.

What about Standard Data?

Because this leverages standard BusinessObjects Universes, reporting off of an existing universe is a snap. As far as I can tell, accessing non-Universe data is limited to data sources that have a Universe built on them. Once you have the connection, you simply add any object from that universe, edit the SQL, and make it say whatever you want. Again, not something your average power user will need to do, but the power is there.


  Now What?

While Interactive Analysis doesn't have absolutely every data connection you could ever want, it absolutely has everything your average power user needs. It goes well beyond the standard Web edition.

Please come back next week to see what I've found about this playing with on-demand!

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.


Filter Blog

By author:
By date:
By tag: