All versions of Crystal Reports up to v14.2 (2016) need to use the 32-bit ODBC Administrator. But in Windows 7 and later you are taken to the 64-bit Administrator by default.  A couple of years ago I wrote about this.  Today after helping a customer I learned a few more strange things about the ODBC Administrator that can get confusing.

The user was creating DSNs but CR could only see the DSNs if he added it as a “user” DSN. If he added a new “system” DSN he could not see it. The odd part was that one existing system DSN DID show up in CR, but any new system DSN would not show up.

So the first thing I checked was that we were loading ODBCAD32.exe to add the new system DSNs. Then we recreated, reconfigured and retested the system DSNs. I also looked at the INI Files that ODBC creates and everything. So I was stumped. As I was telling them that I was giving up I checked the 64-bit ODBC shortcut I had added to my desktop. I was surprised to find Continue Reading »
Confusion over 32-bit and 64-bit ODBC

Here is a clever tip I just received from a blog reader who is also a Crystal Reports developer. He wanted to create a title with something that looks like the “text-shadow” property.  He found that using an image didn’t print as clearly as a true font so he came up with a clever way to create the shadow.  Here are the steps:

1) Put the title text into a large bold font.
2) Copy the title text object and paste a duplicate directly on top of the original.
3) Right-click on the copy and select “Move – To Back”.
4) Changed the font color of the copy to silver.
5) Nudge the copy slightly down and to the right.

There you have a clean shadowed font.  And thanks to Adam Butt of APB Reports in Trondheim, Norway.

Formula names can contain any characters you want, including numbers, symbols and spaces. But I just found a reason not to use a space as the first character of a formula name.

A customer recently tried to modify a formula in an old report, and was prompted to log into the repository. She sent me a screen shot of the messages, which said:

enter the name of your system (Central Management Server)

then:

Repository services are not available.

I figured that there was probably an old repository function in the formula.  So I asked to see the report and went through all the formulas.  There were no repository functions in any of the formulas.  And yet, when I right clicked on the first formula and tried to edit it – up popped the same message.  I was stumped until I noticed that the first formula name started with a space. So I switched to one of my own reports and renamed one formula so that it started with a space. When I right clicked on that formula and tried to edit it, voila!   I got the same messages. I ran the same test it in versions Continue Reading »
Don’t start your formula names with spaces.

In most cases I find it more efficient to let Crystal generate the SQL for reports.  This makes the reports simpler for most CR users to create.  It is also easier to add additional fields, and the Visual Linking window makes the query structure easier to understand. Finally, in most cases, the performance is essentially the same since CR can generate workable SQL.

But there are plenty of cases where the only practical solution is to base the report on a SQL Command, instead of on linked tables. When I need to do a UNION of two or more queries, or when I need to apply a filter to the ‘outer’ table of an outer join, I have to resort to SQL Commands.  I am not very fast at writing SQL so I usually create a regular report first, using the correct tables, joins and fields. Then I copy the SQL that CR generates and use that as the starting point for my command.  Recently I find myself using SQL more, so I have started to pick up tricks that make SQL queries work faster.

Case #1

Last week I was working on a query that combined several fairly large tables.   The query was taking several minutes to return data and we were trying to see Continue Reading »
Making SQL commands more efficient

Sometimes there are text blocks on your reports that contain more then one line of text (notes, memo fields, comments, etc.).  When you format any multi-line text field, you have a tab of options called  ‘paragraph’ formatting. The first 3 settings on this tab allow you to determine how far the text block should be indented. Your indent options are “Left”, “Right” and “First Line”. This can be misleading because “Left” and “Right” are calculated from the left and right edge of the object, while “First Line” is calculated from the indent set in the “Left” position.

Take this example: You want the first line of text to be indented 1.0 from left while all other lines in the block are indented .5 from left. You might try putting 1.0 in ”First Line” and .5 in Left indent.  What you would get is an indent of 1.5 on the first line.  This is because it adds the 1.0 in ”First Line” to the .5 in “Left”. If instead you put .5 in both places, then the first line will indent .5 + .5 or 1.0 inches.

And what if you want a ‘hanging indent’ where the first line is not indented but the rest of the paragraph is indented .5? For this you would put in .5 for the “Left” indent and -.5 (a negative number) for the “First Line”. The negative number added to the positive nets to zero so that the first line has no indentation, while the rest does.

I always mention case sensitivity in my Crystal Reports classes. My normal comment is that CR formula comparisons are case sensitive with the exception of the selection formula, which is usually NOT case sensitive.  I even wrote about this here, last month.  So I was surprised last week in my Advanced class when our formulas ignored all case differences. I thought this might be a recent change so I went home and tested reports back to v8.5. None of the comparisons were case sensitive.

So I did a quick Google search and found a 10 year old forum thread discussing case sensitivity. One short comment in that thread explained the difference. And guess who wrote that comment and promptly forgot about it (Doh!).

So the answer is that there is a setting in Continue Reading »
Case sensitivity in Crystal Reports – revisited.

Crystal limits how many values you have to choose from when you use a dynamic parameter.  The default limit is 1,000.  To show more than 1000 values you have to add a registry key called ‘LOV’ in the CR path for Database options.  This needs to be done on each PC that will be running the report.   The following examples are for versions 11.5 through 14.x.  Your registry path may be slightly different depending on your Operating System and your CR version.  See this SAP link for more info.

1) Add a new registry entry under the appropriate path on your PC to the DatabaseOptions key:

For v11.5 use this:
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.5\Crystal Reports\DatabaseOptions\

For 2008 use this:
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\

For 2011, 2013, 2016 on a 64-bit Operating System use this:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\DatabaseOptions

For 2011, 2013, 2016 on a 32-bit Operating System
OR for 2020 on a 64-bit Operating System use this:
HKEY_LOCAL_MACHINE\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\DatabaseOptions\

2) Add a new key at that level called: LOV

3) Add a string to that key called MaxRowsetRecords

4) Set the value to whatever limit you want.

I have read that you can enter a zero or -1 to mean unlimited, but that hasn’t worked in my tests.

Also note that loading large lists this way can make the parameter run for a few minutes before the user can select a value. Also note that any dynamic list that has more than 200 values will activate the parameter’s ‘batch interface” discussed in a previous post.

(And thanks to Duane Fenner, an Account Manager and CR developer at LeaseTeam, Inc. for suggesting this post.)

One of my customer sent me two screen shots. One showed a normal preview on his development machine. The other showed a preview window on the runtime machine. The second had the group tree, but the report was nothing but a long blank vertical strip. He had been struggling with it for a month and could not figure out what was causing the strange behavior at runtime.

When the preview behavior changes on a different machine, it is usually due to a printer driver problem. I also know that CR will normally Continue Reading »
Strange printer behavior at runtime

For the past few days I have been helping a customer develop a GANTT style chart in Crystal Reports.  The challenge was that Crystal’s normal Gantt chart doesn’t allow multiple bar segments on the same row.  So I was showing them how to create a GANTT chart using formulas and specially formatted summary fields. This method requires formatting dozens of small fields with the same color condition.

I selected an initial color but was trying to think of the most efficient way to change the color in all the condition formulas on the fly. Before I had an answer the customer showed me a method I had not seen before.  He created a single formula that returned Continue Reading »
Controlling the color of many objects from one formula

Say you have several columns of numbers that get added together like Price, Tax and Shipping. At the end of a customer group you would have three subtotals. You might decide to write a formula that adds those three subtotals together to get the combined total for the customer. It would work fine but there is usually a more efficient way to do this. Instead of writing a formula that combines the subtotals I would write a formula that combines the fields at the detail level. It would look like this: Continue Reading »
A total of a formula instead of a formula of totals.