The Crystal Reports® Underground

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.

Timing is everything. In October of 2004 I added a new formula to my website. Formula #22 showed how to round a dollar amount to the nearest nickel, but could also be used to round any number to whatever increment was needed. I have used that formula many times since – including my blog post from 2 weeks ago about rounding a DateTime to the nearest 15 minutes.

Today I heard from Ido Millet of Millet Software who suggested some improvements to that blog formula. His formula used the Crystal Reports MRound() function. I had never used the MRound function so I looked it up to see what it did. It does exactly the same thing as Formula #22.

After a bit of research I found that I mentioned the MRound() function in my January 2005 Newsletter. It was one of a dozen new functions in Crystal Reports XI.  But I somehow missed the fact that it did the same thing as Formula #22, which I had posted 3 months earlier.  Today I updated Formula #22 to use the MRound() function, but I have left the original formula for users who are still using CR versions before XI.

In the past few weeks I have been contacted by two different users with xBase data (dBASE, FoxPro, etc). Both were trying to read the data using CR 2020 and not finding a way to connect. One had to upgrade to 64-bit because some of the xBase.dbf files had gone past 2GB.  As I now know, a 32-bit process can only use a maximum of 2 GB of memory. I didn’t know this was a general limit, but I did know that when one of my MS Access databases gets to 2GB I have to purge and pack it before my reports will run.  The other user upgraded without knowing that his connectivity would be affected. They might be able to revert back to CR 2016.

I have still not upgraded to CR 2020 because I use DAO connections and have little motivation to change them. However, if I get a slow week, I plan to setup a test environment to experiment with CR 2020. Then I can see if there are any workarounds for the xBase issues.

If I can’t find a driver that can connect directly to xBase there is one other thing I may try. I have read that MS Access 2016 can connect to dBASE files. It may be possible that MS Access 2016 can be used as a bridge between Crystal and dBASE files.  If so that might work for other xBase flavors like FoxPro.  I will post what I find.

If anyone else has any ideas, I would love to hear from you.

This morning a customer asked me to round a DateTime value to the nearest quarter hour. I had written this logic for them before but we couldn’t find the formula so I started from scratch. When I was done it didn’t look familiar so I suspected I had used a different approach this time.

Later we found the original formula, and it was longer and more complex. That is when I remembered writing it, and saying at the time that there had to be a more elegant approach. I must have been off that day because I couldn’t seem to find the simple solution and just wrote something that worked.  Today it clicked:

Local DateTimeVar DT := {Orders.Ship Date};
Local DateVar D := Date(DT);
Local numbervar Sec := DateDiff('s', D, DT);
DateAdd('s', Mround(Sec, 15*60), D);

And thanks to Ido Millet of Millet Software for suggesting the even better approach to this formula now shown above.

If you put a picture object on a report, that object will start out in full size. Usually I find this is much larger than I need and I have to reduce the size of the object. When you resize an image object using the sizing handles (sides and corners) you can easily change the ratio of the height to the width, which might distort the image. This ratio is called the “aspect ratio” and typically you want to keep the same ratio as in the original image.

One way to change the image size and keep the aspect ratio is to right click on the image, select “Format Graphic” and go to the “Picture” tab. There you can use the “Scaling” percentage to enlarge or reduce the height and width.  If you use the same number in both boxes the image will get bigger or smaller but will keep the aspect ratio of the original.

Today one of my colleagues showed me something he learned from one of his customers. Maybe some of you know this already but it was new to me.  If you select an image object and then hold down your “Shift” key you can change the size of the image and the “Shift” key will lock the aspect ratio.  Using the sizing handles you can make the image bigger or smaller, but the height and width will stay in proportion to each other.

One advantage of this over entering the numbers as described above, is that you can visually choose the size you want. When entering the numbers you have to exit to see the result.  This often means some trial and error to get the image to the size you want.  In testing this tonight I found that after using the “Shift” method the “Scaling” numbers in the “Picture” tab were sometimes slightly off – usually less than 1% different.  If you want them exactly the same you might still use the “Shift” method to get the size to look right visually, then go into the “Picture” tab and adjust the “Scaling” numbers so that they are exactly the same.

I had a customer this week who updated a report to read Oracle instead of SQL Server. The report was based on a command and the SQL was virtually identical. But for some reason the Oracle version would take a full 5 minutes to refresh while the SQL Server version only took a few seconds.

Once the report was run in Oracle the first time it could be refreshed using different parameters and it would only take a few seconds. I had the customer check for indexes but those had been set up to match SQL Server. I wondered if it was a data cache, but that didn’t explain why setting new parameters would be fast.

The only other thing that I could think of that was unique to the first refresh was a setting in Crystal that does a “Verify Database” on the first refresh. This usually takes a few seconds and isn’t noticeable, but as a test I turned that feature off. The first refresh was now as fast as SQL Server. Apparently, something in their environment caused the “Verify” process to take 5 minutes to complete.

If you run into something similar, go into File > Report Options and look for the check mark that says “Verify on First Refresh”.  Unless your environment is changing frequently, this doesn’t need to be checked, although in most environments you won’t notice a difference.

If you aren’t familiar with the “Verify” feature you can invoke it at any time by using the option in the database menu. This forces Crystal to poll the database for a complete list of fields for every table used by the report, including the data types.  Crystal stores this info in the report, which gives you a full field list even when you aren’t connected to the database.  Whenever fields are added, removed or their data type is changed this list needs to be updated . That is what it means to “Verify Database”.