Reporting Tools

March 24, 2017

This article compares Crystal Reports features to those of other reporting products. And to me, reporting includes more than visualization and summary tables.  It also includes operational reports (invoices, packing slips, etc) and financial statements.  That is why I am not currently including tools like Tableau that are focused on visualization.

The feature list is Crystal-centric because my readers are mainly Crystal users and the question I want to answer is this:

If I were to switch from Crystal Reports to another product, what features would I gain and what features would I lose?

I can’t list every feature of every product but I have started with a matrix of about 80 features that I have defined in the feature glossary.  Out of these 80+ features the ones I consider the most important are listed under the category Approach and are discussed below in more depth.  I have tried to limit the list to features that can be quantified or are obvious so you won’t see things like “ease of use”.  The list will evolve as I get more feedback from vendors, users and readers, so I hope to winnow out the features that highlight the most important differences.  If you have a feature to suggest, please Email me or give me a call:

Overall approach features:
The features in this section are the ones I consider most important to understand, and they are discussed in the next few paragraphs.  You will find them near the top of the grid. You will also notice that some of the grid cells have a blue background. These show where a product takes a different approach to a key feature than the approach used by Crystal.  (Yellow cells indicate features that need more research.)

Is a metadata layer required
In other words, does the person creating a report connect directly to a database connection or do they connect to a set of fields that are selected for them by someone else.  Newer tools seem to favor the second, two-tiered, approach where a technical person is responsible for assembling the required dataset in advance.  The end users then create reports by pulling elements from the dataset.  Users do not have to worry about connections, tables, joins or cryptic field names. The marketing language for these tools often says things like “self-service BI” or “without any training” or “without waiting for IT”.   In some environments this works.  But it also requires that someone be able to predict the dataset requirements in advance and then assemble the elements for the users.  Whenever requirements change and a data element needs to be added, then it is no longer self-service.

In this first round of eight tools, three require a metadata layer.

True Story:
I was on a consulting project years ago.  The requirements were complex and the data structure was poorly documented, so progress was slow.  Halfway through the project I noticed a brand new pile of boxes for Cognos Impromptu, another reporting tool.  I asked about the boxes and the project director told me that they were dropping Crystal for Impromptu because Crystal was too complicated to use.  They had been given a demo of Impromptu and were impressed with how simple it was to create and modify reports. So they had purchased $15,000 worth of Impromptu licenses.   Here is the subsequent dialogue:

Ken – “So who is going to create the Impromptu catalog?”
Director “What’s a catalog?”
Ken –  “Impromptu doesn’t read raw data, it only reads from a catalog.  The catalog maps the raw fields to friendly field names. The catalog has to be set up before you create any reports. Once you have the catalog built it is very easy to create reports from it.”
Director – [ long quiet pause ]
Director – “No one said anything about a catalog”.

They quickly found that all of those same data problems still had to be solved to build a catalog.

Is the report design interface designed for end users or programmers
Most of the tools have a graphic interface that is designed for end users.  Only two  have an interface that is geared more to programmers than it is to end users.  Any tool that is built on a metadata layer has a separate interface for the metadata, and that isn’t always a graphic interface.

Is the design area banded or page based
When I first started creating reports around 1990, all the tools I knew had a design area made up of recurring horizontal bands. Most had the exact same seven bands but gave them different names. You placed fields, text and other objects into specific bands depending on how often you wanted that object to appear.   In more recent times tools use a single design area (or page) which can contain other objects. I call these ‘page based’ tools.  In these tools you typically need a table object to create recurring rows.

Table  objects can be made to behave something like bands.  But, table rows typically don’t have all the controls available in a banded reporting tool.  Also, table objects do not typically allow free form placement within the rows of a table.

A banded tool can usually be made to behave like a page based tool.  In Crystal, for instance, you can suppress all of the bands except for the Report Footer.  Since this only appears once it will behave just like a page based design area.  You can then place objects into it like charts, cross-tabs and subreports. You can even replicate the functionality of a table object by creating a subreport and placing it in the Report Footer.

There are five tools in the current comparison that are banded and three that are page based.  All of those that are page based support table objects.

Are the template file settings stored in an “Open” format.
Open formats are a more recent innovation, driven by users wanting to be able to transfer there content from one tool to another.  Historically, reporting tools like software in general, wanted to store document content in a proprietary file format to “encourage product loyalty”.  But some newer tools store document content in an open format such as XML.   Three of the tools in this first round use an open format.

So, below are the current tools in the matrix and a capsule of information about each one.

Crystal Reports ($499)
Crystal Reports allows you to directly query virtually any database and format the output in a very precise layout. It uses a banded design area which makes it good for formatted output (forms, financial statements, etc).
It is designed primarily for end users but has a sophisticated expression language that can be used to do sophisticated calculations verging on programming.

It has also developed an ‘ecosystem’ of 50+ third party add-ons that provide independent viewing, scheduling, exporting, email, etc.

MS Access ($110)
Is primarily a database program, but it also provides the ability to create reports that directly query virtually any database.  It uses a banded design area which makes it good for formatted output (forms, financial statements, etc).
It is designed primarily for end users but has both a sophisticated expression language and and application programming language.  The expression language is not quite as advanced as Crystal Reports, but the application programming language allows developers to do things that Crystal can not do.

R&R Report Works ($600)
This is the tool that I worked with 20 years ago before I switched to Crystal.  It is still being used by a small but loyal customer base.

ReportWorks allows you to directly query virtually any database and format the output in a very precise layout. It uses a banded design area which makes it good for formatted output (forms, financial statements, etc).
It is designed primarily for end users but has a sophisticated expression language that can be used to do sophisticated calculations.

SSRS (Free with MS SQL Server)
or Report Builder (free download)
SSRS Report builder is a free standing version of the report design tool incorporated into SQL Server.  Report Builder  allows you to directly query virtually any database although it was intended primarily for SQL Server data.  Report Builder uses a page based layout area and uses table objects for recurring sections.  This makes it suitable for less complex layouts.
Report builder is designed for application developers who want to incorporate reports into their SQL Server applications.

List and Label ($1200)
List and Label is a reporting tool that is sold to application developers. It allows these developers to provide standard and ad hoc reporting capabilities to users in a controlled environment. The report designer does not connect directly to the data.  Instead the app developer must first create meta data layer of fields that are available to the report developer.  The report developer, therefore, has no control over the tables, fields or joins used to generate his datasets.

LL uses a page based layout area and uses table objects for recurring sections.  Normally this would make it difficult to replicate more complex layouts, but by using variables LL can overcome these limitations, allowing the area of the page  surrounding the table object to correlate with the sections in the table.

Other than creating the metadata layer, the report designer is geared toward end users being able to create their own reports. And I found that it had features comparable to Crystal Reports.  It also added some features not found in Crystal like the ability to automatically generate an index or table of contents.

Also note that the price mentioned above is for the App developer, who can distribute a free report designer to all of his users.  This allows users to create their own reports from the metadata layer created by the App Developer.

QlikView ($1,350)
QlikView is designed for interactive analysis and visualization. It is not designed to create reports.  QV designers do not tap directly into raw data but must have datasets created for them by someone who knows how to write QV scripts.  These scripts are similar to SQL queries and load specified tables into memory to be used by QV.

QV uses a page based layout area and uses table objects for recurring sections.  The layout objects are geared mainly to charts and cross-tabs.  For those who want to use QV data to then generate more form based reports, there is an application extension called NPrinting which adds some page management capabilities.

Once the scripts are written to generate the datasets, the designer is geared toward end users being able to design their own output and to manipulate the data that is in memory. Because the data is in memory the manipulations happen very quickly.

Jaspersoft Studio (Free open source)
Jaspersoft Studio is a design environment for Jasper Reports, an open source reporting tool that is built in Java.  It can be run on Windows, Linux and FreeBSD and is aimed web application programmers that want to include reporting in their applications. So Jasper Reports do not work with ODBC or OLEDB data but can work with JDBC data connections, XML, XLS and CSV data.

Jasper Reports does not require a metadata layer but one is available if you want to configure it.  It is unusual in that it uses a banded design area but still allows you to add a table object, in addition to charts and cross-tabs. The report definitions are stored in XML format but the files have the extension .jasper

You can download and use Jaspersoft Studio for free.  Those that want vendor support, or want access to some of the more advanced features, can purchase professional or enterprise level software from Jaspersoft.

Impromptu (price currently unknown)
Impromptu is (was?) the reporting feature of Cognos from IBM. You purchase licenses of Impromptu if you already have a Cognos deployment.  From my reading it appears that the latest version of Impromptu (7.5) may be the last as it is replaced by Cognos 8 Report Studio. But the IBM support schedule shows it will be supported until 2018.

Impromptu report designers do not tap directly into raw data but must have catalogs (metadata layers) created for them by someone else.  The catalogs define what fields available, where they come from, and how each relates (joins) to the rest of the catalog.

Once the catalogs are created, the report design environment is aimed at end users who can design their own reports.  They use a banded design area and save their designs in a proprietary format file with the extension of .imr







Comments are closed.

Jeff-Net
Recrystallize Pro

remiCrystal reporting solution