Archive for the 'Bugs and Errors' Category
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.
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.
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