CODEWITHSUNDEEP
crystal-reportsselectionrecord
user2249939
user2249939

Reputation: 11

Crystal Reports Record Section not returning same data as sql

I found a solution that another Tech had posted:

New Formula:

{Product.Size} <> “xsm” or IsNull({Product.Size})

Unfortunately, when preview your report, you will find that this doesn’t work. That is not because of a mistake in our logic but rather, what I consider to be a bug in Crystal Reports. If I took this exact same condition and applied it to the database records using a query analyzer or querying tool, I would see the blank records. Unfortunately, Crystal is not allowing the null values to come through even though our formula says that they should.

The trick to circumventing this bug is to put the IsNull() check FIRST in the formula.

Thus, if we rearrange the condition to this:

IsNull({Product.Size}) or {Product.Size} <> "xsm"

WORKED LIKE A CHARM

Problem is, if I select criteria for the second OR statement ({HiredRate.UTRANSDOC}startswith{?TransYN}) and NO for the first ({HiredRate.UTRANSWEB}startswith{?WebYN}) I get only one record that meets the TransYN criteria. If I switch places in the formula putting ({HiredRate.UTRANSDOC}startswith{?TransYN}) 1st I get all of the data.

When I run the SQL query I get all of the data no matter what order they are in. The Crystal Preview only gives me all of the data on the first from the OR section.

The only thing that stands out looking at the data from SQL is that the one record Crystal is returning has YES in the Transdoc field and the Transweb field is blank. All other records show YES for Transdoc and NULL for the Transweb field.

Here is the Crystal Record Selection Formula

{HiredRate.CONTSUPREF} startswith {?LanguageCombo}
and    {HiredRate.ONDATE} = {?ProjectDate}
and    {HiredRate.ACTVCODE}= "SIG"
and    {HiredRate.RESULTCODE} = "CLM"
and    (
      {HiredRate.UTRANSWEB}startswith{?WebYN} 
or    {HiredRate.UTRANSDOC}startswith{?TransYN}
or    {HiredRate.UTRANLANL0}startswith{?LanloYN}
or    {HiredRate.UINTCONSEC}startswith{?InterpYN}
or    {HiredRate.UINTCONF}startswith{?IntConfYN}
or    {HiredRate.UINTOPI}startswith{?OPIYN}
      )

Here is the SQL query Crystal is using:

SELECT HiredRate.DEAR, HiredRate.CONTSUPREF, HiredRate.LASTDATE, HiredRate.CONTACT, HiredRate.USOURCLANG, HiredRate.UTARGLANG, HiredRate.UTRANSDOC, HiredRate.UTRANSWEB, HiredRate.UTRANLANL0, HiredRate.UINTCONSEC, HiredRate.UINTCONF, HiredRate.UINTOPI, HiredRate.ONDATE, HiredRate.ACTVCODE, HiredRate.RESULTCODE
FROM GoldMine_Main.dbo.HiredRate HiredRate
WHERE HiredRate.CONTSUPREF LIKE 'ENG>SPA%' AND (HiredRate.ONDATE>={ts '2012-04-01 00:00:00'} AND HiredRate.ONDATE<{ts '2013-04-06 00:00:00'}) AND HiredRate.ACTVCODE='SIG' AND HiredRate.RESULTCODE='CLM' AND (HiredRate.UTRANSWEB LIKE 'NO%' OR HiredRate.UTRANSDOC LIKE 'YES%' OR HiredRate.UTRANLANL0 LIKE 'NO%' OR HiredRate.UINTCONSEC LIKE 'NO%' OR HiredRate.UINTCONF LIKE 'NO%' OR HiredRate.UINTOPI LIKE 'NO%')
ORDER BY HiredRate.DEAR, HiredRate.CONTACT

Upvotes: 1

Views: 3375

Answers (1)

user359040
user359040

Reputation:

This is happening because the {HiredRate.UTRANSWEB} is null - the rest of the expression is therefore evaluating as null in Crystal, even though (logically) it shouldn't.

When the first two or conditions are swapped around, the {HiredRate.UTRANSDOC} condition evaluates as true and the rest of the expression is short-circuited - which is why records are then selected.

Upvotes: 1

Related Questions