Records Not Showing Up On Report

[Former Member]

I'm using Crystal XI

I can take the query from "Show SQL Query..." and paste it into a query screen in SQL Server 2008 management studio and retrieve the correct records. However, the report itself does not contain all the records the should have been returned.

To the best that I can figure, the records where a transaction date (date/time field) is null is not showing up on the report. I only display the field, do not do any calculations based on the field.

It is part of the record selection formula, where I test for the field to be between start/end parameters or is null (which produces the correct SQL query as I stated above)

So why are the records not showing up on the report, and more importantly, how do I get them to show up?

TIA

[Former Member]

Hi

Do the following

Go in Record Selection formula and on top of it you will find u2018Exception for Nullsu2019 dropdown , please make it Default values for nulls. It will take care of field null values.

Thanks,

Sastry

[Former Member]

Adding to Sastry's point, you can try the below:

Go to File--> Report options --> Convert Database Nulls to default and Convert other Nulls to Default.

Thanks,

Kiran

[Former Member]

It already was set to "Exception for Nulls"

On the other hand, just on a whim, I tried reversing the order of the query from what it was:

{MyQuery.Company = {?Company}
    And 
    (
    {MyQuery.Transaction_Date} In {?Start/End Date}
    Or
    IsNull({MyQuery.Transaction_Date})
    )

To:

{MyQuery.Company = {?Company}
    And 
    (
    IsNull({MyQuery.Transaction_Date})
    Or
    {MyQuery.Transaction_Date} In {?Start/End Date}
    )

And that seemed to work. I'm not sure why it will work in the Mangement Studio, but not in Crystal Reports, but at least it works.

Thanks for the tug in the right direction.

[Former Member]

Hi

Can you make it to 'Default values for Nulls' insted of Exception for null values and try.

Also you can include :

IsNull({MyQuery.Transaction_Date}) or l({MyQuery.Transaction_Date}) = " "

Thanks,

Sastry