Crystal Report won't display record if GUID column in Record Selection Formula

0
votes

I have a table with a GUID column and a recordId (not a guid) column. I need my report to accept a GUID as a report parameter and use it as part of the Record Selection formula to make certain the report only get the record Id's it needs.

[Linking Table] -> [Detail Containing Table]

Since the report doesn't support passing a GUID parameter directly, I was able to convert the GUID to a string and pass it in without incident. When I generate the SQL query in Crystal Designer it looks good: The WHERE clause is properly formatted and if I run the query in SQL Designer it will find all the records I need to fetch. Actually attempting to display the report fail however, it states that 0 records were found despite SQL Profiler also showing that the same SQL was fired off.

Has anyone run into this issue? Is there any way to get the Record Selection formula to not drop the records when actually displaying the report?

1
crystal-reportscrystal-reports-xi
record selection formula doesn't drop anything.. it only adds where clause to the query... check the data you are passing to GUID which is preventing the required data to be returned. – Siva
It's adding the proper where clause in the SQL query, but announces that 0 records were returned when it does its display pass. SQL Profiler has verified that the proper GUID is being used in the Where clause. – ChargerIIC
It means for that GUID there are no records...try another id – Siva
I'm not certain you understand. It is the correct GUID and running the SQL statement directly against the database in SQL Manager does produce records. – ChargerIIC
It's strange behavior... Try removing record selection and run the report and see records coming or not – Siva

1 Answers

0
votes

After working with SAP Support we were able to determine that there is a defect in 14.0.11. The SQL Query was being formatted correctly, but the record linking under the hood was using a different (and incorrect) linking order. Since the database results and internal schema wound up with different starting tables, Crystal discarded the results to prevent exceptions.

Going to Database Expert -> Links -> Order Links and checking the 'Link Ordering is Enforced' box forced the engine to use the same link ordering throughout the report in the proper order. Then the SQL query and internal record schema matched and we got records.