Karla News

Software Reviews – Crystal Reports

Software Reviews, Sql

Crystal Reports is a dynamic program that allows for a decent degree of customization. For the sake of simplicity, I have listed the pros and cons below, each based on my own experiences with this program over the past few years.

Pros:

  • Has a nice wizard that helps less experienced users select tables and create table joins.
  • Lets you write your own SQL statements to query the database.
  • Create and modify complex cross-tabs.
  • Prompts! Crystal Reports calls these parameters, but no matter what you call them, they’re the best way to create reports for end-users.
    * Prompt formats include Boolean, String, Currency, Number, Date, Date-Time, and Time.
    * You can allow users to enter their own responses, or make them stick to a list of values.
    * You can allow for ranges, multiple values, and you can force a max/min value.
    * You can also build a prompt that allows values based on a field in the database – a real time-saver if values tend to change frequently
  • Formula fields allow you to create calculations that reflect one or more combinations of field and parameter values.
  • Optional formatting lets you highlight a field based on a value. It’s hard to figure out, but you can do the same thing for other fields, too.
  • Sub-reports let you display information based on the current record, and you can even link to another report, so it opens on command.
  • Selections (filters) allow you to only show the data you want on the report.
  • You can create a Selection based on parameters.
  • You can write a report based on more than one database, or you can write a report that pulls in data from an Excel spreadsheet (just make sure no one moves the file, or your report will break).
  • Sorting, grouping, formatting, and exporting options let you handle a lot of different requirements. Although you can’t sort based on a parameter, you can build a Formula field that accounts for parameter values, and sort based on that.
See also  Guide to Microsoft Access JOIN Statements

Cons:

  • Page formatting options – it would be nice to have a cover page in landscape, and the rest of the pages in portrait, but you can’t. I guess you could run two separate reports.
  • Summaries of data are calculated as the report “prints”. That means you can’t display your summaries up front, and you can’t use summarized data in most of your formula fields.
  • In the standardized version, you can’t build cascading prompts (parameters) – that means that you can’t show the user a list of values within a prompt .based on their selection in a previous prompt. You can, however, access this handy feature in Crystal Reports Pro.
  • You can’t link to a sub-report in a cross-tab report.
  • You can’t create, or link to, a sub-report within a sub-report.
  • Crystal Reports allows for record-level, and group level selections (filters). I was really excited about this when I first saw the option, but I’ve never found a use for the group level selections.

I’ve been using Crystal Reports for about 3 years now, and before that, I’d been designing reports using the COGNOS Suite of reporting tools. While at first disappointed with Crystal Reports, I quickly found that all the shortcomings I found could be solved by writing my own SQL statements to interact directly with the database.

This includes the most difficult of situations I’ve encountered in Crystal Reports, filtering data based on a summary. To handle this, I create an SQL statement that calculates the necessary summaries, and then I filter based on that information.

I know that the wizard that helps a user select tables and create joins is a robust tool, but to be honest, only one report I created that is currently in use is based on that wizard. I found that the complexities of reporting data, and filtering appropriately, mean that I always need at least one custom SQL statement.

See also  RDBMS Components

Overall, I haven’t yet found a report that I couldn’t make Crystal Reports produce, and that speaks volumes for a robustness well beyond my initial impressions.