SAP Knowledge Base Article - Public

1433745 - Incorrect result is returned in Crystal Reports using Sybase

Symptom

  • Incorrect results displayed in Crystal Reports
  • Crystal Report returns one row of data but the same query when fired on database (Sybase) returns 4 rows

Environment

  • Crystal Reports 2008 Service Pack 2
  • Sybase

Reproducing the Issue

  1. Create a new Crystal Report using Sybase connection
  2. Select all the objects from required tables and preview the report
  3. Create a record selection formula for example as “({T_Employee.UserName} = “Tim” AND {T_Site.City
    = “London"})”
  4. Save and close
  5. Refresh the report (one row is retrieved)
  6. Copy the SQL from Database > Show SQL Query option
  7. Run the same SQL onSybase DatabaseSQL Advantage (4 rows retrieved are retrieved).

Cause

This was raised as a potential bug to product group under reference number ADAPT0137 but has been rejected by design as its due to how Crystal Reports handle null values.

While Crystal Reports can interpret Null values from Database, null values from the database are considered to a 0 (for numeric fields) or a blank (for non-numeric fields). The root cause of this problem is (if “<blank>”=”string1”) then there is a problem. In CR if (“”=”Engst”) returns false then in Sybase this formula returns true. 

Resolution

Workaround

Change the selection formula to {T_Employee.UserName} in ["", "Tim"] and {T_Site.City} in
["London",""] to include 
the blank value, then the same results (4 records) is obtained. 

Keywords

KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Bug Filed

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports XI R2