Can't Set Crystal Report Selection Formula Programatically

Question

Hello all, first, I can't stand Crystal! Okay, that's off my chest...

Now, we have an old VB6 app we maintain for a client, which uses the Crystal Automation library to programatically change the record selection formulas in a bunch of Crystal Reports 8.5 reports.

There are two reports which are ALMOST identical. I had to change them recently to add another field from another table. When I added the table in to the reports though, while it added it in the visual designer, it did not add it in the FROM clause of the SQL statement. So, I manually edited the SQL statement to add in the additional join. KO, works great. If i run the reports in Crystal preview mode, they work exactly as expected.

Now, the users went to test the changes from within the VB app. One of the reports works fine and dandy. The other report however, is failing to set the selection formula as expected.

The code sets the selection formulas using the function PESetSelectionFormula. I verified that the string being passed in to the function as the new selection formula is correct via a step-through examination of the variables. The call to PESetSelectionFormula seems to be working okay, and is returning a value of 1, which as near as I can find anywhere indicates success. (The other report, which is working fine from code is also returning 1.)

However, the report is failing with an error: Error Code: 534 - Error detected by database DLL. The code, for debugging purposes dumps out the SQL string currently being used by the report. The SQL coming out of the report is:

SELECT ... FROM ... 
WHERE

ORDER BY ...

As you can see, the WHERE clause is blank, which I would imagine is why the database DLL is upchucking on this statement. I don't understand why the automation library is not setting the WHERE clause even though the call to PESetSelectionFormula is being passed a valid string and is returning success. I thought perhaps it was because I had manually edited the SQL in the report to add the table it wasn't adding, but I did the same thing in the other nearly identical report, and that one is working fine.

Anyone have any ideas why PESetSelectionFormula might report success but not actually do anything?

P.S. I have already tried doing a Database > Verify Database from the menus, and that said the report was all up to date and did not help at all.

Answer 1

I've never used PESetSelectionFormula, but I have encountered problems importing otherwise acceptable data:

1. Does your final SQL query use any uncommon functions, i.e. nz()? Crystal doesn't always recognize VB functions, imho.

2. Do you have any null values in your imported data that are creating conflicts?

3. After gathering your data, can you put it into a temporary table and tell Crystal to pick up the data from that temp table? If you have issues with #1 or #2, this may be a good workaround.

Answer 2

In some versions of CR, the PESetSelectionFormula appends the new value to the existing record-selection formula, in other versions, PESetSelectionFormula replaces the existing record-selection formula with the new value.

I don't recall when this switch occurred, but it should be easy for you to determine which behavior is occurring.

Answer 3

Okay, this is typical of why I just despise Crystal Reports.

My boss came back from vacation, and looked at it, ... did nothing, and now it works. He showed me exactly what he did, and he didn't change or do anything to the report, just the same debugging steps I'd taken, but now it magically works.

I truly dislike software that just automagically stops and starts working like this. Thanks for the ideas.