Grouping by Formula. A Report Design Tip.

It is helpful to remember that Crystal Reports will allow you to group by just about any formula. This allows you to create groups that are not immediately available as fields in your database tables.

For example say your boss is annoyed that your report is only displaying data by some obscure in house sku number. Getting the product description from a different table is often possible by adding the table and linking, but in some cases that data is not readily available to you.

You could instead, create a formula that parses the skus for you.

if {itemkey}="0343243" then "Widget"

else if {itemkey="0343244"} then "Coffee Can, Black"

else "Misc. Item"

The values would then show up as nice tidy groups (the groups would show up as Widget, Coffee Can, and Misc Item Naturally if you have thousands of codes you will have to find other ways).

Also note that you don't necessarily have to test for just one field! In the above example for instance, you could add another else if that checked for total sales above certain amounts, and make a handy new category. (Say your widget sold more than $1,000,000, you could have the formula return Hot Item - Widget - Sales Great than One Million or whatever would be useful.

Keeping in mind when creating a report that grouping by formula is an option, you can often provide far more flexible reports than simply using database fields!

Crystal Reports (henceforth "Crystal") is a reporting tool that can produce reports from a number of data sources including ODBC, JDBC, Microsoft Access databases, Microsoft Excel spreadsheets, flat files, and many others. It is the de facto standard for report generation. Besides working as an application in its own right, it can be tied in with C, .net/C#, Visual Basic, and soon Java through integration with Sun Microsystems' Java IDE, Eclipse. The product is available both as a standalone application and as a server that can generate reports for the web (or other clients) in a just-in-time fashion. Crystal can also export to a wide variety of formats, most notably HTML and PDF.

Crystal has changed hands a number of times (it is now held by Business Objects and was previously most famously owned by Seagate) and versions still more times; It is now up to version 11, known as Crystal XI. Most of my experience has been with Crystal Reports version 9, which is the least recent version anyone is still putting any effort into supporting. Often, software vendors will bundle the same antiquated version of Crystal with their software for months or even years after the new version comes out. Crystal has only one file format, and opening reports with new features in older versions of Crystal will often cause problems.

Crystal is conceptually simple. There exists a design view where database fields, formula fields, and other elements are combined to produce a report; and a preview view which will show you in a fairly faithful fashion what a report will look like when printed (or exported to PDF.) Most export methods are not worth much, especially that which targets Microsoft Word. The software has two primary purposes; first, to produce quality printed output, and second, to create dynamic reports. These reports can show additional detail when their elements are clicked into, which is known as "drilling down".

Unfortunately, Crystal is a convoluted mess with no finesse whatsoever. Many of the existing options are extremely limited and much of the functionality you would expect is utterly absent. Much of the time, the user finds themselves engaged in programming tasks (utilizing either a subset of Visual Basic, or another extremely limited scripting language with a very similar syntax) which should in no way be necessary.

For example, there is no way to calculate a conditional median without writing code. It is possible to create summaries of displayed information, and in fact you can take the median of a summary. For those who are not familiar with the process of calculating the median of a list of values, one first sorts the list, and the result is the middle value of the list, or the average of the middle two values if there are an even number of list elements. Crystal contains functions which perform a number of summary functions on arrays (lists) including average and mode, but median is not one of them. At the same time, there is no array sort function! If you would like to be able to have a single report with (for example) median age, median age of males, and median age of females, it will be necessary to implement both an array sort algorithm and a function to derive the median value of an array.

Even more pathetic is the fact that you cannot chart data created while "printing" (formatting) records without the use of a subreport. Sound silly? This is further compounded by the fact that you cannot pass an array to a subreport, though nearly all graphs work, if not on summaries, then on arrays, elements of which are selected by a reserved variable. In order to pass an array to a subreport it must be converted into a string of not more than 65,534 characters (216 - 2) and then converted back to an array upon reaching the subreport!

This is not the end of Crystal's amazing lack, however. You also cannot write custom functions that return arrays, nor can your functions reference external variables, even though Crystal has both Global and Shared variables. Global is actually a serious misnomer in that Global variables are available only to other formulas in the same report section, while Shared variables are available everywhere. There is no variable scope in between the two. Global is the default scope, and Local variables are also provided.

Many of Crystal's limitations derive from the fact that it attempts to be a good database user by retrieving the minimum number of rows possible. This in turn means that it crafts fairly precise SQL SELECT statements when retrieving data, in order to minimize returned rows. These statements are derived from the record selection formulas, which therefore cannot use values derived during report processing. In order to do that type of secondary processing, it is therefore required that you create groups and provide them your other criteria.

Finally, the online help is utterly pathetic. This is true of at least versions 6, 9 and XI. You can actually search on a function's name, with nothing else, and yet the function you were looking for will be halfway down the list of topics, below items which don't even have any of your search terms in the title. It is necessary to constantly search Business Objects' website to supplement the documentation.

If you think Crystal Reports might be the right solution for your problem, and there is any alternative, think again. While fairly satisfying results can be derived from using some programming language to gather data and pre-cook it before passing it to crystal, that eliminates most of the program's usefulness to begin with.

Log in or registerto write something here or to contact authors.