Manual Running Concatenation

In a previous entry, while discussing why I wanted to thank my most difficult customers, I mentioned a reporting technique called 'Manual Running Concatenation'. There seems to be enough interest in it to warrant the following discussion. It should be noted that my primary reporting tool is Crystal Reports (http://www.businessobjects.com), and the techniques described below were developed within that tool.


Crystal Reports has an interface that allows for the quick creation of running totals that is quite flexible in designating when the running total is incremented and when it is reset to zero . . . [more]

The Trick To Creating Multiple Records

It happens in practically every reporting class I teach: a student has a report that was working fine until s/he added a new table to the underlying query, and all of a sudden there are multiple copies of each record in the report! Imagine that, the report is broken!


I remember back when this phenomenon used to stump me. When I understood relational databases in greater depth, the mystery disappeared. If I have a data set consisting of customer records and then link in a table of addresses, it should not surprise me that there would be multiple records in the data set for each customer that ha . . . [more]

Boo Hoo or Woo Hoo?

"Reasonable people adapt themselves to the world. Unreasonable people attempt to adapt the world to themselves. All progress, therefore, depends on unreasonable people." - George Bernard Shaw


It's one of the few constants in today's changing world. Wherever two or more software developers gather together, the war stories commence, with each participant vying for position, each relating a more outrageous story than the last about the trials of working with demanding clients. Each persecuted coder takes perverse pleasure in relaying the details as if it's a badge of honor to be so downtrodden. < . . . [more]

The disappearing LEFT OUTER JOIN

"What happened to my LEFT OUTER JOIN? My filter criteria destroyed the LEFT OUTERness of my SQL join."


NOTE: the application of the techniques described herein requires the use of ANSI/92 or newer standards with regards to the SQL language.


In the early part of my career in application development, I would often create a SQL statement with a LEFT OUTER join to pull information from a couple of tables in the database. I would then apply a selection criteria in the WHERE clause, and all of a sudden the data being returned would behave as if I had used an INNER join even thoug . . . [more]