Archive for the 'Bugs and Errors' Category



Using reserved characters in formula names

Monday 9 August 2021 @ 8:03 pm

I recently heard from Gordon Portanier (from the Crystalize consulting group in Canada). He was doing something I have seen several customer do over the years. He would start certain formula names with a symbol so those formulas would sort to the top of the field explorer list. Gordon happened to use the @ symbol.

When he upgraded to CR 2020 these reports ran fine, but he found that he could not create a new formula that had an @ symbol in the name. A message told him that four characters [ @ { } ? ] were now considered reserved characters and could no longer be used anywhere within the name of a formula field.

Existing formulas, however, are not affected unless you try to rename them. So Gordon found another report that had the formula names he needed and copied them to the current report. The formulas work fine.

Up through CRv12(CR 2008) a formula name could include ANY character in any position. But when I tested this in CRv14.2 (CR 2016) I got the same warning as Gordon. My guess is that this restriction started with CRv14 (CR 2011) and so it would also affect CRv14.1 (CR 2103) and all later versions.  If any of you are using CRv14 (CR 2011) or CRv14.1 (CR 2103) you can confirm this by trying to add a formula with an @ in the name.  Let me know what happens.

An interesting point. This rule only applies to formula names. It does not affect the names of SQL expressions, parameters or running totals.

Another interesting point is that the @ (which represents formulas) and the ? ( which represents parameters) are reserved. But you can still use the % symbol (which represents SQL Expressions) and also the # (which represents running totals).




Crystal Reports 2016 date formatting issue

Wednesday 21 July 2021 @ 2:29 pm

When you set up Windows you get to select a Regional Format for things like dates and times. For instance, English(US) format will show today’s date as 7/21/2021 while English (UK) will show today’s date as 21/7/2021. In Crystal, certain date objects can be assigned to use these formats, so that they automatically change from one format to another based on the regional format setting of the PC running the report. This is handy if you have users in different countries. To use this feature, right-click on the date field, choose “Format Field” and on the “Date” tab select the “System Default Long/Short Format”.

However, I just had a user complain to me that he couldn’t get CR 2016 date fields to respond to the regional setting. So, I did a quick test in my own environment and found that changing my regional Format from English (United States) to English (United Kingdom) had no effect on date format in CR 2016. I closed Crystal and reopened it, created a brand new simple report and put the print date in the Page Header – and it still showed in US Format. To make sure I was using the right setting I tried the same test in CRv10 and the print date came up in UK format. I repeated the test in CRv8.5 and it also came up in UK format – but not in CR 2016.

I did an online search and found an SAP thread where someone else had the same issue when they upgraded to CR 2016. SAP’s response focused on the database client (?), but the issue didn’t appear to have been resolved.

I wonder if Crystal is somehow pulling the regional format correctly when it is installed, but then isn’t updating the region if it is changed after the install. If anyone is seeing different behavior or has insight to share, please let me know.




Undocumented limit on the ExtractString() function

Sunday 4 July 2021 @ 4:21 pm

I have written before about using the ExtractString function. This function is found in the “Additional Functions” of Crystal because it comes from a UFL. But this UFL has come with Crystal Reports for a long time.

Today I found out that this function has an undocumented limit -it can return a maximum of 510 characters. If you need to allow for more than that you will have to use an alternative approach. Here is the formula that I used before I discovered the ExtractString() function. It will also work if you need more than 510 characters.

Local stringVar x := {Your.Field};
Local stringVar Beg := "BeginString";
Local stringVar End := "EndString";

Local NumberVar BegPos := Instr(x,Beg );
Local NumberVar EndPos := Instr(x,End );
Local Numbervar BegLen := Length(Beg);

if BegPos > 0 and EndPos > 0 and EndPos > BegPos
then trim(x [ BegPos+BegLen to EndPos-1 ])
else ''

In the first 3 lines you put in the name of the field, and then the two strings that mark the beginning and the end of the string you want to return. You shouldn’t need to modify the other lines.

The formula will then return everything between those two strings but will not include the strings themselves.




Arial 10 text objects get “squished”

Tuesday 27 April 2021 @ 12:35 pm

I just figured out how to solve an annoying Crystal bug that I have seen, on and off, for years. This bug has to do with text objects that are added using the default font of Arial 10. I don’t see this issue every day but it happens regularly with some customers and even on my own machine.

What happens is that a new text object gets horizontally squished. The characters are sized correctly, but the horizontal space from one character to the next is only about 10% of what it should be, so the characters overlap. You don’t see the problem while you are typing the text (Before), but as soon as you click outside the text object you see the squish (After).

Today I found a report that had this issue, and took the time to experiment.  Here are some factors that might contribute to the problem:

  • The report is opened on a PC that uses a different printer driver than the design PC.
  • The report is opened on a PC that has a different version of Crystal.
  • Your default font for text objects is set to Arial 10 (and possibly other fonts).

If you add a new text object to the report under these conditions, you might see the squish.  For a long time I would have to change the font, change the style (to bold) or change the font size to correct the problem. But now I know two quick ways to eliminate the squish.

1) Go into File > Page Setup, don’t change anything, and click OK.

or

2) Save the report, close it and reopen it.

Either of these should get the font calculations back in line with the new printer driver.

If anyone runs into this situation (or something similar) and the above methods don’t work, please let me know.




An error has occurred in the script of this page

Tuesday 16 March 2021 @ 9:22 pm

I have written several times in the past about script errors on the Crystal Reports “Start” page. The message is:

“An error has occurred in the script of this page”

I have seen at least 3 customers experience this problem in the past week so I figure it is time to mention this again. You will see this error when you first open Crystal Reports or when you close a report and Crystal Reports reverts to the “Start” page.  Some users think there is a problem with the report they are trying to open, but in truth this error is unrelated to any reports.

The error is usually caused by one of SAP’s servers having a problem.  If you want to prevent this error you can follow the directions in this blog post on how to prevent Crystal from requesting an internet connection.




Max rows/columns of a cross-tab

Sunday 7 March 2021 @ 5:05 pm

I found a limit that exists in Crystal that I didn’t know about before. Even if I had known about this limit I wouldn’t have ever expected to exceed it.  But it just popped up in one of my reports.

I created a report related to donors for a large organization. One part of the report needed to show the top 10 donors from a very large pool of people. I decided to use a cross-tab with a “Group Sort” so we wouldn’t need another subreport.  It tested just fine on the sample data set.

But, when they ran the report on the full table they received an error saying a cross-tab couldn’t have more than 65K rows or 65K columns. Apparently, the data set included over 100K donors.  To find the top 10 donors, the cross-tab would have to initially create a row for every donor. Even though I only needed to see the top 10, the cross-tab needed to see ALL of them, and that exceeded the limit.

Instead I created a subreport and grouped by donor. Then used the “Group Sort” on the actual groups, rather than a cross-tab. Fortunately this limit doesn’t apply to groups in the report, only to groups (rows or columns) in cross-tabs.




Missing the recent file list

Saturday 19 December 2020 @ 4:47 pm

When working with customers I often re-open recently used files.  Recently it seems that some of my customers versions of Crystal don’t show the recent files in the file menu.  This puzzles me because I am on CR 2016 and I have always seen the list of recent files at the bottom of the File menu.  There is a short list on the start page as well, but the longer list has always been in the menu.

Yesterday one of customers shared with me that she had been struggling with the same issue and then stumbled across the recent file list in a new place.  There is a yellow folder icon on the toolbar which represents “File > Open”.  Next to this folder is a small drop-down arrow.  Clicking that arrow shows the recently used files.  Neither she nor I had noticed this so I am betting that we are not the only ones.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for pointing this out.

On a related note, you can change the number of files that are shown in this list.  The default is 5, but you can increase this number in the registry.

The key is here:

Computer\HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\
Crystal Reports\Recent Files

The value to change within that key is called “FileCount”.

But there is also some strangeness about the filecount value. You can put in any number, but the registry only has 10 slots. So any number beyond 10 has no where to go. Then, the list in the file menu can only display the first 9 items of the 10 so I am not even sure why they have a 10th item. The other list, the one in the box on the Start Page, can only show the first 5 of the 10.

The other mystery I haven’t solved (yet) is why my install of CR 2016 still shows the recent files in the “File” menu,  while many Crystal installs do not.




When a reinstall won’t fix the formula editor

Thursday 22 October 2020 @ 8:04 pm

I took another dip into the registry today. I had to fix the formula editor toolbars for a customer (see below).

formula editor error

Two sections would not dock and the field tree on the left could not be made visible. We tried the field tree icon and right-clicks to allow docking. Nothing worked. I tried using the Toolbar Reset check mark in View > Toolbars. That didn’t work either. He even tried uninstalling and reinstalling Crystal, which also didn’t work.

But I remembered from my last post that the toolbars have lots of registry keys which store their settings and positions and I figured these were corrupted. And since re-installing didn’t solve things that meant the bad registry keys weren’t being replaced by the install. So we did another uninstall and then we went into the registry. Most of the Crystal registry entries had survived the uninstall. I was tempted to delete just the node for the formula editor, but we decided to play it safe and delete the entire node named “Crystal Reports”. It is found in this path:

Computer\HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\

We exported this node to a .reg file just in case we needed to restore it and then deleted it.  When we reinstalled Crystal Reports the formula editor went back to normal.

Today I did some more testing because I wasn’t sure if we needed to do the full uninstall.   I closed Crystal, then I went into the registry and exported the “Formula Editor” node. This is found under the “Crystal Reports” node mentioned above.  Once it was exported I deleted the “Formula Editor” node.  I then went into Crystal Reports and worked in the formula editor.  When I checked the registry the “Formula Editor” node had been recreated.  It wasn’t fully populated but it was there.  I went in and did a SQL expression and a custom function and this created more of the keys for that node.  In other words, it may not require a complete re-install to fix this part of the registry.  Crystal appears to create these keys, as needed, when they don’t exist. So if you have windows or toolbars that won’t behave, you can try this approach first.




Fields that change format when moved to a different environment

Monday 21 September 2020 @ 8:52 pm

I recently had complaint from a customer that a numeric field was changing format when moved from one PC to another. The format was fine when run on the developers PC but on the user’s PCs the format would change. I have seen this happen with both numeric and date fields.

The source of the problem is the format option at the top of the list called “System Default”.  If you leave a field formatted with this choice the report will use whatever format is set in the regional setting of the PC where the report is being run. Of course this means that a report might look different when run on another PC. If you don’t want the format to change from one PC to another then you should identify the specific format you want for numeric, currency, date and datetime fields.

Using a specific format should solve this 99% of the time. I have only seen a few instances where a report moved to another environment would have formatting changes despite the format being specified in the report. In those cases the final workaround is to write a formula to display the value. You can use Totext() to convert the field into text that is formatted the way you want. You use the text version for display purposes while using the original field for other functions like sorting and totaling.

Here are some examples:
Totext ({Date.Field}, 'yyyy-MM-dd') //converts a date into a string like 2020-09-21
Totext ({Number.field}, 0,"") // converts a number into a string with no decimals or commas




Page Header plus Page Footer is too large for the page

Sunday 17 May 2020 @ 10:06 pm

I had a customer call me recently about this error. Usually this error is quick to resolve.  It is almost always caused by a an object that “Can Grow” (e.g. subreport, memo field) in the Page Header. The customer sent the report to me with saved data and it opened fine on my PC. Both sections were empty and both sections were also suppressed. That made no sense to me. I assumed they had sent me a different report by mistake.

So we did a remote session and I confirmed it was the same report. When it opened on their PC the error appeared. After some poking around I went into “Page Setup” to check the page size and noticed that the bottom margin was set to 11 inches. The error message about the PH/PF turned out to be a red herring. The real issue was a combination of the margin setting and the selected printer. Once we entered a reasonable margin setting, the report behaved normally in their environment.

So if you get this error and can’t find anything that would cause it, you have one more place to check.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server