Crystal Reports falls into that category of tool which promises to help end users accomplish technical tasks easily. They can point it at a database, ask the database a question, and voila, a report pops out, complete with pretty fonts and colors.

Like any such tool, however, there's a point where it starts getting technical. Jon's company passed that point ages ago, and hired on a dedicated Crystal Reports Developer to write reports that were too complicated for the end users. But even that has its limits, and eventually, their reporting needs outgrew what a Crystal Report implemented by their dedicated developer could do.

So Jon's team was handed a pile of reports and told, "implement these as stored procedures in SqlServer." Now, there wasn't anything so fancy as being given requirements or any sort of description about what the reports were for, or what they did, or what would qualify as a successful port version an error. The reports themselves were the requirements: reverse engineer them exactly as written, doing a 1-to-1 mapping wherever possible.

That was mostly fine, but there were a few "special" points in the original reports, where the developer had implemented their own custom Crystal Report functions. Including their own custom date functions. Like this one:

  shared DateTimeVar dateObj1;
  shared DateTimeVar dateObj2;

  If IsDate(ToText(dateObj1,{@DateFormat})) and IsDate (ToText(dateObj2, {@DateFormat}))
    then

Now, looking at this, you can see that dateObj1 and dateObj2 are typed as DateTimeVars, which yes, means they only hold date times. And thus, in the conditional, we see a pattern we've seen many, many times before: we convert the actual date object back into text and then validate that the text contains a valid date. Which it will, because, again, we started with a date in the first place.

But the goal was to do a 1-to-1 mapping, since no one really knew what the reports actually depended on or how they worked, and no one was going to free up the budget to actually answer those questions. "Thoughtlessly port the code, as accurately as possible," was the order of the day.

So, despite every desire in his body, Jon replaced those ToText calls with TO_CHAR and kept the logic exactly the same.

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!