Archive for the 'Bugs and Errors' Category

No tables shown in the Database Expert

Sunday 16 June 2013 @ 11:07 pm

I was helping a customer set the location of several tables from one database (or catalog) to another identical database on the same server. We tried to set the location using the ODBC connection, but they are on the same server so it didn’t change the catalog. We also tried logging out and logging back into the ODBC connection so that we could select a different catalog on the login screen. But this only changed the catalog shown in the connection properties. The table properties still showed the original catalog. When we refreshed the report the data still came from the original catalog.

So we figured we would have to set the location table by table. That would allow us to specify a table within the new catalog. But that is when things got weird. We Continue Reading »
No tables shown in the Database Expert

Copying text into Crystal Reports

Wednesday 8 May 2013 @ 9:21 pm

I try to avoid copying text directly from a formatted document (Word, PDF, etc) directly into a Crystal Reports text object. While Crystal can accept many of the formatting characters from other applications, they can sometimes cause unpredictable behavior.

I recently had a customer show me a text object in Crystal that refused to display the first few characters of the text. We would see these character when the text object was in ‘edit’ mode but they would disappear when we exited and went to preview. We couldn’t find the formatting characters that were causing this behavior, and if we copied the text to a new text object the behavior followed. We finally solved it by copying the text to Notepad, and then copying from Notepad to a new text object. This stripped out all the hidden formatting.

So now whenever I have to transfer formatted text to a report, I first paste it into a simple text editor like Notepad. Then I will copy the text and paste that into Crystal. I can then add any needed formatting within Crystal Reports.

Saved data vanishes when you narrow the criteria.

Monday 22 April 2013 @ 11:29 am

A user in Tek-Tips was puzzled. He was viewing reports with saved data and changing the parameters to filter the report in different ways. When the saved data was generated today, he could change the parameters and filter the data as expected. But if he tried to do this on an older report, the saved data would go away.  I have run into this before and so I know the likely cause – a  date-sensitive line in the filter, something like This:

{db.DateField} in LastFullMonth

When the report is refreshed, it uses today’s date to calculate the beginning and ending of the prior month and filters the data to those dates. So a report run in April would only include March data.

But what if that report is saved with data and then Continue Reading »
Saved data vanishes when you narrow the criteria.

Using SQL commands in ABRA HR

Wednesday 13 March 2013 @ 9:36 pm

I don’t know how many of you out there using Crystal with the ABRA HR/Payroll software, but I just had a real challenging episode with it and ended up learning quite a bit (the hard way).

My task was to speed up a report that took 3 hours to run. The problem was obvious – a recurring subreport that ran 900 times on a typical payroll. A subreport was used because linking the table would have required adding a filter to an outer join, something that the CR linking window can’t do.

Plan A:
To eliminate the subreport we needed a SQL Command that included a Left Outer Join to a subquery. But when I wrote the command it generated errors. So I started Continue Reading »
Using SQL commands in ABRA HR

SQL expression bug in v9, v10, v11(XI)

Saturday 26 January 2013 @ 12:14 am

So here is an odd bug I found in Crystal versions 9, 10 and 11(XI). It affects SQL Expressions that contain a correlated sub-query. This example expression works in both CRv8.5 and CRv12 (2008) but none of the versions in between:

WHERE CAL.ACCOUNTNO = Contact1.AccountNo)

The expression is designed to show the first appointment in the Cal table for each contact in the report. Using a SQL Expression avoids duplication I would normally get by adding the Cal table to the report directly. Run this expression in CR versions 9, 10 or 11 and you get this error:

Error in compiling SQL Expression.
The multi-part identifier Contact1.AccountNo
could not be bound.

At first I thought this error was coming from SQL. As a test I took a v12 report that had this expression and opened it in v10. The report ran fine. But if I then try to edit the SQL expression or even check it, I get Continue Reading »
SQL expression bug in v9, v10, v11(XI)

Mixing Number and Currency Values

Wednesday 19 December 2012 @ 10:55 am

A user was getting an error in a Crystal formula and asked me to take a look. The formula looked like this:

{FieldA} / {FieldB} * 100

She was dividing one column by another to get a percentage and Crystal responded with “a number is required here” – pointing {FieldB}. I checked the data types of the fields, and found that {FieldA} was a numeric but that {FieldB} was a currency field. Apparently you can’t divide a numeric by a Currency. That was news to me so I did some testing and learned a few things:

1) You can divide a currency value by a numeric value or by another currency value with no error.
2) You can use % instead of / and the data types don’t matter
3) You can convert a currency value to a numeric value by using the function ToNumber() but NOT by using the function Val().

So there are two options that will work to accomplish our requirement:

{FieldA} / ToNumber ({FieldB}) * 100

or the much simpler:

{FieldA} % {FieldB}

In this last option the percent sign automatically moves the decimal two places so there is no need to multiply the result by 100.

Strange behavior with IIF

Saturday 15 December 2012 @ 12:42 am

I found an interesting behavior the other day.  A customer had written a formula that was hitting null values and failing.  Normally I would do something like this:

If  IsNull ({FieldA}) then {FieldB} else {FieldA}

But this customer had written it using IIF like this:

IIF (IsNull ({FieldA}) ,{FieldB} , {FieldA})

I expected it to do the same thing and was surprised to find that IIF doesn’t work the same as IF THEN.  With some testing I found that Continue Reading »
Strange behavior with IIF

Report processing flow chart dropped from the CR help file?

Tuesday 14 August 2012 @ 10:40 pm

I was teaching a lesson today about variables and evaluation times for a remote student.  One of the things I like to use during this lesson is a diagram from the CR help file.  It is called the “Multi-pass reporting flow chart” and is found at the end of the section called the “Report Processing Model”.    The diagram shows the specific steps that a report goes through from beginning to end, and which steps are considered part of each evaluation time.

So I was surprised to find that the diagram was not listed in its usual place in the help file contents.  I didn’t spend much time looking for it, since I couldn’t imagine that there was a more appropriate place.  I figured that the diagram was dropped from CR 2011.  It may be that it has been moved, but just in case, I decided to post it on my website.  That way if I am teaching a class on-site and I don’t have it I can still use it in class.

I won’t be able to check it here because after evaluating CR 2011 I decided to hold off on the upgrade.  There are so few changes that I didn’t feel it was urgent.    So if someone out there is using CR 2011 and finds this diagram somewhere in the help file, I won’t mind being proven wrong.

Display strings and null values

Sunday 5 August 2012 @ 4:05 pm

I read a forum discussion where someone was asking how they could have all the null values on the report print ‘n/a’. They were faced with having to write a formula field for every field on the report. I suggested that they could use the Display String property that is available when you format a field. The Display String allows you to override the value that will print on the report. The advantage is that you can use the function CurrentFieldValue within the condition formula and this makes it easy to apply to lots of fields at once.  I suggested that they use a Display String formula that was something like this:

If CurrentFieldValue = ”
then ‘n/a’
else CurrentFieldValue

My plan was Continue Reading »
Display strings and null values

Another change to PeachTree database

Tuesday 17 July 2012 @ 10:55 pm

I create lots of reports for PeachTree accounting so I was surprise to learn that Sage has dropped the PeachTree name and is now calling the product Sage 50 Accounting. This change comes with the new (2013) version.

And just as they did in their last upgrade, they dropped fields from the tables.  The first one I have found is one that some of my customers were using in their reports.  The field is called PSQLCompletedDate and is (was) found in the JrnlHdr table.  If you have been using this field in your reports you will probably need to replace it with the field CompletedDate, also in the JrnlHdr table.

Next Posts »» «« Previous Posts

Versa Reports

The Expert Series