Skip to Content
Author's profile photo Andrew Baines

Smelly Crystal Reports

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.

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      This seems to lead into another blog.  Can I volunteer you for it?  What do you do about the "smelly" Crystal reports?

      Nice first blog!

      Michelle

      Author's profile photo Mark Richardson
      Mark Richardson
      I would say many of these IT / DBA "control" SMELLs come from reporting directly against the transactional system database.

      If you want the freedom to query the PROD data "at will" without going through the View / Stored Procedure route with IT / DBA's then someone need to find the money to MIRROR the PROD data onto a stand-alone reporting database. (...but that costs $$$ than nobody wants to spend because they are "Just Reports").

      Formatting and Commenting problems come from not have any enforced "Reporting Standards" within an organization - and no "Gate-Keeper" process to release new reports into PROD.

      Reports are just like any other piece of CODE - and should be managed with whatever source-control and RFC processes are required by the organization.

      I did a presentation at the "2010 Business Objects Conference" that covered a lot of the Best Practices around these issues. You should be able to download the PDF of my slides from the ASUG site.

      Author's profile photo Andrew Baines
      Andrew Baines
      Blog Post Author
      Mark, yes, you're right. A lot of this is due to the mismatch between Crystal starting as an end-user tool; as its use becomes more important and grows within an organisation, you start to hit barriers due to needing the controls you discuss.
      However, the more controls you put in place, the less flexible the reporting solution becomes and the more of a barrier IT becomes. Once IT start to be perceived as part of the problem, the users start to find workarounds - standalone copies of Crystal Reports, or data dumps to Excel etc. That gets even smellier! Data Silo City.
      While a lot of large organisations are able to have these controls and processes, I do a lot of work with smaller companies, and they just don't have the people or technical resource.
      I'd prefer to avoid these problems by guidelines and training rather than controls. Then, where necessary, for company wide, mission critical reports have the kind of work-flow you advocate.
      That said, a lot of this depends upon the size and style of organisation.

      Thanks for your input.

      Author's profile photo Mark Richardson
      Mark Richardson
      I agree, everyone wants "Flexibility" - but a lot of current legislation (HIPPA, Privacy, SOX, etc) exposes a company to a lot of RISK if you let everyone query PROD data without serious controls.

      The IT group may be perceived as a "Barrier" by end-users, but they are often just the folks in-the-middle between end-users and the legal/compliance guys.

      One of the companies I work with (5,000+ employees coast-to-coast) has a "Managed Desktop" environment which limits the ability to install your own software.

      We did a network-scan for all the installations of the CRW32.EXE file (Workstation, CR Version, Last User Login) - and had a workflow to justify your local-install of CR.

      If you didn't meet the threshold for having a local install, you are limited to accessing "packaged" Crystal Reports from InfoView...and certain applications have limited use of WebIntelligence for Ad-Hoc.

      There are also limits on the underlying Oracle databases.

      Author's profile photo Andrew Baines
      Andrew Baines
      Blog Post Author
      I think we're busily arguing about agreeing that it depends upon the situation!
      A number of my larger customers have the report writers sitting outside of IT in a separate business unit. This is especially true where the end users are pure report consumers - where the on-screen report is just consumed like a print out, with little interaction. IT isn't seen as a barrier to end users, it can be a barrier to the report writers though.
      Can I be allowed to add a 6th smell? Organisation smell where the business controls don't fit the reporting or legal requirements?
      Author's profile photo Mark Richardson
      Mark Richardson
      100% agreement on the "Sixth Smell" (*worst Bruce Willis sequel, ever...)

      Organizations smell where the business controls don't fit the reporting or legal requirements. Both of which need to be reviewed and refreshed on a regular basis.

      What you don't know CAN hurt you. I think it a lot of cases the new legislation can come as a "surprise" to small to mid companies who don't even think of their reporting impacts until they get caught in violation after a complaint.

      Here endeth my rant..

      Author's profile photo Former Member
      Former Member
      Hi Mark
      I'm interested in taking a look at your presentation but after 25 mins of google searching I still haven't been able to locate it. On the upside though I've found some other iteresting discussion.
      Would mind posting a link to your presentation please?
      KR
      Amanda
      Author's profile photo Mark Richardson
      Mark Richardson
      I think you need to be an ASUG member to download the Conference Slides.

      Contact me via the LINK below - and I will send you a PDF.

      http://www.roserichardsonenterprises.com/contact.html

      Author's profile photo Former Member
      Former Member
      Great 1st blog, Andrew.  Generally, I agree with all of your points, with the exception being #4 - Complex SQL. Simply using complex SQL doesn't make a report "smelly".

      Personally, I think having a good knowledge of and ability to write SQL, as well as how it works in conjunction with Crystal Reports, is a step that takes a report writer's expertise to a completely new level by sheer virtue of what that report writer will be able to accomplish more efficiently than he or she may have been able to do before. Using SQL--whether in Views, Commands, Stored Procedures or SQL Expression fields--can make a report much more efficient, eliminate unnecessary sub reports, allow unions and complex joins, etc..

      Being a Crystal Reports expert isn't just about knowing only Crystal Reports. Additional skills are required.  Should every report be based on SQL?  No. But a Crystal Reports expert should use the right tool for the job.

      That being said, I do agree that somebody with no SQL knowledge could absolutely have a hard time interpreting, modifying and/or maintaining a report based on complex SQL.

      Author's profile photo Andrew Baines
      Andrew Baines
      Blog Post Author
      We're in agreement - my concern is to do with the degree in which it's used.
      If a customer has 50 reports and only a couple use SQL commands, I'm not worried. If most have complex SQL, I'd say there's a bad smell.
      If the only way the customer can achieve the required results for basic reports is by fighting Crystal with SQL, then they need to look at the underlying data or revisit the reports. It could be time for a data-mart/warehouse, or just a chat with the report developer (do they see writing SQL as the primary way to create reports?), or Crystal isn't the right product for the job (never thought I'd say that!).
      There are always going to be times when you have to fight a product to produce the results you need (that's the fun bit), but it should be the exception, not the norm.
      The developers' maxim of "it's easy to create complex code, it's the simple stuff that's difficult" is very relevant here.