News & Views

After the longest beta programme since time began, the new release of Crystal Reports Server (now just Crystal Server) was finally released to shrink-wrap last week. I've checked my diary, and this is almost 6 months since the 'release' date! Off the record, it's nearly a year since I was told to expect the release. Let's hope the time was well spent! There are plenty of places to see list of new features, so I'm not going to repeat that here. I'm going to give my first impressions from the perspective of an independent consultant who's been working with Crystal for most of his adult life.Yes, it's supposed to be slightly irreverent.

I'll follow up this blog with actual usability stuff once I've had feedback from my customers about their experiences, and I've had some time to do 'real work' rather than just playing.

First up, the install process:

Service Pack 2?! Isn't this fresh off the press? OK, so select customers have been running BI 4.0 since early this year, so maybe it's a sign that this is mature software. But two service packs in 6 months? I'm glad I wasn't running the original release.

On the other hand, imagine if Microsoft release Windows 8 this time next year after a 6 month testing phase with select customers. The first shrink wrap installs as Windows 8 SP2. Imaging the howls of derision!

Let's give SAP the benefit of the doubt for now.

It's also version 14 - so superstitious to skip 13, or maybe the delay is due to my sleeping through version 13?

Some good news:

I've always liked the way that Microsoft have checks for pre-reqs when you install their software. Saves so much pain and reconfiguration later. Nice to see this here, I lose count of the number of times I send a list of pre-reqs to a customer before an installation to find they've not been done - this should save a lot of time.

More good news - .Net SDK

Tucked away in the very flexible installation options is the .Net SDK. I do a lot of .Net development, so after the missing SDK in the V0 release of Crystal Reports Server 2008, and the knowledge that there was no .Net version of the new InfoView, I was concerned. There is a 32 bit version too, as a separate install.

Even more good news - SQL Server as default system database

I assume this is due to the tiff between SAP and Oracle. Oracle own MySQL, so SAP have dropped MySQL from the installer. Before anyone launches into 'Open source is the answer', ask yourself one question - how many small companies who did their own installation of Crystal Reports Server 2008 have the slightest idea how to backup a MySQL database? Even if they already have MS SQL, chances are they don't know how to create a new system database, so they just keep clicking next and end up with a system they never backup.

Thank you SAP (and I don't say that very often).

Other bits and bobs:

There a various options - version control and diagnostics. I've not had time to even check the licensing agreements, so won't get excited just yet. Version control has been a frustrating omission for too long, so glad to see something appear. Now all I need is proper control of publishing - reports available from, to etc.

Central Management Console

Nothing but good news here so far - it hasn't really changed. After the massive changes of the last release, it's good to see that tasks are done in pretty much the same way. There is new stuff, but the old stuff can be done in the same way. I've not worked through any proper workflow yet, so I don't know if I'll still be playing 'hunt the option' between the 'Action' and 'Manage' menus, or wondering whether or not rightclick will give me a context sensitive menu or the browser options. Hopefully, that's all been fixed.

Infoview BI Launch Pad

Login screen remains the same, but that's about it!

After that, first impressions aren't great, but can be overridden by the administrator. First time you login, you get 4 mostly empty boxes on the home page - where are my reports? So you click the tab that says Documents, and get this:

Nothing in favourites, nothing in inbox, etc, etc. We're back in the land of Crystal Enterprise 8.0, playing hunt the link. Keep looking, and you'll see Folders at the bottom, click that and you're back in the familiar territory of your report folders. I know that I can change all these defaults centrally, but for the default to hide all your reports is just silly.

Overall

On balance, I feel positive about the new release, hopefully that feeling will continue. When I get time, I'll write some more.

I'd originally intended this to be an April Fools entry, but it's been a busy month. With a little bit of judicious editing, I'll take advantage of Easter and make it a post about resurrection. We can still have a bit of fool though!

Along with the rest of the SAP/BO/Crystal world, I've been interested to view the progress of BI 4.0, especially the Crystal side of things as that is my main focus. It was while watching the demo of Crystal Reports for Enterprise that I had a strange feeling of deja vu.

Summing up the important features of Crystal Reports for Enterprise:

• It's written in Java
• It works with rpt files
• It's not quite as functional as Crystal Reports
• It'll be 'problematic' to deploy with all those annoying java updates

Those with memories going back about 12 years will remember Seagate Analysis as part of Crystal/Seagate Info, and its associated product, the freebie Crystal Query (I may have got the name wrong, but it was going to be free forever).

The main features of Seagate Analysis were:

• It was written in Java
• It worked with rpt files
• It wasn't quite as functional as Crystal Reports
• It was difficult to deploy as back then a 40MB+ download (IIRC) was big, and you still had those annoying java updates
• It had a really clever tab that let you reuse your report data in an OLAP-like slicer dicer

Well, 4 out of 5 ain't bad!

I've no idea if any of the old Seagate Analysis code has been reused, I doubt it, and kind of hope not!

However, it does look like an old product has been resurrected.

While typing this, I've been wondering two things:

1) Why did Seagate Analysis fail (if fail is the correct word)

2) What else should be resurrected?

On its failure; Seagate Analysis was only in the product for one release, being dropped with the release of the 'new' product, Crystal Enterprise. Seemed a strange choice at the time as so much energy had been spent on marketing it and, ignoring the size of download and installation issues, it fitted quite nicely into a web application. A few years later, the dreadful Crystal Reports Explorer appeared, but it was never used much (for very good reasons).

My personal, biased opinion is that part of the failure was due to being written in Java. I've always felt you can tell a Java application from the fact that it isn't quite as good as the Windows (or Mac) version, bit clunkier, bit slower, less functional.

If my possibly ill-informed, biased opinion (!) is right, could a similar fate await Crystal Reports for Enterprise?

On resurrecting other software, SAP & BO have had their acquisition sprees over the past few years. I'm sure there's plenty of stuff that's been quietly ignored. Before you say anything, you can't resurrect Desktop Intelligence yet - the body's still warm!

I've seen comments out and about regarding the lack of new features in the next release of Crystal Reports due this year (see this pdf) . As far as I'm concerned, SAP have deprecated the awkward ActiveX viewer, so I'm willing to forgive them anything!

More seriously, this is mature software, so changes are going to be gradual, the last truly revolutionary release of Crystal was version 5 when all the conditional formatting, multi-section & sub-reports appeared. I know there have been subsequent changes to the database connectivity that were significant from an engineering perspective, but not important to end users. I need to qualify this paragraph by admitting I like Office 2010, especially the ribbon, so maybe my opinions are odd (I'm not too impressed with Firefox 4 either).

Moving on, this post isn't about Crystal 2011 (sorry, SAP Crystal Reports 2011, the marketing police will be calling), it's more retrospective. I'm about to do some work using Crystal Reports 8.5 (sorry, Seagate Crystal Reports 8.5). This got me thinking about all the new stuff that's appeared over the years, some new features have seemed trivial but have become must-haves, whereas other features have seemed major, but have never been used. One last mention of Crystal 2011 - no matter how few new features there are, it will be more than in Crystal 8.5 - erm - pdf export and support for Crystal Enterprise, that'll be ££££ please.

I can't remember when all these things appeared, so I'll not bother. If you don't remember any of these, then you've probably a lot less grey hair than me! I've excluded some of the exciting stuff (eg Dynamic Parameters) simply because they did seem important, and have remained so.

Things I'd be upset to lose, but didn't seem important at the time:

1. Resizing the Database Links Window (aka Visual Links) - sounds like a daft one to start on, but it used to be a fixed (tiny) size and having to scroll around all the database tables used to drive me nuts! Such a trivial change that made life so much easier.
2. Duplicating Formulae - it's common in Crystal to have many very similar formulae running across the page. All that cutting and pasting from one formula to another was made so much simpler when you could just duplicate and rename.
3. Formula Workshop - I really didn't see the point of this when it first came out, but it makes finding formulae so much simpler. Add to that, being able to find a formula's dependencies is priceless.
4. Sub-report linking - not the ability to, but the user interface. Remember that tiny box with drop-downs that never seemed to link quite as you'd expect? Then, you'd try to remove a link and - oops. The current version is far from perfect (not visual enough), but at least it works.
5. Saved data selection formula - really geeky choice, this is the less glamorous part of being able to edit the parameters in the viewer. Great idea, well executed. I've been working on some reports based on an XML data source where the initial report takes a while to load the data, I wouldn't want to repeat the delay every time the user changed a parameter.
6. Longer formula text - once upon a time there was a fairly small limit on how long a formula could be (can't remember how long). First report I wrote for a major customer (Computacenter) blew that away so I had to break the formula down into many smaller parts. I then had to cut and paste as the formula was replicated 31 times.
7. Format Painter - Just looking through the old new features documents and can't believe this didn't arrive until version 10. I know it still doesn't quite work as it should on some formatting, but it's a great help.
8. Charts - there have been many changes to the charts interface over the years. There used to be the separate chart application where you could do advanced edits, then you'd add the chart and it would all change ever so slightly! The current interface is nearly right with the exception that some of the changes can only be made in design, not preview, and that resizing the chart messes things up - maybe next time?
9. Report Explorer - another new feature that I didn't use for a while. Then I spent 20 minutes trying to select lines in a crowded section! Just click in the Report Explorer and you have it.

Things that seemed important, but ....

1. Maps - I loved to demo the maps. Here's my report, insert a map just like you do a chart and bingo - map of USA with sales by state colour coded. As you can probably tell from my spelling, I'm in the UK, not US. The UK map looked great, but there was no way to sensibly place your data onto the map as the regions made no sense. The only option was to buy another map, but the licensing was so horrendous that nobody did. Moral of story: if you want maps, buy a mapping application.
2. Workbench - The ability to group all your reports into a selection of projects seemed like such a good idea. I spent ages creating a project per customer and adding their reports. Then I fired up my laptop to find there was no simple way to keep the two synchronised. Nor did the workbench add much to windows explorer other than the ability to check some dependencies.
3. Running Totals - It used to take 3 formulae to create a running total - one to initialise, one to increment and one to display. New Running totals were supposed to make life so much simpler with a simple interface to define what you were adding up and when to calculate it. Then you'd try and do something complex and have to delete your running total and go back to the 3 formulae solution. For anything beyond trivial, I stick to 3 formulae now.
4. Basic Syntax - There are millions of VB programmers, and millions of Crystal Reports users - what could be more obvious than letting Crystal use Basic. Except it wasn't Basic, it was just different words to those used by Crystal, a bit like describing the Crystal Syntax as Pascal. That meant that neither VB programmers nor Crystal Reports users could use the new syntax. Let's get rid of it and let the people writing the documentation work on something else.
5. Templates - there used to a joke about the paper clip in Microsoft Word - "You appear to be writing a letter, would you like me to @%@^@ it up for you?". This was Crystal's answer to the paper clip - "You've spent hours placing those fields on your report, would you like me to randomly rearrange them for you". With the initial release, it wasn't even possible to undo the changes! Maybe it's improved since then, I don't want to take the risk - I'll stick to modifying an existing report that has the correct formatting.
6. Autosave Report - This seemed to be such a great idea - you have a program that does (occasionally) fall over so why not save your work as you progress? Except it saved the report over the existing file so if you wanted to just test a few changes, you lost your stable file - I know you should work on a copy, but welcome to the real world. I much prefer the method used by QlikView where the filename is automatically incremented with a version number.
7. Sort Control. I think many people will disagree with this one! When I used to demo Crystal, I was often asked about dynamic parameters - they were a feature that lots of people wanted. I was never asked about on-report sorting. They look like something that someone who had used WebI would ask for. Newsflash - WebI and Crystal have different uses. Add to that, the clunky formatting - they look really nasty.
8. Highlighting Expert - look! I can change the colour of this field based upon it's value. However, I also want to change the colour of a second field based upon the value of the first, so now I have to do the same thing in two different ways on the same report.
9. Xcelsius objects - I can see the thinking in development - our chart engine doesn't always work as we'd like, let's add one that's really difficult to use so that we can tell people they have a choice. I love Xcelsius (I know the name has changed), and think it makes a great dashboard tool, but it's too complicated when you add it to Crystal. Add to that the problems of testing all these disjointed bits and it gets worse. Maybe the integration interface will change in the next release, then I can move this up the page to features I don't want to lose.

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.

1. They don't really know how many reports they have. As this is something discussed at annual review time, would be a great help to know.
2. They know they have far too many report instances, but not sure which reports are a problem. As they have many folders and umpteen reports, browsing all the report folders isn't an option. Just adding global limits isn't an option - some of these reports need to be kept for many years.
3. When they change their main application's database, they're never sure which reports will be affected.

• Report: folder path, current instance count, subreports, author, comments, subject, title
• Database Connection: Database name, server name, userid, whether or not integrated security used
• Database Table:Table name, alias
• Database Column: Column name and name as used by Crystal. Sadly, this doesn't tell you whether or not a column is used, just that it's available - that functionality was mysteriously removed in Crystal 7.
• Table Links: The links between the tables, source table/column, destination table/column & type of join