Not filtering out Null Values from a column

[techytechy]

Hi Guys,

I am using crystal Reports X1 r2.
From a column that has null values too in some of its rows I would like to filter out some rows. In the result I dont want Null value records to be filtered out along with others in the filter criteria. For Eg:

MyResult:
------------------
Col1 Col2
1 A
2 B
3 C
4 Null
-------------------
I filtered out B, C in the select expert using:
not({Col2} in ["B", "C"]). In the result I want to see record 1 and 4, but record 4 also got filtered. So, I set the option "convert database Null values to default" and "convert other Null values to default" but still the record 4 got filtered. Could any one assist?

Thanks!

 

[fisheromacse]

Without knowing what your table/data is like, I think you could use a formula with a variable like this:


stringvar colb;

IF isnull({Yourtable.YourField})
OR TRIM({Yourtable.YourField})= ""
THEN colb := " "
ELSE colb := {Yourtable.YourField};


In your report replace the field with this formula.

 

[lbass]

I would remove the convert null to default setting and then use a selection formula (report->selection formula->record)like this:

(
isnull({table.col2}) or
not({table.col2} in ["B","C"]
)

If the second column is from a different table, you should have a left join FROM the col1 table TO the col2 table.

-LB

 

[Henna4]

Hi,

I have a list of Equipment IDs that I enter into my selection criteria.
If an Equipment ID is not in the database, it doesn't show up on the report. What formula would I use to display the Equipment ID number(s) that are not in the database as well as a statement beside them saying "No Data Found"?

Thanks for your help!

 

[lbass]

You should have started a new thread as this is a different topic. Anyway, create a formula {@accum} (this assumes the equipment ID is a string):

whilereadingrecords;
stringvar x;
if {table.equipID} = {?ID} and
not({table.equipID} in x) then
x := x + {table.equipID} + ",";

Place this in the detail section.

Then create a second formula for the report header:

whileprintingrecords;
stringvar x;
stringvar y;
numbervar i;
numbervar j := ubound({?ID});
for i := 1 to j do(
if not({?ID} in x) and
not({?ID} in y) then
y := y + {?ID}+", "
);
if len > 2 then
"No Data Found: " +
left(y, len-2)

-LB