The Crystal Reports® Underground

This week I had to do something new in SQL.  I needed to take a list of multiple route records for each driver and append the routes into single string for that driver.  This is relatively simple in a Crystal report, but not so simple in a SQL Query.  After I realized how complex this would be in a normal SQL query I searched to see if there was a simpler way.  What I found was that several databases have a function specifically designed for this task.

In SQL Server and Postgre SQL it is called String_Agg().
In Oracle and DB2 it is called ListAgg().
In MySQL it is called Group_Concat().

Even though SQL is considered a “standard” language, some function names and syntax will vary from one database to another. That is why I created a “cheat sheet” a few years ago that lists the most common SQL functions (with their correct syntax) for the 8 SQL flavors I encounter the most:

• • • SQL Server
    • MySQL
    • Oracle
    • MS Access
    • Pervasive
    • Progress
    • PostgeSQL
    • DB2

Today I added these “aggregate” functions to the cheat sheet along with a few other updates.  The cheat sheet is now also available on the library page of my web site.

My customers often use large, high-resolution monitors.  Sometimes the icons and menu options are microscopic – even to the local user. It is even worse for me since I am seeing their screen through GoToMeeting which reduces the screen even further.

But recently, one of my colleagues pointed me to a video that might help. It shows how to change a compatibility setting specific to high-resolution monitors.

If you are seeing microscopic icons on your Crystal toolbar, check out the video and see if it helps. And thanks to Gordon Portanier with the Crystalize consulting group in Canada for sharing this tip.  And also to Mike Pieczynski of OnPoint Consulting for sharing the video.

If you want to change the properties of a section, you go into the Section Expert(“Report” menu). You can also get to the “Section Expert” by right-clicking on a section name and selecting the option “Section Expert” from the “Flyout” menu. This menu also gives you a few properties you can change for a section without going into the Section Expert, like hiding, suppressing or deleting a section.

But I’ve noticed something strange in recent versions of CR. This flyout menu doesn’t always respond to user choices. I have noticed it both in my own environment and also when working remotely with several customers. It can be annoying to repeatedly right-click on a section name and have your choice ignored. One customer just stopped using the flyout menu altogether and just started navigating the main menu to open the Section Expert.

Now I think I have figured out a pattern for this behavior. If I right-click on a section quickly, without moving the mouse at all, the flyout menu will appear but it won’t respond to my choice. If, however, I right-click on a section and hold the cursor while dragging down a bit before I (left) click on my choice,  then the menu will respond to my choice.

I am guessing this is a bug, since I have not seen this problem with any other flyout menu in Crystal.

Crystal Reports is a great tool, but it isn’t perfect. One of my long standing complaints is how hard it can be to grab the sizing handles on an object using the mouse. The active zone around the sizing handle is tiny, so you have to be very precise or you end up dragging the object around instead of sizing it. You would think that zooming in would make the active zone bigger, but it doesn’t.

So I was very happy when I discovered, years ago, that you could size objects with the keyboard. If you hold down the shift key and hit the left/right arrow keys you can make the object’s right side move in and out. If you hold down the shift key and hit the up/down arrow keys you can make the objects bottom edge move up and down. If instead you hold the CTRL key while you hit the arrow keys, the entire object moves in one of the 4 directions but the size doesn’t change.

But soon after I started using Shift-Arrow I found that it didn’t work half the time. It was just this past week when I figured out the two situations where you can’t use Shift -Arrow to size objects:

1) When you are in Preview mode. For some reason using Shift-Arrow only works in Design mode. This is odd because using CTRL-Arrow to move objects can be done in both design and preview. That may be a bug but I found it true in all the versions I tested.

2) When the edge of the object snaps to a guideline. Once an object is ‘snapped’ to a vertical or horizontal guideline, the snapped edge can’t be moved using Shift-Arrow.  But following the pattern mentioned above, guidelines snapped to the top or left side do not prevent using CTRL-Arrow to move objects.

I was working with a customer’s report on my PC and trying to change a parameter. Instead of showing me a list of values I was presented with an unusual dialogue, looking for credentials. This dialogue didn’t look familiar and it took me a few minutes to figure out why it was there. The parameter I was using was a dynamic parameter and it was trying to pull a list of values from the database. That database didn’t exist on my PC, of course, so this is the window I saw:

As you can see it doesn’t allow you to change the name of the server or the DB. It does allow you to log in but that won’t help because I suspect you will only see this when the DB doesn’t exist. To get by this you have cancel and then change the dynamic parameter to pull form a local source.

So if you see a strange login that has “Enter Values” at the top and “Use Single Signon Key” at the bottom, check to see if your dynamic parameters can find their data.

A long time ago I wrote about a bug in Crystal Reports that affects two special fields:

• Modification Date
• Modification Time.

These fields show you when that report was last modified. If you open and refresh a report without changing anything the modification date and time should not change. This works most of the time, but it doesn’t work if you have a subreport.

This came up again this past week and after some discussion I thought of another article that I wrote in January about the options setting ‘Verify on first refresh”.  I ran a quick test and found that turning this setting off solved the problem. Apparently, doing that “Verify Database” counts as a report modification – but ONLY if there is a subreport in the report.  The behavior is different if there is no subreport.  This inconsistency is why I categorize this as a bug.

So if you are using the modification date (or modification time) in a report that has one or more subreports, you might want to turn this setting off.  It is found in File > Report Options.

And, if anyone finds an issue with turning off this setting, please let me know. I can’t think of one.

There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a web based option is necessary. The “official” options from SAP are Crystal (Reports) Server and BO Enterprise. But there are other, less expensive products out there that also provide web delivery of Crystal Reports. These third party products allow your users to run and view reports from a browser. You can also centrally manage your report deployment from a browser.

I have created a page on my blog that lists and compares these products, and I update it every Winter with info from the vendors. This year the list features 8 products:

Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
IntelliFront BI – a traditional Web portal
Ripplestone – a traditional Web portal
rePORTAL CR – a traditional Web portal
ReCrystallize Server – a traditional Web portal
ReCrystallize Pro – a launch page generator for the web
Bezlio – a SaaS Web viewer

The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (xls) that shows some of the specifics for comparison, including prices.

If you have any feedback to share on these tools I would be happy to hear from you.

One of my customers wanted to highlight all of the empty values in a string column, but still leave the highlighted space empty. I tried using a background color formula but that didn’t work. Then I remembered an article I wrote about display strings  and how you can’t force a display string to show anything when a field was empty. It seems the same thing is true about background colors.

I tried using IsNull, converting nulls to default values and even the highlight expert. The only thing that worked was to replace the field with a formula like this:

If {Table.Field} = ''
then ' ' //single space
else {Table.Field}

The second line isn’t an empty string but is a single space. Because a space is a character, Crystal will format it with a background color.