Smelly Crystal Reports

[AndrewBaines]

This is my first post on the SDN blogs, so I'm afraid it's a shameless cross-post from my regular blog over at wordpress.

One of the few blogs I have bookmarked is Phil Factor's Phrenetic Phoughts, in a recent post he discussed SQL Code Smells. As he explains, code smells aren't a new idea, they're not even necessarily causes of errors, they're just things in code that don't feel quite right. The sort of thing that may be (or may not) be working correctly now, but you just get a feeling it will cause a problem in the future when making changes or something behind the scenes changes.

This got me thinking about smells in Crystal Report design. I do a lot of work with people's existing reports, so I see a lot of different styles - some good, some bad.

Being an end user tool, a lot of Crystal Report designers have no formal training. In addition, the iterative nature of report design means that the design of a reports isn't normally planned. Even if it is planned, changes are made that don't necessarily fit in with the original design. You then have the battle between the designer and the IT focussed DBA who doesn't trust the end user to access the database directly so creates views and stored procedures. The end user finds this restrictive so  workarounds are used that make the situation worse.

A few things that make me draw breath:

1. Linked Views IT have created a view, but something is missing. The report designer has a choice - wait a week for IT to change the view, or link to another view. They always choose the latter. All of a sudden, instead of a simple query on the customer view, they're querying the entire transaction history of every customer to retrieve some obscure fact. They then blame Crystal Reports for bad performance.  Who do you blame? IT need control, the designer has a deadline to meet. You can argue about company culture, or talk about the 'real solution' being a data warehouse.

2. Complex Conditional Formatting Everywhere you go in Crystal now, there is a little formula symbol that allows you to conditionally do something. Be that change the font size, hide a field or change the colour. It's great - most of the time. Where it starts to get smelly is when you have very complex formatting. Just how do you debug it? To make it worse, it's then copied and pasted into multiple places. Change management?  Best solution is to use a proper formula that returns a value that is then used in the conditional formatting. That way you can use it in multiple places, and place the formula on the page to see what's going wrong. If I get a report with this smell, I copy the formatting into formulae then use the formulae for the formatting. Other problem is that it's so fiddly to find where all the conditional stuff is. Which leads me on to....

3. Comments Every article on code smells has this in the list of problems. It may be obvious what a formula is, but will it be so obvious when it's been changed 2 or 3 times, then revisited 6 months later? It's not uncommon to see Crystal Reports that are over 5 years old. As well as commenting in formulae, I like to add suppressed text fields to the report canvas to explain different sections - especially where conditional suppression is used.

4. Complex SQL I mentioned earlier that many Crystal Reports designers have no formal development background. Complex SQL isn't their issue - this one comes from SQL developers who have been landed with Crystal Report design because 'they know SQL and Crystal uses SQL'. Instead of trusting Crystal to write the SQL, they hand code. However, they're really good at this, so like to use sub-queries instead of joins, lots of aliases (renaming table to a, b,c, etc ) you end up with SQL that's really hard to interpret. Clever SQL developer then moves to another project and hands the report to the aforementioned end user.

5. Stored Procedure for every report The SQL developer is the guilty party again! Each report has its own stored procedure that was designed when the report was first requested. This locks in the query and makes change hard work. It also forces a dependency on IT for every change ever made in the future. Users try to modify the report, which takes us back to the first problem.

I'd love to hear what others think. If you don't want ot post here, pop over to my web site.