Archive for the 'Bugs and Errors' Category

Spreadsheet doesn’t list any tables

Sunday 15 March 2015 @ 10:30 pm

I recently worked with a customer who needed to incorporate some Excel data into a report. Excel tables are not my first choice, but sometimes it is the only practical solution for the client. Since these were XLSX files we setup up an ODBC connection. But when we connected from Crystal we could not see any tables under that ODBC connection. We were thinking about re-saving it as an XLS when I did a quick online search and found the answer we needed in a forum post.

We had to go into File > Options > Database tab. Then under Tables and Views we had to add a check mark for ‘system tables’. Then the spreadsheet table showed up. I was surprised, because I have created many reports that read spreadsheets and I don’t ever remember having to do that. It is possible that there was something unique in that environment that required this change, but I never argue with success. And, if you ever run into something similar you have one more thing to check.

ToText() function throws in an extra space.

Thursday 26 February 2015 @ 5:48 am

You can use the ToText () function to turn dates into strings, which is handy in cases when you need to combine a date with other characters. For instance I use this pattern in IF-THEN-ELSE formulas, when I want to give the user a parameter to choose sorting on a date value vs a string value:

else ToText ({Tale.Date} , 'yyyy-MM-dd')

This turns the date into an 8 character string that can still be used as a sort field and will still sort the records in chronological order. The [yyyy] returns a 4-digit year, the [MM] in the middle returns a 2-digit (0 padded) month and the [dd] on the end returns a 2-digit (0 padded) day. The dashes can be any character that you want to use as a divider. So using the formula above the date value 2/4/2015 ends up looking like this:


For purposes other than sorting you might want a 2-digit year or a month/day value that is not zero padded. In that case this formula:

ToText ({Tale.Date} , 'yy-M-d')

creates a date that looks like this:

15-2- 4

The digits are correct, but if you look closely you will see an extra space in front of the days value. For some reason a single-digit month value does not have a space but a single-digit day value does have a space. This must be a bug since I can’t think of any reason for the difference. And it is a long standing bug since I just tested this in v10 (2004) and v8.5 (2001) and both have the exact same behavior.
If you ever encounter this and want to remove the space from in front of a single digit day, you have to use a replace function like this:

Replace ( ToText ({Tale.Date} , 'yy-M-d') , " " , "" )

You should activate the AutoSave feature

Friday 16 January 2015 @ 10:09 pm

One of my favorite sayings:

Good judgement comes from experience.
And experience comes from bad judgement.

I was reminded of the value of AutoSave this week when one of my reports froze up on me before I had a chance to save a large number of changes. I waited for it to come back and even went to lunch. But an hour later the report was still frozen. Normally I am pretty good about saving my work often, but I had been concentrating on a problem and lost track of time.

The machine I was working on did not have the CR AutoSave feature activated. But somehow the report that froze did not freeze the rest of  Crystal. I was able to use the menu and save my other reports, but I could not save the report I had been working on.

Since the menu was still working I took a long shot and activated the AutoSave feature. After a few minutes I noticed a new RPT file in the temp directory and it had all of my most recent changes. So even though it would not let me save the “frozen” report, CR was able to AutoSave it for me.

And as if that wasn’t a strong enough message, I had three client workstations crash on me this past week. None of them had AutoSave activated. So allow me to make a public service announcement:  Read my prior post on how to use the AutoSave feature and then activate it in your environment.

The ) is missing ?!?

Saturday 27 December 2014 @ 12:32 am

If you make a mistake in a Crystal Reports formula, the error messages are usually pretty helpful. But every once in a while the error can send you on a wild goose chase. The message above is a good example. It will appear when you have an opening paren without a closing paren. But it will also appear in the following formula, even when the parens match:

NumberVar Save01;
NumberVar Save02;
NumberVar Save03;

if {@criteria}
then (Save1 := 0; Save02 := 0; Save03 := 0)

Now in this formula it is obvious that the parens are not the problem. So why would the error message mention a missing paren? This happens only when you have a specific combination of factors – a Continue Reading »
The ) is missing ?!?

Group Tree takes you to the wrong page

Saturday 20 December 2014 @ 6:30 pm

I recently had a report grouped by Employee with one page per employee. Each Employee showed up in the group tree on the left of the screen. But every time we clicked on an employee in the group tree, we ended up on the page of the employee just BEFORE the one we wanted. I thought it was a glitch to I changed the group options, then closed the report and eventually closed CR completely.  The problem didn’t go away so I realized it was something in the report.

After some experiments I realized what was going on. The report was printing one page per employee and printing only the group footer. But there was no actual “new page after” checked for the group footer. There was one employee per page only because the group footer was so large that only one group footer could fit on a page. So it mimicked a page break.

But the group tree doesn’t take you to the group footer of the group, it takes you to the Group Header. And in this report the Group Header for each employee was suppressed. So as soon as each Group Footer finished on a page, the next Group Header would start right below it. And because it was suppressed, it would always fit on the prior page.

So the Group Tree took us to this (invisible) Group Header which was always on the page before the corresponding group footer. All we had to do to solve the problem was check “new page after” for the Group Footer and that moved the (invisible) Group Header to the next page, right above the corresponding Group Footer. And then the Group Tree worked as expected.

IE Security setting breaks parameters

Wednesday 10 December 2014 @ 9:52 am

Are you having trouble with your parameter windows in Crystal Reports? Did your calendar control stop working? Are the “OK” and “Cancel” buttons not responding?

I just got a note from Adam Butt of APB Reports in Trondheim, Norway.  Apparently he was experiencing these problems and worked for hours to try and solve it. He went as far as uninstalling and reinstalling his entire CR product suite, including several versions. Only when that didn’t work did he realize that he had recently changed his Internet Explorer Security setting to “High”. Changing it back to “Medium High” resolved the problem. I will have to do some testing to see which of the underlying IE security settings breaks CR parameters. But I am posting this now for others who might run into this problem, or a similar problem, with parameters.

Java script error on the Crystal Reports start page

Wednesday 19 November 2014 @ 11:32 am

One of my customers sent me a question about an error message they got when they went to open a report. I recognized it as an error I had seen recently on a customer’s workstation. Then today I closed and reopened Crystal and got the same error message. For those of you who open reports by double clicking the RPT file it might seem that there is a problem with report, but this is a problem you will see anytime the CR designer starts.

I have written before about how the Crystal Reports start page will request internet access and try to load dynamic content (ie ads).   I showed users how to prevent the start page from requesting internet access by renaming a few files.  What we are seeing today is that there is apparently a problem with some java script in the way that dynamic content is loaded.  Once SAP fixes the problem the error should go away.  In the meantime if you just stop the script the reports should load and run fine.  If you don’t want the start page to load web content you can use the method in the post mentioned above.

Field disappears during Verify Database

Thursday 30 October 2014 @ 11:07 am

All reports pull their data from tables of some kind, and these tables do change over time. Fields are added or removed and sometimes field names or their data types are changed.  Crystal has the “Verify Database” feature to update the report so that the table structure stored with the report matches what is in the database.   If a field used by the report is removed, renamed or has been changed to a different data type, you will have to map the old field to the new field in a small mapping window.

But I found an interesting exception.  One of my customers had a character field that was changed from a long string to a memo field.  Apparently the Verify Database ‘mapping’ feature can’t see memo fields, even when they have the exact same name as the original string field.  So when I went to Continue Reading »
Field disappears during Verify Database

Syntax error or access violation

Tuesday 29 July 2014 @ 9:53 pm

Today I was dealing with this error message. It was generated by a report that uses a SQL command with a parameter. The parameter was a numeric and when we replaced the parameter with a specific number, the error went away. Putting the parameter in the same spot generated the error.  So as part of troubleshooting I started taking out pieces of SQL until there was very little left of the query, and the error remained.  So we were surprised to find that moving the same SQL to a new report worked fine, even with the parameter.  The customer was ready to recreate the report from scratch.

Then she made a comment that she had once deleted and recreated a parameter to fix a similar problem.  Unfortunately that hadn’t worked for her today.  But this rang a bell, so I asked her if she had used the same parameter name when recreating the parameter.  She had, so I deleted the parameter and created a new parameter with a different name. I used the new parameter in place of the old one and the SQL ran correctly with no error message.  It appears that this parameter name, which she had used successfully in several other reports, had somehow gone flaky inside this report.  She tried to rename the working parameter back to its original name and the same error popped up.  So the problem wasn’t with the SQL Syntax or with the value but something specific to that parameter name in that one report.

So if you run into a similar problem with a SQL Command parameter and nothing else seems to fix it, here is one more thing you can try.

Formula failures without error messages

Saturday 28 June 2014 @ 7:21 am

I cleared up a mystery today, one that I thought was just a Crystal glitch. It explains why Crystal formulas sometimes fail without an error message to describe the problem. We are not talking about invalid formulas. An invalid formula generates an error when you save it. We are talking about a formula that fails only when the report is run and it finds data that it can’t process. For example, a formula that divides one field by another is valid, but will fail if the denominator field has a value of zero.  A formula that converts characters from a string field into a date will also fail if the characters don’t make up a valid date. Both of these failures would normally pop up an error message.  So what does it mean when it doesn’t?

Take an example where formula A is used inside formula B, and then formula B is in turn used inside formula C. If formula A fails then Continue Reading »
Formula failures without error messages

Next Posts »» «« Previous Posts

Versa Reports

remiCrystal reporting solution