Archive for the 'Bugs and Errors' Category
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.
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
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.
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
A customer sent me a report today with an unusual behavior. There was a total on the report and the problem was that the total would change when she unsuppressed a details section and change back when she re-suppressed the details. Even adding a blank details section made the total change.
At first I figured she had to be mistaken. I could think of no way that suppressing a section or splitting a section could have an effect on totals. But as I thought about it I realized there was one one obscure way. Both of the changes she mentioned could change the number of pages. And there is one way that adding pages can affect totals.
There is a property in the Continue Reading »
Add a blank section, and the totals change?
I noticed something odd the other day when sorting/grouping a character column. The column was date values stored as strings in the format Year-Month-Day. When I looked at the data I noticed that some values had dashes and some did not, like this:
What surprised me was that they were still sorting in chronological order, as if the dashes didn’t exist. I had never seen that before. Since I like my sorts to be consistent and predictable I did some quick testing to help me understand what was happening.
I discovered that if you sort or group on a database field – and even on some simpler formulas – the database driver will add an “ORDER BY” to the SQL query. This means the database Continue Reading »
Dashes and strange sorting
I was working with a customer today, troubleshooting a report reading MAS 90. The report had a subreport at the detail level and he said that the full report could take up to 24 hours to run. I tested the subreport and found that the selection formula was not being converted into a WHERE clause in the SQL of the subreport. So every instance of the subreport (thousands) had to do a serial read of the entire table. The subreport seemed pretty simple so my first approach was to recreate it and test it as a stand alone report. It took only a few seconds to run and I could see the WHERE clause in the SQL. But as soon as I inserted the new version to replace the existing subreport the SQL would no longer generate a WHERE clause.
So then I studied the relationship between the main and the subreport. I found it was possible to Continue Reading »
Subreports in a Sage/Mas environment
When my customers send me report problems they sometimes feel the need to send me a photo album. I will get a screen shot of the linking window, a screen shot of the preview tab, a screen shot of some key formulas, etc. It is much more efficient to send me the RPT file. With the RPT I can open the linking window myself, open any formulas, and open anything else that might help me troubleshoot the report. An RPT can replace most of the screenshots I get and gives me much more information. If the RPT can’t be saved with data I may Continue Reading »
Troubleshooting from Screen shots
I am not sure how many of you will have use for this info, but I have one customer who ran into an unknown limitation in the Crystal Reports API (.NET). He was using FindItEZ to track changes in a very large and complex report. The report had one section divided into over 400 subsections and certain subsections weren’t being identified correctly. Instead they were repeating as “phantom” duplicates. So the team at FindItEZ did some testing and Ken Gnazdowsky reported what they considered a bug in the .NET API.
But, after some discussions with SAP they were told that the .NET API can only keep track of 104 subsections in any one of the 7 major sections:
Report Header/Report Footer
Page Header/Page Footer
So if you go past 104 in any one section (past subsection label ‘cz’) then the Crystal.net API loses the Continue Reading »
Limitations in the Crystal Reports .NET runtime
I thought I had written about this before, but apparently I have only mentioned this in forums and never on my own blog. When you export a Crystal Report to PDF, by default Crystal will reduce the fonts by around 5%. Most people don’t notice this, but in some situations this causes problems. The solution requires that you go into the registry and add some keys. Finding where to make the change and what the change should be is a bit tricky because there is an older method and a new improved method.
The older method requires Continue Reading »
Reduced fonts when exporting to PDF